not a legal OLEAut Date RRS feed

  • Question

  • User-342061962 posted

    I am trying to read data from an excel file in my c# code.

    On some files I randomly get "Not a legal OLEAut date". I checked the dates in the file and they seem to be legal dates. I am unable to figure out this issue.

    I have spent hours trying to figure this out. Any help will be appreciated.

    This is the code I am using:

    DataSet ds = new DataSet();
                using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(GetExcelOLEDBConnectionString(xlsPath)))
                    // Select the data from Sheet1 of the workbook.
                    using (System.Data.OleDb.OleDbDataAdapter oleda = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", conn))
                        oleda.Fill(ds, sheetName);

    And the connection string:

    "provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filename + "; Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

    Friday, November 8, 2013 2:03 PM

All replies

  • User465171450 posted

    One of the tricky about working with excel is how it determines datatypes. It will often just scan the first 8 rows (I think, It may be as high as 12 but I thought it was 8) and determine the datatype based on that. If the rows have funky values in them, or are blank, it may just decide it's text instead. I've had that happend to me a number of times with dates and currency especially.

    Friday, November 8, 2013 6:30 PM
  • User-1910946339 posted

    Do you have examples of the data which triggers the error?

    Sunday, November 10, 2013 10:08 PM
  • User-342061962 posted

    I think I figured out the cause of the issue.

    One of the columns I am trying to read has dates. In the bottom of the sheet (about 8-10 lines down) I have some assumptions stated that have some numbers in that partcular column. When those numbers are big then this issue arises.

    I forced the code not to read the rows where i add text for information purposes by using this code:

    OleDb.OleDbDataAdapter oleda = new OleDb.OleDbDataAdapter("select * from [" + sheetName + "] where Number is not null", conn)

    Thanks for your responses.



    Monday, November 11, 2013 12:46 PM
  • User-1910946339 posted

    You will probably be better off in an Excel developer forum.  I can't see that this has anything to do with ASP.Net

    Perhaps http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Monday, November 11, 2013 3:44 PM
  • User1225373008 posted

    Check in excel file for related  date column,cell is in date format. Also checkout all other column,cell that are not used for date and those are not defined or set as date.

    Thursday, August 13, 2015 6:39 AM