DateTime format mismatch on importing from Excel Sheet

Asked 2009-06-08T13:50:44.220

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.

Edit Report

3 Answers

How many rows do you have, and are all the cells in the date column valid dates? Sometimes the OleDB routines will incorrectly identify a column as text if there is some inconsistency in the cells in the first 8 rows. (8 is the default number of rows that is read to determine data types)

answered 2009-06-08T14:32:02.297

One thing you should always specify in your connection string is IMEX=1:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;IMEX=1\"

It helps with parsing columns that contain both numbers and strings. Might help with date parsing as well, but then you would have to manually convert all dates with:

System.IFormatProvider format = new System.Globalization.CultureInfo("en-US", true);
DateTime d = DateTime.Parse(dataSet.Tables[0].Rows[i]["MyDate"] as string,format);

answered 2009-06-14T10:02:38.600

You can use this function to format whatever date format you received to the format you need.

Here is the code:

Public Shared Function ConvertToDate(ByVal dateString As String, ByRef result As DateTime) As Boolean
Try

'Here is the date format you desire to use
Dim supportedFormats() As String = New String() {”dd/MM/yyyy”}

'Now it will be converted to what the machine supports
result = DateTime.ParseExact(dateString, supportedFormats,System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None)

Return True
Catch ex As Exception
Return False
End Try
End Function

answered 2009-06-16T13:51:08.360

DateTime format mismatch on importing from Excel Sheet

3 Answers

Your Answer