Last post Aug 13, 2015 06:39 AM by Shrinath25
Nov 08, 2013 02:03 PM|MGuser|LINK
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))
And the connection string:
"provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filename + "; Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
Nov 08, 2013 06:30 PM|markfitzme|LINK
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.
Nov 10, 2013 10:08 PM|Paul Linton|LINK
Do you have examples of the data which triggers the error?
Nov 11, 2013 12:46 PM|MGuser|LINK
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.
Nov 11, 2013 03:44 PM|Paul Linton|LINK
You will probably be better off in an Excel developer forum. I can't see that this has anything to do with ASP.Net
Aug 13, 2015 06:39 AM|Shrinath25|LINK
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.