Microsoft Text Driver dropping some data during reading

I am using the Microsoft Text Driver (DSN-less mode) to read CSV files
in my application. these files are comma delimited, but not quoted by
double-quotes. they come from many customers, so I cannot easily
control their format.

I have found that when using the DSN-less connection of the Microsoft
Text Driver, in some cases it will not read some data.

more specifically - if you have a column that for some rows has a
numeric value, it seems the driver treats this column as numeric, and
if you have a non-numeric value in any other row for same column it
will not read the value (it will be NULL or empty)

This is not happening if the fields are quoted by double-quotes, but
again, this is coming from the clients.


any ideas ?

this is how I open and read the CSV file:
==============================

set conn = Server.CreateObject("ADODB.Connection")
set RS = Server.CreateObject("ADODB.RecordSet")
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & aPath & ";" & _
"Extensions=asc,csv,tab,txt" ,"", ""

sSQL = "SELECT * FROM " & aFileName
set rs = conn.Execute(sSQL)

do while not RS.EOF
.....


and this sample CSV ilustrates the issue. look at "field1"
========================================
Field1, Field2
1234,text
5678,text 2
nonmeric,text 3


the value for RS("Field1") for the last row will by NULL!

You need to look into using a Schema file. Also, there is a registry key
that you can tweak to get it to behave better (But I don't know what it is
off hand.)
Here is an article from MS, you can also find tons of info on the subject
on google, just search for ADO Schema.ini

http://msdn2.microsoft.com/en-us/library/ms974559.aspx

Lance

"yoram ayalon" wrote in message

>I am using the Microsoft Text Driver (DSN-less mode) to read CSV files
> in my application. these files are comma delimited, but not quoted by
> double-quotes. they come from many customers, so I cannot easily
> control their format.
>
> I have found that when using the DSN-less connection of the Microsoft
> Text Driver, in some cases it will not read some data.
>
> more specifically - if you have a column that for some rows has a
> numeric value, it seems the driver treats this column as numeric, and
> if you have a non-numeric value in any other row for same column it
> will not read the value (it will be NULL or empty)
>
> This is not happening if the fields are quoted by double-quotes, but
> again, this is coming from the clients.
>
>
> any ideas ?
>
> this is how I open and read the CSV file:
> ==============================
>
> set conn = Server.CreateObject("ADODB.Connection")
> set RS = Server.CreateObject("ADODB.RecordSet")
> conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
> "Dbq=" & aPath & ";" & _
> "Extensions=asc,csv,tab,txt" ,"", ""
>
> sSQL = "SELECT * FROM " & aFileName
> set rs = conn.Execute(sSQL)
>
> do while not RS.EOF
> ....
>
>
> and this sample CSV ilustrates the issue. look at "field1"
> ========================================
> Field1, Field2
> 1234,text
> 5678,text 2
> nonmeric,text 3
>
>
> the value for RS("Field1") for the last row will by NULL!
>