[RESOLVED] Select from Excel returning null data instead of real data

[MMock]

I typed this question once already, hit preview, and somehow never got the preview and lost what I typed. It has been that kind of a day.

So here is my quick overview then I am going shopping. I am using OLE DB to select from an excel spreadsheet. It has over 15000 rows and 33 columns. Sometimes even when a cell is not null, it is returning a null value. If I just do a "select * where [termination date] is not null", for example, it will return no rows even though I can see with my own eyes that sometimes termination date is not null. If I select * where [termination date] is null, I get every single one of the 15000+ rows. Is Excel or Ole DB choking on the nulls for some reason?

[jmcilhinney]

This may or may not be your issue, but it's certainly the first thing I'd check. When using Jet or ACE to read data from non-database data sources, the engine will generally preview a small number of rows to determine the data type of each column. It makes a best guess based on the data it reads. Once a data type is established for each column, any data that is found in that column that doesn't conform to that type will be read incorrectly, often as NULL.

The default number of rows to use for this preview is 8. You can change that, but it requires editing the Registry. You can set it to a larger value if you like but, if you want to be sure, the safest option is to use 0, which means that all rows are used in the determination of column data types. This will make the operation of retrieving the data take longer, because the preview phase will take longer, but it's the only way to be sure that you will always use the most appropriate data type for the data you're reading.

I'm on Windows 7 x64 and the relevant Registry key for Jet is:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

For ACE the key would be:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

for Office 2007 or:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

for Office 2010. If you're on an x86 OS then you would omit the Wow6432Node. The relevant value in each case is TypeGuessRows.

[MMock]

Thank you, that was a great help and worked well with the termination date column. I still have one issue. I have this errant last column in my spreadsheet. It is column AH, which makes it reader.item(33) after I read the data into an OleDbDataReader. This is also a column that sometimes has a date and sometimes has a blank. Now if I reference it with its name, that is, reader.Item("C annuitant"), it bombs with an index out of range error. But if I reference it with the numeric index, 33, it's fine. So it just seems like there is something screwy going on with this field. And if I give in and say fine, I'll just call it reader.item(33), I am still not getting the data in that variable that I see in Excel. Is this related to my original problem or something different and more screwy?

[MMock]

The problem must either be because it's a date column that is sometimes null or because it's the last column. If I look at (33), my code is telling me it read a null. This should be valid date for the row I am looking at. Because if I look at (32), which is a sex field, I have am "F". I also have a first name and last name (30) and (31). These fields all go together - either they are all filled in or all empty. So why are 30-32 read correctly, but 33 is not??

[MMock]

Now if I reference it with its name, that is, reader.Item("C annuitant"), it bombs with an index out of range error. But if I reference it with the numeric index, 33, it's fine.

I fixed that quirky error by selecting the column list explicitly, instead of select *. It now knows what "C annuitant" is. I'm not 100% happy with this decision, because if the client who provides us this input changes their column headings in the spreadsheet, that will require a coding change.

[jmcilhinney]

Does it work if you use a column name without spaces?

[MMock]

I don't know...but of my 33 columns, many have spaces in the names, such as "C annuitant last name" (that is another one in the set of data that is either all-are-null or none-are-null, along with "C annuitant"), and these other ones work fine...

[MMock]

I tried renaming the column to Cannuitant (removed the space) and it didn't matter.

I am beginning to want to give up on this as it doesn't seem reliable. There are other ways I can read an Excel file in VB.NET code? Basically I want to read it, delete some records that meet certain criteria, do a little bit of reformatting, and write it back out. People are doing this manually now, so I was trying to make their lives easier, but mine has become a nightmare... I was just saying the other day how cool I thought it was that you could treat an Excel file like a database...

[MMock]

I am reading the input data from a CSV file instead. It is working well, and much more reliably. Now I am wondering if the input file we get from another client which is also Excel and worked fine with OLE DB, should be rewritten. It works fine now, but what about if null data start coming in in one of the columns??? If I can't trust it here, why should I trust it there?

[MMock]

I am marking this thread closed as my original question was answered, though my last remains to be answered, but time will tell. Thanks, John.