Reading XLS data, some fields return NULL?

[XgrinderX]

Hey everyone!

I am reading an XLS spreadsheet within an ASP page and getting weird results. Here is a sample of the data I am reading:

1 2 3
9.5.07 Change Legend
45% 1 1 = Offense, 0 = Defense

The first row is always read as the field names so row 2 is index 0. So the data should be like this:

(0,0) = 9.5.07
(1,0) = Change
(2,0) = Legend
(0,1) = 45%
(1,1) = 1
(2,1) = 1 = Offense, 0 = Defense

But what I get when I read it is this:
(0,0) =
(1,0) =
(2,0) = Legend
(0,1) = 45%
(1,1) = 1
(2,1) = 1 = Offense, 0 = Defense

Is there something I should know about readin XLS files? This really doesn't make any sense to me.

-Greg

 

[Tarwn]

I have noticed this in the past, though generally not on the first row. The driver that reads the excel file does some sort of black magic to guess the data type for the column based on the majority of the column contents. Thats all fine and good and apparently works. However, the part that leaves us all gasping in wonder when we finally figure it out is that the driver then proceeds to return nulls for any value that is of another type. And I don't mean "can't be converted to the chosen type", I mean "I'm not going to bother to try and convert that, it just ain't what I'm looking for". That means that if your whole column is a text type but one value somewhere in that column is a number, it returns a null for that value. And vice versa for a column of numbers and one value that is a number-stored-as-text.

So usually the answer is to highlight the whole column and format it the same. This seems to appease the idiotic driver some of the time.

Is this silly? yes. The longer I program with MS languages/tools the more I realize that everything they do is sprinkled with these little bits of genius. It's like there is one programmer at MS that gets included in all of the projects that doesn't quite know what's going on but is always given two or three little out-of-the-way projects to keep him busy. He worked on the excel driver, he worked on the HTTPWebRequest object (throws exceptions for valid HTTP return codes) in .Net, he wrote the Exit code for the .Net Application object (doesn't exit), he was the one that decided it should be (col,row) for the ADO GetRows method, etc. heck, it's probably him that decided to use delegates for Events in .Net 2, giving us events that throw exceptions if no one is there to catch them. And it doesn't stop there, have you ever noticed that your application memory use doesn't add up to the amount of used memory in Task Manager? Or tried to set custom paper sizes in VBA code?

Sorry, I have lost hours of my life to this "feature" in the excel driver, it's driven me a little mad

 

[tsuji]

The proper way to stop the guessing so as to return all the data is to supply the extended property, namely IMEX, of the connection string and give it value 1 as string data type. A typical string would be something like this.
[tt]
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[green]abc.xls[/green];Extended Properties=""Excel [green]8.0[/green];HDR=no;[blue]IMEX=1[/blue]"""
[/tt]
ps: Path to the xls file and the excel version are for you to adjust to your environment.