c# - LinqToExcel Not Parsing Date -

i working client import rather larger excel file (over 37k rows) custom system , utilizing excellent linqtoexcel library so. while reading of data in, noticed breaking on records 80% in , dug little further. reason fails majority of records (with associated dates ranging 2011 - 2015) normal, e.g. 1/3/2015, starting in 2016, structure changes this: '1/4/2016 (note "tick" @ beginning of date) , linqtoexcel starts returning dbnull column.

any ideas on why , ways around it? note isn't casting issue - can use immediate window see values of linqtoexcel.row value , column index is, it's empty.

edit

here code using read in file:

var excel = new linqtoexcel.excelqueryfactory(path.combine(this.filepath, this.currentfilename)); foreach (var row in excel.worksheet(file.worksheetname)) {     data.add(this.fillentity(row)); } 

the problem i'm referring inside row variable, linqtoexcel.row instance , contains raw data excel. values inside row line up, exception of column date empty.

** edit 2 **

i downloaded linqtoexcel code github , connected project , looks issue deeper library. uses idatareader read in of values , cells in question aren't being read empty level. here block of code linqtoexcel.excelqueryexecutorclass failing:

private ienumerable<object> getrowresults(idatareader data, ienumerable<string> columns)     {         var results = new list<object>();         var columnindexmapping = new dictionary<string, int>();         (var = 0; < columns.count(); i++)             columnindexmapping[columns.elementat(i)] = i;          while (data.read())         {             ilist<cell> cells = new list<cell>();             (var = 0; < columns.count(); i++)             {                 var value = data[i];                  //i added in, since worksheet has on 37k rows ,                  //i needed snag right before hit values looking                 //to see idatareader exposing. row inside                 //idatareader relevant column i'm referencing null,                 //even though data exists in excel file                 if (value.gettype() == typeof(datetime) && value.cast<datetime>() == new datetime(2015, 12, 31))                 {                 }                    value = trimstringvalue(value);                 cells.add(new cell(value));             }             results.callmethod("add", new row(cells, columnindexmapping));         }         return results.asenumerable();     } 

since class uses oledbdatareader retrieve results, think can't find value of cell in question. don't know go there.

found it! once traced down oledbdatareader failing , not linqtoexcel library itself, sent me down different path around. apparently, when excel file read oledbdatareader (as virtually utilities under covers), first few records scanned determine type of content associated column. in scenario, on 20k records had "normal" dates, assumed date. once got "bad" records, ' in front of date meant couldn't parsed date, value null.

to circumvent this, load file , tell ignore column headers. since header column string , of values dates, makes string because of mismatched types , values need loaded properly. there, can parse accordingly , work.

source: what imex in oledb connection string?