Shall I read an excel file via OleDb Jet4.0 and save into dataset?

Question

I have to read an excel file and put into a dataset.

Shall I read excel file content via OleDbDataAdapter, and then Fill into a dataset? I tried but faild. It said the application cannot recognize database format when data adapter is doing Fill method.

Code:

String queryAll = "SELECT * FROM [Sheet1$]";
String xlsPath = Directory.GetCurrentDirectory() + "\\paid.xls";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath;
try
{
    m_dbDA = new OleDbDataAdapter(queryAll, strConn);
    DataSet dsPaidXls = new DataSet();
    m_dbDA.Fill(dsPaidXls);  //exception here
 }
 catch (System.Exception ex)
 {
    MessageBox.Show(ex.Message);
 }

Does it mean there is no way to directly read an excel data and put into a new dataset? And the only one way is to read excel data cell by cell and insert to a new DataSet with datatable?

Thanks in advance.

========================================
Resolved
========================================

String queryAll = "SELECT * FROM [Sheet1$]";
String xlsPath = Directory.GetCurrentDirectory() + "\\paid.xls";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath +
                 ";Extended Properties='Excel 8.0;IMEX=1';";
try
{
   m_dbDA = new OleDbDataAdapter(queryAll, strConn);
   DataSet dsPaidXls = new DataSet();
   m_dbDA.Fill(dsPaidXls,"[Sheet1$]");
   dataGridView1.DataSource = dsPaidXls;
   dataGridView1.DataMember = "[Sheet1$]";
 }
 catch (System.Exception ex)
 {
     MessageBox.Show(ex.Message);
 }

Fionnuala Fionnuala · Accepted Answer · 2011-08-22T10:11:24

OLEDB works quite well once you have the correct connection string and are aware of issues with data types. Jet is for versions prior to 2007 and you need to add extended properties for Excel.

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
  + xlsPath + "Extended Properties='Excel 12.0 Xml;HDR=YES';";

See: Connection Strings
How To Use ADO with Excel Data from Visual Basic or VBA (contains useful notes)
Various notes

Shall I read an excel file via OleDb Jet4.0 and save into dataset?

3 Answers