none
OleDb error opening large Excel 2007 files on web server RRS feed

  • Question

  •  I am having an issue with an ASP.NET application opening Excel 2007 files on the server.

     Here is my process:

    1. User selects a file to upload
    2. Save the file on the server
    3. Open the file using OleDb using the Microsoft.ACE.OLEDB.12.0 provider
    4. Load it to a database table
    5. Do some processing
    6. Export a CSV file

     I get an error on step #3 – on the connection.Open() method call.  One of the two errors occur:

    a.  External table not in expected format, or

    b.  No error message available, result code: E_FAIL(0x80004005).

     

    Now for the strange part.  This does not occur when any of the following conditions are true:

    1.       I run on my development machine

    2.       Files are Excel 2003 format

    3.       Files are less than 2 MB

    Reversing that, it only occurs on the test/production web servers with Excel 2007 files that are larger than 2 MB.  If I convert the XLSX files to XLS, then they work fine.

    Wednesday, May 5, 2010 3:50 PM

Answers

All replies

  • Hello,

     

    Welcome to ADO.NET Managed Providers forum!

     

    It seems to be a product issue I know in the ACE engine.  When the .xlsx file is large, it is likely the file contains some charts.  .xls files work fine since the JET driver is used to read the file.   Fortunately, the issue is fixed in a hotfix for Office 2007 driver.   

     

    Please follow these steps to install the hotfix:

    1.      Of course, we need the Office 2007 data driver, http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en.  

    2.      Install the Office 2007 data driver SP2, http://support.microsoft.com/default.aspx?scid=kb;EN-US;957262.

    3.      Install the hotix, http://support.microsoft.com/default.aspx?scid=kb;EN-US;968861. 

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, May 6, 2010 3:04 AM
    Moderator
  • Thank you for your reply.  

    There are no charts in these files, just many rows of data.  So, after reading the description of the issue resolved by the hotfix, it does not sound like it will fix my issue.  I do have the first two steps in place.

    Any other possible solutions to this issue?

    Sincerely,

    Marc Butenko

    Thursday, May 6, 2010 2:44 PM
  • Hi Marc,

     

    Can you install the hotfix to see if it solves the problem?   I checked our internal database and find that the current case is related to an old support case which is solved by this hotfix. 

     

    Besides, the ACE driver seems to be managed by the Access team.  If the problem still exists, I will help to consult the product team for better support. 

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, May 7, 2010 4:59 AM
    Moderator
  • Hi Marc,

    Could you please tell us how is the problem now?  

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, May 12, 2010 1:28 AM
    Moderator
  • I am having the EXACT same issue. Were you able to solve the problem?

    Hoping for a response.

     

    Monday, May 17, 2010 7:49 PM
  • Same issue. Did you solve the problem?

    Thanks

    Regards Stefan

    Thursday, May 27, 2010 2:21 PM
  • We are working with Microsoft on the issue.

    They asked us to try out different things on the server and now XLSX file upload is working. But the problem is we are not able to pinpoint what "change" fixed the issue !

    I will definitely post if I know what the fix was.

    HChand

    Thursday, May 27, 2010 6:32 PM
  • Thank you for the response. Did you install the following?

    2.      Install the Office 2007 data driver SP2, http://support.microsoft.com/default.aspx?scid=kb;EN-US;957262.

    3.      Install the hotix, http://support.microsoft.com/default.aspx?scid=kb;EN-US;968861. 

    Regards

    Stefan 

    Friday, May 28, 2010 6:43 AM
  • I am having the same exact issue.  The SA installed the Office 2007 Data Driver SP2 and Hot Fix 968861; the Office 2007 Data Driver was already installed.  The server has been rebooted and the problem still exists.  Can anyone please provide a solution?
    Wednesday, June 16, 2010 2:10 PM
  • Hi all

     

    Just some updated information for this issue.

    The background of this issue:

     

    "Since the Excel 2007 openxml format is zipped xml so the ACE driver OLEDB needs to read the whole file to unzip it. If the file is too large for the memory buffer, it gets cached out to disk and the location it uses is Temporary Internet Files for whatever user the app is running as. In the case of my customer, the web app is running as Network Service which means (we found this by using procmon):

    C:\Documents and Settings\Default User\Local Settings\Temporary Internet Files\Content.MSO. To work around this, we manually created the Content.MSO directory and gave Network Service modify permissions to Content.MSO. ”

     

    Another workaround would be using Openxml sdk (or the Packaging APIs plus the .NET XML classes) instead.  

    Hope it is helpful!

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by lovetolearn Thursday, June 24, 2010 2:15 AM
    Wednesday, June 23, 2010 7:00 AM
    Moderator
  • You can also try to use .NET Excel reader from my web site. It allows to read Excel files without creation of temporary files and does not require any providers, so there is no dependency
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, June 23, 2010 10:44 AM
    Moderator
  • Thank you so much for the information!  It was a permissions issue.

     

    Here is what the SA said:

     

    “The key to this issue was the statement they made which is “If the file is too large for the memory buffer, it gets cached out to disk and the location it uses is Temporary Internet Files for whatever user the app is running as”. I ran a file trace and sure enough the Network Service was denied access to that folder. I created the folder and set the permissions.”

     

    Once the SA did this, the application works just fine now for large Excel 2007 files using the ACE driver.

     

    Again, thank you very much.

    Thursday, June 24, 2010 2:17 AM
  • We worked with Microsoft on this issue and this was there recommendation:

     COPY the file to another folder.

     MOVE it back to the intended folder.

     This is what we ended up doing.

     Thanks for all your responses!

     HChand

    Friday, June 25, 2010 1:25 PM
  • Lingzhi

    Our production server is a Windows 2008 server 64  bit. There is no c:\Documents and Settings, instead there is a C:\Users.

    Can you tell me what is the corresponding folder to:

    C:\Documents and Settings\Default User\Local Settings\Temporary Internet Files\Content.MSO

    on a Windows 2008 server?

    Thanks

    Friday, June 25, 2010 1:38 PM
  • Thank you for the information this exactly the solution to the problem I was having.
    Tuesday, August 10, 2010 2:15 PM
  • First of all sorry about upping this topic but I have the same problem as described in the first post. After reading this topic I was eager to try the suggested permissions solution.

    The OS of the server however, is also windows server 2008 and I have no clue whatsoever of which folder I have to grant the permissions. I've already tried a few but no luck so far.

    Any help would be appreciated!

    Monday, November 15, 2010 4:11 PM
  • Worked for me too, thanks!
    Wednesday, March 9, 2011 7:19 PM
  • I have track on procmon.exe.

    and finally  get it  on win 7.

    'C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files'


    • Edited by yhz2006 Thursday, March 8, 2012 7:14 AM
    Thursday, March 8, 2012 7:14 AM
  • I'm having the same issue as the original poster. ASP.Net web application, importing excel 2010 files, open with OleDb using the Microsoft.ACE.OLEDB.12.0 provider.  Imports work fine except when the files are over 2Mb in size.  The server I am using is Windows 2008.  Can you tell me the correct cache directory for windows 2008?

    Tuesday, July 23, 2013 9:34 PM