Microsoft ACE OLEDB connection creating empty Excel when there are 166,110 rows

Question

I am programming in C# and using an oledbconnection. This is the standard connection string e.g.

using (OleDbConnection conn = new OleDbConnection(
          "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
           saveFilenameAndLocation + 
          ";Extended Properties='Excel 12.0 Xml;HDR=Yes'"
      ))  

This works successfully and creates my Excel spreadsheet full of the correct data, when the number of rows isn't excessive. However, whenever the number of rows in the spreadsheet increases to a large size (e.g. currently it is failing on 166,110 rows), it produces an empty spreadsheet with the worksheet tab name set to A266FF2A662E84b639DA.

It is not possible to rewrite this so that it doesn't use an OLEDB connection, any ideas why it doesn't work when the row size increases?

Answer 1

I am not sure about your application environment, but I have seen this when generating Excel files from an ASP.NET app.

Once the amount of data exceeds a certain size (~1 MB in my experience), the provider will attempt to create a temp file while generating the output. I have come across this issue using the 32-bit provider on 64-bit systems. If you are running under a service account with this configuration then the location where these files are created is

C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO

This location, however, is only accessible to administrators and SYSTEM by default, and if the provider is running under a non-privileged account and is unable to create the temp file it will fail silently and just return its default "empty file" with the A266FF2A662E84b639DA worksheet.

What you need to do is grant the account of the application that runs under (for example Network Service or IIS AppPool\) read/execute/list contents permissions along the path "C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files" and then full access to the Content.MSO folder itself.

If the provider matches the bitness of your system then I suspect you need to perform the process above for C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO instead, although I have never tested this.

I want to extend my thanks to sysinternals for providing procmon that helped me troubleshoot this issue.

Answer 2

One of the employees at my company ran across this problem today. The solution was simple: She had only 20MB free space left on C: - If you free up enough space on your system drive, it should solve the problem.

Answer 3

Windows Server 2012R2 Check also if folder INetCache exists and this folder has full perimissions C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Windows\INetCache

Answer 4

After 10 hours I found the solution.

Every 1000 rows we have to close and reopen the connection.

Sample is here.

string createTable = "Create Table [Sheet_X] (field1 char(255), field2 char(255),field3 char(255));";
OleDbCommand cmd = new OleDbCommand(createTable, conn);
conn.Open();
cmd.ExecuteNonQuery();
var counter = 0;
foreach (var item in items)
{
    if (conn.State == ConnectionState.Closed)
        conn.Open();
    string insertdata = "insert into [Sheet_X] (field1,field2,field3) values('value1','value2','value3');";
    counter++;
    if (counter >= 1000)
    {
        counter = 0;
        conn.Close();
    }
}

Answer 5

If it still don't work after tried all above solutions, try this one, it worked for me:

In the "Advanced settings" of your IIS Application Pool, Change the value of "Load User Profile" from "False" to "True".