OleDbDataReader from Excel gives blank line

[Satal Keto]

Hi,

I am creating an application which uses OleDb to connect to an Excel spreadsheet and read the data into DataTable. The problem that I am having is that some of the columns I am getting are coming up blank, I've managed to narrow down that this is because that the first few lines for that column are blank.
As unfortunately this is valid in the context of the Excel spreadsheet as not all dates etc. are required to be filled in.
I was wondering whether anyone knows of any way that I could go about ensuring that the columns are populated by the OleDbDataReader even if the first x number of rows have that column as blank.

Thanks for any help in advance.

Satal

 

[Satal Keto]

jmcilhinney

Open RegEdit and navigate to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel key. Create a value named TypeGuessRows of type DWORD if one doesn't already exist and set the value to 0. This will force the Jet OLEDB provider to test the value of every cell in a column to determine the column data type instead of the default 8. This will make reading the data slower but it also means that the wrong data type won't be used so values that don't match that data type won't be ignored.