Problem Parsing Excel File 1

[Mugs321]

Hey all,
I'm creating an Excel import utility using ASP/VBScript and I'm running into a wierd problem.

Once I'm in the proper record, I'm trying to parse the row and return the cell contents. Here's the (rough) code:

'Conn string
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("/ecr")&"\xlsTest.xls;Extended Properties=Excel 8.0"
oConn.Open strConn

'Select data
strSql = "SELECT * FROM [Sheet1$]"
Set oRS = oConn.Execute(strSql)

For each varItem in oRS.Fields
   response.write varItem&"<BR>"
Next

... pretty simple.

My problem is that my code always returns an empty string for any cell that has a date in the cell directly below AND there's any non-numerical chars in the cell in question. (ie. '123' returns fine, '12a' returns empty)

I've tried changing the cell to Text, General etc.. but nothing works. The truly odd thing is that all other cells return exactly as they should (non-numerical or not, column E or not).

Hope I didn't confuse anyone... any thoughts?

 

[Mugs321]

Through a little more testing I've noticed the following:

The bottom cell only has to be a number (not just a date)... and even if I change that cell to 'Text', the problem still persists.
Also, adding an empty row between the headings and the data makes not difference at all.

 

[tsuji]

The problem is that the provider is guessing the schema of the database. To resolve the problem, forcing the return as string type value by adding IMEX=1 to the connection string extended properties. Like this.
[tt]
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("/ecr")&"\xlsTest.xls;Extended Properties=[blue]""Excel 8.0;IMEX=1""[/blue]"
[/tt]
A good reference on various details is the mskb article 257819.

http://support.microsoft.com/kb/257819

 

[tsuji]

Further note:
I suppose you know you've to loop through the rows otherwise what you've shown will write out one row only.
[tt]
'Select data
strSql = "SELECT * FROM [Sheet1$]"
Set oRS = oConn.Execute(strSql)
[blue]
oRS.movefirst
do while not oRS.eof[/blue]
For each varItem in oRS.Fields
response.write varItem&"<BR>"
Next
[blue]oRS.movenext
loop[/blue]
[/tt]