Read An Excel File With OLEDB?

Question

Hi I am reading an excel file with oledb(The file has 100000 rows). I must read file quickly.

string conn;

                conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
                ("Data Source=" + _filename + ";" +
                "Extended Properties=\"Excel 12.0;\""));
                OleDbConnection oleDBCon = new OleDbConnection(conn);
                oleDBCon.Open();
                DataTable dt = oleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string excelsheetname = dt.Rows[0].ItemArray[2].ToString();
                string SSQL = "SELECT * from [" + excelsheetname + "]";

                OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
                DataSet ds = new DataSet();
                oleDA.Fill(ds);
                DataTable _DtTable = ds.Tables[0]; // or [ ds ]
                oleDBCon.Close();

and then in _DtTable with a for loop I am inserting these cells to DB.. How can I read this very large excel quickly? And insert to DB? I used Parallel.For but it is not true solution for me.. Any idea?

Digvijay · Accepted Answer · 2012-02-10 12:56:24Z

1

To add records to MyTable using ADO, you can use code similar to the following:

'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
  " values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
  " values ('Joe', 'Thomas')"
conn.Close

This is from MSDN: http://support.microsoft.com/kb/247412

answered Feb 10, 2012 at 12:56

Digvijay

3611 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Digvijay Over a year ago

I probably didn't read carefully enough. You wish to read from excel and insert into another DB, maybe SQL Server and not insert records in Excel file. To read excel files i prefer to use ExcelDataReader from exceldatareader.codeplex.com

mekar10 Over a year ago

thank you! but on first line it says "Lightweight and fast library written in C# for reading Microsoft Excel files ('97-2007)." I have excel 03/07/10 files.. Does ExcelDataReader support excel 2010 files?

Pranay Rana · Accepted Answer · 2012-02-10 12:54:45Z

1

Make use of OpenXML of SQL to insert data in bulk which do fater work for you

here is code did by me for same work : Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function

answered Feb 10, 2012 at 12:54

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

1 Comment

mekar10 Over a year ago

this is useful solution for insert? I didnt tired it? Are you sure is your code more good performance than from classic for loop on Datatable(like ı write up) ??

Henrik Gering · Accepted Answer · 2012-02-10 12:56:29Z

1

You could look at some of the ways the database can consume Excelfiles

answered Feb 10, 2012 at 12:56

Henrik Gering

1,8891 gold badge19 silver badges31 bronze badges

Collectives™ on Stack Overflow

Read An Excel File With OLEDB?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related