VB Forum / Database Access / September 2007

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!