9

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.

Smi
  • 13,850
  • 9
  • 56
  • 64
softwarematter
  • 28,015
  • 64
  • 169
  • 263
  • If you open the sheet in excel, are the cells recognized as dates? can you apply for example the "month" function on them? – tekBlues Jun 08 '09 at 14:06
  • What do you mean the code is failing? Is there an exception, or is the data just not being recognized as a date time? – John Weldon Jun 08 '09 at 14:30
  • 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 stop the process of importing, though no error/Exception is thrown. Pls help! – softwarematter Jun 09 '09 at 05:47

10 Answers10

4

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.

Brett Veenstra
  • 47,674
  • 18
  • 70
  • 86
4

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

  • This solved the problem for me. Basically my code handles the data input based on culture info. So all I needed was the data from the excel to be in string format not DateTime format. Adding "IMEX=1" to the connections string did the trick. – Rahatur Nov 07 '16 at 14:32
1

I also faced the same Issue. The Date column in the Excel sheet is dd/MM/yyyy. But as per my system settings the data format is MM/dd/yyyy. Therefore if the date is 31/07/2013 for example , the Date field displayed as empty string.

Using IMEX1 along with "Microsoft.ACE.OLEDB.12.0" Driver Solved the Problem.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"

But "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + "; Extended Properties=Excel 8.0;IMEX=1" doesn't work.

user2588099
  • 11
  • 1
1

You could use the British Culture info to parse the date in British Format dd/MM/YYYY

CultureInfo culture = new CultureInfo("en-GB");
DateTime date = DateTime.Parse("27/12/2009", culture);

where "27/12/2009" is your date field

Stephen Binns
  • 765
  • 10
  • 17
  • 2
    This doesn't work as the `OleDbDataReader` automagically converts the text into a date before you can even get the raw value. – Enigmativity Dec 07 '12 at 01:16
0

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
0

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)

John Weldon
  • 39,849
  • 11
  • 94
  • 127
0

maybe try formatting the date column in excel then import?

iceangel89
  • 6,113
  • 8
  • 40
  • 55
0

You may want to check that the current culture you are running supports UK style dates.Here is a link on how to check and change this at the thread level http://support.microsoft.com/kb/306162

The OleDb connection string seems to support ";Locale Identifier=". I think the eb-GB identifier is 2057 (Don't quote me ;)), you could also give that a try.

As for the exceptions not being thrown i think during the fill you may want to look into the RowUpdated events as per this link http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.rowupdated(VS.80).aspx.

This is how the Fill method of the DataSet works it will quit on the row where there is a problem, using the above events you may have some options on ignoring the row or reformatting.

Matthew Pelser
  • 936
  • 6
  • 11
0

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);
AareP
  • 2,355
  • 3
  • 21
  • 28
0

How about filling the dataset programmatically instead of via a data adapter?

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0");

Dim _myDataSet As New DataSet
                Dim con As New OleDb.OleDbConnection
                con.Open()
                Dim cmd As New OleDb.OleDbCommand(" SELECT * FROM [" + "Sheet1" + "$]", con)
                Using dr = cmd.ExecuteReader()
                    While dr.Read
                        Dim row = _myDataSet.Tables(0).NewRow()
                        With dr.Item("excel date column").ToString
                            Dim dt As New Date(CInt(.Substring(6)), CInt(.Substring(3, 2)), CInt(.Substring(0, 2)))
                            row.Item("dataset datecolumn") = dt
                        End With

                        \*'populate other columns here' *\
                       _myDataSet.Tables(0).Rows.Add(row)
                    End While


                End Using
Maslow
  • 18,464
  • 20
  • 106
  • 193