3

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
Marcel
  • 15,039
  • 20
  • 92
  • 150
Sophonias
  • 874
  • 5
  • 16
  • 38
  • 2
    Do you know which field in the spreadsheet is producing the error? If so, what does the field contain? – user1201210 Sep 26 '12 at 16:01
  • possible duplicate:http://stackoverflow.com/questions/310700/meaning-of-exception-in-c-sharp-app-not-a-legal-oleaut-date – Prabhu Murthy Sep 26 '12 at 16:40
  • @CodeIgnoto Its not a duplicate!!! .. I looked at the question first. none of the suggestions and solutions apply for me. – Sophonias Sep 26 '12 at 17:07
  • @Dynguss It doesn't say which row it has a problem on it only says "Not a legal OleAut date." ... there are three date fields in the spreadsheet. – Sophonias Sep 26 '12 at 17:10
  • Is there a stack trace associated with the error? Are you able to eyeball the spreadsheet yourself and see which values are wrong? – user1201210 Sep 26 '12 at 17:14
  • its a small console class that I use to quickly filter a lot of data. I have almost 18 thousand records in there it is really difficult to identify which date is wrong manually. and the thing is it didnt have that problem earlier when i did the same thing with the same file. There is a date from 1968 but i dont think that is old enough to cause problems. also i didnt see any stack trace data that would have been very helpful piece of info. – Sophonias Sep 26 '12 at 17:37
  • @Sophonias can you explain what changes you recently made with the code? Have you tried changing the code back to the previous version and testing it to see if you get the same error? – Mike Kellogg Sep 26 '12 at 18:43
  • Try opening the spreadsheet in Excel and sorting the three date columns in turn. An invalid date should end up being the first or last entry in the column – barrowc Sep 27 '12 at 22:47

5 Answers5

8

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.

Sophonias
  • 874
  • 5
  • 16
  • 38
0

Look for different date formats, in my case one of the column was in dd/mm/yyyy format and the other was in mm/dd/yyyy. Once the date formats were rectified data import worked.

Prem
  • 119
  • 1
  • 4
0

In my case, I had a date in my table inserted manually with the wrong format, it was 01/01/0019 instead of 01/01/2019.

Shamal Sabah
  • 229
  • 2
  • 7
0

I have this issue while reading excel in oledb.

I found that in excel many date columns are empty in the beginning records. i had 6000 records in excel and one of the datecolumn has around first 4000 empty cells so excel is not able to understand what to convert the cell so i added one dummy record fill with date and process my file. Or you can move few records which has date value in it at top

KMR
  • 101
  • 2
  • 3
0

How can you fix this? Hi!jack your data with a dummy row at row 1 and force the column(s) in question into a string (in this case only - it is a data type error, so apply the fix according to the type).

It is necessary to understand what the data adaptor does, which interprets the data type of each column by examining, by default, the first 8 rows of data (sans header if HDR=Yes in connect string) and deciding on a data type (it can be over-ridden -yes there is an override - in the connection string to 16 rows - almost never very helpful).

Data adaptors can do other nasty things, like skip strings in columns of mixed data types, like string/double (which is really just a string column, but not to the adaptor if the first rows are all double). It won't even give you the courtesy of an error in this example.

This often occurs in data coming from ERP sources that contains "Free Form" columns. User defined columns are the usual suspects. It can be very difficult to find in other data type issues. I once spent quite a bit of time resolving an issue with a column that typed as a string with a max length of 255 chars. Deep in the data there were cells that exceeded that length and threw errors.

If you don't want to advance to the level of "Genetic Engineering" in working with data adaptors, the fastest way to resolve an issue like this is to hi!jack your data and force the column(s) in question to the correct type (or incorrect, which you can then correct in your own code if need be). Plan B is to give the data back to the customer/user and tell them to correct it. Good luck with Plan B. There is a reason it isn't Plan A.

More on manipulating via the connection string and similar issues with the adaptor - but be wary, results are not going to be 100% fool proof. I've tested changing IMEX and HDR settings extensively. If you want to get through the project quickly, hi!jack the data. OleDB & mixed Excel datatypes : missing data

Here is another posting similar in context, note all of the possible time consuming solutions. I have yet to be convinced there is a better solution, and it simply defies the logic a programmer brings to the keyboard every morning. Too bad, you have a job to do, sometimes you have to be a hack. DateTime format mismatch on importing from Excel Sheet

jb_
  • 11
  • 1