ACE oleDb drivers unable to handle huge excel files

Question

Does ACE OLEDB drivers have any known issues with larger files? I am using the below code to retrieve the worksheets in a 400Mb xls file

public string[] GetWorkSheets()
{
    var connectionString  = "Provider=Microsoft.ACE.OleDb.12.0; data source=c:\filepath\filename.xls; Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\"";
    DataTable dataTable;
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();//Exception thrown here for large files
        dataTable = connection.GetSchema("Tables");
    }

    int lenght = dataTable.Rows.Count;
    string[] worksheets = new string[lenght];
    for (int i = 0; i < lenght; i++)
    {
        worksheets[i] = dataTable.Rows[i]["TABLE_NAME"].ToString();
    }
    return worksheets;
}

I receive a OleDbException with the message System resource exceeded. I am not calling this function in loops, or opening any other connection before I reach here. This code works perfectly for smaller files.

My system has 4Gb RAM.Runs on Windows 7 64Bit. The Ace driver is also 64bit.

Any idea what can be done on fix this issue?

Something odd - I created a large Excel file, and with the file closed, I got the above error, but with the file open, the code ran without errors. The computer I used has very little memory indeed, and the code ran fairly fast with the file open. — Fionnuala, Feb 17 '11 at 14:54
If you are trying to replicate..let me know which part of the code u need... — Whimsical, Feb 17 '11 at 15:42
I have been messing around quite a bit, and the only way I can get this to work is by importing into Access (Jet) and referencing that file. Note that the import did not work until I opened the large Excel file. Curiouser and curiouser. — Fionnuala, Feb 17 '11 at 17:45

score 0 · Answer 1 · answered Feb 18 '11 at 14:41

0

You are using ACE so i assume it is a 32bit platflorm. Win2k3?

Have you tried it with the /3GB switch in boot.ini?

The virtual address space of processes and applications is still limited to 2 GB unless the /3GB switch is used in the Boot.ini file. http://www.microsoft.com/whdc/system/platform/server/pae/paemem.mspx

With /3GB you get one GB extra which might just do the trick?

answered Feb 18 '11 at 14:41

Pleun

8,856
2
30
50

Im on a 64bit platform...using 64bit drivers. Check question update – Whimsical Feb 18 '11 at 14:48
And I dont think there should be a memory issue...The above function is just reading the names of the worksheets – Whimsical Feb 18 '11 at 14:50

score -1 · Answer 2 · answered Feb 14 '11 at 15:59

-1

read this article

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

maybe your file has more then 1,048,576 rows by 16,384 columns?

answered Feb 14 '11 at 15:59

Silagy

3,053
2
27
39

the file is a valid excel file...I can open it fine in excel 97 – Whimsical Feb 14 '11 at 16:25
how much columns you have in your file? – Silagy Feb 14 '11 at 16:53

ACE oleDb drivers unable to handle huge excel files

2 Answers2

Linked