OleDbDataReader from Excel gives blank line

Satal Keto

Well-known member
Joined
May 15, 2006
Messages
86
Programming Experience
5-10
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 :D
 
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.
 
Back
Top