Dailycode.info

Short solution for short problems

How to read an excel 2007 or 2010 file with the OleDbConnection.

How to read an excel 2007 or 2010 file with the OleDbConnection. My mistake was that I started out from a older connection string:

 

sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties=Excel 8.0;";

 

 

I got 2 different errors:

1 Could not find installable ISAM.

2 External table is not in the expected format.

It could only be fixed off course by using the correct connection string. So instead of using the Jet.OLEDB I used the ACE.OLEDB (Microsoft Access Database Engine 2010)

 

sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadPath + ";Extended Properties=Excel 12.0";

 

 

Then here is the fill code to read en Excel file into a DataSet

 

private void Upload(string uploadPath)
        {
            //Doupload
            string sConnectionString;
            //sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties=Excel 8.0;";
            sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadPath + ";Extended Properties=Excel 12.0";
           
            OleDbConnection Exlcon = newOleDbConnection(sConnectionString);
            string sDate = "";
            string sRowsNtInserted = "";
            int iRows = 0;
            DateTime date1;
            string sMonthName = "";
 
            try
            {
                Exlcon.Open();
            }
            catch
            {
                //ShowAlertMessage("Please select valid excel file.");
                return;
            }
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", Exlcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
 
            //Read the dataset line by line and process the tasks, see if they already exist.
            if (objDataset1 != null)
            {
                foreach (DataRow dr in objDataset1.Tables[0].Rows)
                {
                    //dosomething
                }
            }
        }