Displaying Excel via ADO - Troubles with commas in excel field

[shu]

Hi All

I have my all my excel fields display just fine, except fields which have commas in them. In those cases ASP just seems to ignore it.

Here's the ASP:
-------------------
<%
Dim cn,rs,strSQL,cnt
Set cn =server.createobject("ADODB.Connection")
Set rs =server.createobject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\inetpub\

http://wwwroot\lae\LibraryResources_complete.xls;"

& _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "SELECT * FROM [All resources$]"

response.write("<table border=""1"">")
response.write("<tr>")
response.write("<th width=""200"">Title</th>")
response.write("<th width=""100"">Author</th>")
response.write("<th width=""50"">Cost</th>")
response.write("<th width=""150"">Competency(s)</th>")
response.write("<th width=""200"">Description</th>")
response.write("</tr>")

rs.open strSql,cn,2,2
cnt=1
Do While NOT rs.EOF

response.write("<tr>")
response.write("<td>"&rs("Book Title")&"</td>")
response.write("<td>"&rs("Author")&"</td>")
response.write("<td>"&rs("Cost)&"</td>")
response.write("<td>"&rs("Competency")&"</td>")
response.write("<td>"&rs("Description")&"</td>")
response.write("</tr>")

cnt = cnt+1
rs.movenext
Loop
rs.close

response.write("</table>")
cn.Close
Set cn = Nothing
%>
-------------------

Here is an example excel file
********************************************
Book Title Author Cost Competency
-------------------------------------------
Title1 Author1 129 5
Title2 Author2 40 5
Title3 Author3 36 3,4
Title4 Author4 15 1,2,4
Title5 Author5 10.20 5
*********************************************

When I output the data on the asp page i get something similar to this:

Book Title Author Cost Competency
-------------------------------------------
Title1 Author1 129 5
Title2 Author2 40 5
Title3 Author3 36
Title4 Author4 15
Title5 Author5 10.20 5

It seems to ignore the Competency field when it has a comma in it.

Any ideas?

Thx in advance.
Shu

 

[tsuji]

The problem is you must have stored the data in the column "competency" of mixed type: the first two rows stored as number (5) whereas the 3rd, 4th rows as string (3,4) and (1,2,4)... etc. This is no good for oledb provider, because you force the provider to "literally" guess and consequently guess wrong. How, it guess is described in this article.

http://support.microsoft.com/kb/257819/en-us

Attention is drawn in particular to the section entitled:
[tt] [green]Considerations That Apply to Both OLE DB Providers
A Caution about Mixed Data Types[/green][/tt]

The easiest cure is to click on the column of competency and set its number (tab) and choose stored as "string" for the whole column.

 

[tsuji]

amendment
>[self]The easiest cure is to click on the column of competency and set its number (tab) and choose stored as "string" for the whole column.

I should have said the easiest cure is to enter all data at that column as string. _[blue]Not just formatting[/blue]_ the display as string.

 

[shu]

I am not familiar with Excel. How do I go about displaying the data in that column as a string. I can see how to format it, tho "string" is not an option so I used "text". But I don't see where I can display it as "string". I am using Excel 2000.

Thanks again.
Shu

 

[tsuji]

I mean _not_ just displaying left justified default to string, but semantically a string as well. Enter 5 like '5 at the gui. That's all it means.

 

[shu]

Ok, I got it to work. I had to tweak the excel sheet a bit. I had to add an additional column which I had to refer to the competency column and output it as a string. BTW - I tried a DTS import to a SQL Server table. The compentency table column had the same problem of null table values where the excel sheet competency field had multiple comma delimited values.

Here is an example of the updated excel file
*******************************************************************
A B C D E
1 Book Title Author Cost Competency Comp to String
--------------------------------------------------------------
2 Title1 Author1 129 5 =""&D2
3 Title2 Author2 40 5 =""&D3
4 Title3 Author3 36 3,4 =""&D4
5 Title4 Author4 15 1,2,4 =""&D5
6 Title5 Author5 10.20 5 =""&D6
*******************************************************************

When I output the data on the asp page i get something similar to this:

Book Title Author Cost Competency Comp to String
----------------------------------------------------------
Title1 Author1 129 5 5
Title2 Author2 40 5 5
Title3 Author3 36 3,4
Title4 Author4 15 1,2,3
Title5 Author5 10.20 5 5

There you go!

Tsuji, thanks for your suggestions. I hope this helps anyone that encounters the same issue.

- Shu