CODEWITHSUNDEEP
c#
David
David

Reputation: 33

C# At opening large Excel file (150MB) by OledbConnection System.OutOfMemoryException

I have to open a large excel file 150MB with half millon of records (excel 2007 .xlsx file) , and i have to migrate it to sql server, I use oledbconnection , however when I try to open the connection it takes more than 5 minutes and after that a System.OutMemoryExcepion appears. I think this connection tries to open all file in memory , how can I read row by row. I am working with Visual Net 2005 and C#

string archivoExcel="c:\largeExcel.xlsx"
string conneStringInraSIS = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                                    "Data Source=" + archivoExcel + ";" +
                                    "Extended Properties='Excel 12.0;HDR=YES;'";


OleDbConnection connexcel = new OleDbConnection(conneStringInraSIS);
connexcel.Open();

Upvotes: 1

Views: 3096

Answers (4)

Abhishek
Abhishek

Reputation: 11

I have faced this issue. The best thing I found in dealing with this problem is that go to codeplex.com and download Excel.dll. This dll will solve this issue very easily. Using this you can directly save your data to a dataset and do what ever you want to do with it then. You would find a sample code at site.

Upvotes: 1

Gabriel McAdams
Gabriel McAdams

Reputation: 58251

New Answer (based on information in comments):

Look into VSTO. I would create an addin that would allow the manager to click a ribbon button to upload the data into SQL Server, and then retrieve information from SQL Server (this will allow Excel to handle its own files, exactly how it does best).

Here is a link that should get you started: MSDN Code Sample

and you can download VSTO here.

Upvotes: 0

shf301
shf301

Reputation: 31394

Some of the comments suggested exporting the Excel files to CSV and parsing the CSV file line by line, but that seems to not be a solution to your problem.

What you can do however is use the Excel ActiveX objects to open the xlsx file and save it as a CSV from code. The Workbook object has a SaveAs method that takes a file format.

So you can get the CSV data straight from the xlsx file and not have to have your clients worry about it.

This assumes that you have Excel installed on the system running this process.

Upvotes: 0

spender
spender

Reputation: 120380

Priority #1 should be getting the data out of the xlsx spreadsheet. Apply business logic once this has occurred. I'd export CSV from the sheets and deal with them.

Upvotes: 1

Related Questions