OleDb - slow reading from Excel

Question

I have table with several thousand rows, but even if I try to read 1000 rows (and 10 columns) it takes about 10 second to get result. I think it's too slow, but I can't find any problem.

Code for reading data:

string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 8.0;HDR=Yes;"", _excelFilename);
using (OleDbConnection c = new OleDbConnection(connectionString)) {
    c.Open();
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Trend_Data$A1:J1000]", c);
    OleDbDataReader dbReader = dbCommand.ExecuteReader();

    while (dbReader.Read()) {
        object[] values = new object[10];
        dbReader.GetValues(values);

        //save values into internal structure - fast
        ...
    }

Stepping through the code I found that calling ExecuteReader function takes so long.

Do you have any ideas, how to speed up code?

Is your file stored locally? The execution will take a while if on a shared drive. — Juliusz, May 17 '11 at 08:31

score 1 · Answer 1 · edited May 23 '17 at 12:19

Try this OleDBAdapter Excel QA I posted via stack overflow.

I took my worksheet from the previous post and added columns (10 total) and rows (10,000 total) and packed it w/ data (the excel file is 1,770 KB). Using XP w/ 4 cpus, Visual Studio 2010, it takes less than 1 second.

Add this at the top:

Stopwatch queryWatch = new Stopwatch();
queryWatch.Start();

and this at the bottom:

Console.WriteLine("Elapsed Query time: {0}.{1} seconds.", queryWatch.ElapsedMilliseconds / 1000, queryWatch.ElapsedMilliseconds % 1000);    
Console.ReadLine();

Output:

Elapsed Query time: 0.623 seconds.

OleDb - slow reading from Excel

1 Answers1