DateTime format mismatch on importing from Excel Sheet

Question

I'm importing data from an Excel sheet on to a DataTable using the following code:

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0");
con.Open();
_myDataSet = new DataSet();
OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + "Sheet1" + "$]", con);
myCommand.Fill(_myDataSet);
con.Close();

I have a Date column in the Excel sheet in the format dd/MM/yyyy. The above code is failing when the date is dd/MM/yyyy (eg. 27/12/2009). How to specify the date format?

EDIT (adding more details):

It is not throwing any exception. Data is imported to the DataSet until the row where an invalid Date format is encountered. I have the date as dd/MM/yyyy in Excel sheet. When I import using OleDbDataAdapter, it is expecting the date in the Excel sheet to be in MM/dd/yyyy. No naturally when it encounters a date such as 27/2/2009 it stops the process of importing, though no error/exception is thrown. So I'm having only partial results in DataTable.

Please help.

Answer 1

Pulling in Date or other mixed-data column items in the past has bit me with Excel.

It seems that their provider "peeks" ahead XX rows (depending on Provider version) to determine what the column type is at runtime. The prior answers that apply properties to your connection have helped me in the past, but do not help when you have BLANK dates and/or different values in the column itself - it confuses the Excel driver.

What I recommend is that you use FileHelpers or another third-party library instead. Infragistics Excel component has treated me very well, while FileHelpers is opensource.

Answer 2

When linking Excel spreadsheets in MS Access, a problem arises when a column has mixed data types. For example, if the first row in the column is "Text" and the remaining are Numeric, the Numeric fields will be formatted as Text and #Error out. Likewise with Dates and non-Dates mixed in the same column.

There are some nice attempts at answers up there, but here is the simple solution to read these Mixed data types without a data type mismatch error:

Try adding "IMEX=1" to your MS Access SQL, such as:

SELECT Type, Width, Weight

FROM [Excel 8.0;IMEX=1;DATABASE=C:\TEMP\MySpreadsheet.xls].MyExcelTable

Thanks, Brian Jasmer