CODEWITHSUNDEEP
c#.netexcelconnection
Zla Baba
Zla Baba

Reputation: 71

Connection to large excel file is very slow

I have a very large excel file, it has about one milion rows and have size about 400MB. I need to estabilish some System.Data.IDbConnection to this file. For now, i tried two aproaches: 1)

System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\file.xls';Extended Properties='Excel 12.0;HDR=YES;IMEX=1';");

2)

System.Data.Odbc.OdbcConnection("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=<FILE>;ReadOnly=True;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"")

Both connections work just fine for small files (25MB). But even for 25MB file, opening connection take about 4seconds.

When I use 350MB file, this simple command: 

connection.Open();

takes more than 3 minutes and i cannot use so slow connection.

Is there any free sollution to get fast IDbConnection for reading large excel file? Thanks for any help.

Upvotes: 0

Views: 1704

Answers (1)

manuerumx
manuerumx

Reputation: 1250

Try a different aproach, maybe if you use an access database, where de excel sheets are linked, you can gain time. The connection between access and excel is wierd but in many cases faster than import Excel files.

Upvotes: 1

Related Questions