Excel import returns blank cells

Question

I have had this problem forever and never managed to figure it out.

I am importing an excel (.xls) file into an asp recordset. Most of the time this works great.

I have column with the following values

4
4
5,6
3

Asp reads those values in except for the 5,6. I have tried formatting the cells and this makes no difference. It appears that asp (or excel) are trying to determine the type of the cell by examining the value. For whatever reason it then throws this hiccup if some of the cells aren't the same format as the majority of the other are.

Answer 1

Have you tried setting the 5,6 to 5.6?

Answer 2

The problem is that ADO scans the first 8 rows and based on the data it finds in each column it sets the column type. So if your first 8 rows contain numbers then it sets that column to numeric and returns null for any other values, for example if the ninth row contains text or a comma. See http://blog.lab49.com/archives/196 for some suggestions on how to avoid this.