How to fix "Not a legal OleAut date." when reading an Excel file in C#?

c# excel oledbdataadapter
Sophonias picture Sophonias · Sep 26, 2012 · Viewed 25.4k times · Source

I have been working with excel spreadsheets and so far I never had any problems with them.. But this error,"Not a legal OleAut date.", showed up out of the blue when I tried to read an excel file. Does anyone know how I can fix this. Here is the code I use to read the excel and put the data into a dataset. It has worked fine previously but after I made some changes (which doesn't involve dates) to the data source this error showed up.

var fileName = string.Format("C:\\Drafts\\Excel 97-2003 formats\\All Data 09 26 2012_Edited.xls");
        var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

        var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);

        DataSet Originalds = new DataSet();
        adapter.Fill(Originalds, "Employees"); // this is where the error shows up

Answer

Sophonias picture Sophonias · Oct 11, 2012

I sort of figured out a work around to this problem I changed the connection string to the latest oleDB provider.

var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'", fileName);

I also made sure that the empty space after the last row of my excel data is not being read as a value.

Related questions

How to SELECT from any spreadsheet in Excel File using OleDbDataAdapter

I'm using OleDbDataAdapter to extract DataSet from excel file, but I have problems with SELECT statement inside DataSet excelDataSet = new DataSet(); using (OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString)) { con.Open(); OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * …

Read More
Quirky SELECT from Excel file via OleDbDataAdapter method (C#)

I have got an Excel file in this form : Column 1 Column 2 Column 3 data1 data2 data1 data2 data1 data2 data1 data2 data1 data2 data3 That is, the whole Column 3 is empty except for the last row. I am accessing the Excel …

Read More
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

How can I create an Excel spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?

Read More