Infragistics Forums

Any example code to read from Excel into my database / dataset

We receive database exports from another application in Excel format that I need to import into my application's database

string strFilePath = this.MapPath(".") + "\\Workbook.xls";
        string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES\"";
        System.Data.OleDb.OleDbConnection cnCSV = new System.Data.OleDb.OleDbConnection(strConnectionString);
        cnCSV.Open();
        System.Data.OleDb.OleDbCommand cmdselect = new System.Data.OleDb.OleDbCommand();
        if (true)
        {
            cmdselect.CommandText = "SELECT * FROM [Sheet1$A1:C6]";
            cmdselect.Connection = cnCSV;
        }
        System.Data.OleDb.OleDbDataAdapter daCSV = new System.Data.OleDb.OleDbDataAdapter();
        daCSV.SelectCommand = cmdselect;
        DataTable dtCSV = new DataTable();
        daCSV.Fill(dtCSV);

        cnCSV.Close();
        daCSV = null;
       

The code above should read in an excel file into ur datatable.  You will have to change the select command for your purposes.

1.The amount of data is not always the same that I need to import so I do not know the exact range, can we somehow select all the rows with data?

2. the other issue is the date format understood by MYSQL is not the same as the excel date format.... So I need to reformat so it will be understood by MySql when I import

For the first question, I believe you can just remove the cell range and it will import the entire sheet.

I do not know the answer to the second question but here is a link to an article that gives an end to end solution:

http://aspnet.4guysfromrolla.com/articles/031208-1.aspx