Microsoft Text Driver dropping some data during reading - ODBC

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" <yoram.ayalon@structuredweb.com> wrote in message
news:1190045159.038779.6590@o80g2000hse.googlegroups.com...
>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!
>

thanks for the quick and acurate help!

the schema.ini method works! it was a bit tricky for me since I have
to create it on the fly, as the path and file names are dynamic, but
it works like a dream: my SCHEMA.INI file looks like this:

[Filename]
Format=CSVDelimited
Col1=Field1 Text
Col2=Field2 Text
....

and no code changes were neccessary, except generating the INI file.

thanks again

On Sep 17, 12:11 pm, "Lance Wynn" <LanceW...@community.nospam> wrote:
> 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" <yoram.aya...@structuredweb.com> wrote in message
>
> news:1190045159.038779.6590@o80g2000hse.googlegroups.com...
>
>
>
> >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!- Hide quoted text -
>
> - Show quoted text -

You do have to specify each field, and it's type. The Zip field should be
text.

"James D. Houston" <jd_houston@earthlink.net> wrote in message
news:e8yWddh%23HHA.4476@TK2MSFTNGP06.phx.gbl...
> I'm having a problem similar to the one Yoram was having. I'm importing a
> set of names and addresses from a .csv file. Every thing works fine
> except for the Zip Code field. If the field contains a standard 5 digit
> zip everything is fine. But if the field contains an extended zip code
> like 21134-4121 the field is imported as a null for that particular zip
> only. In other words, if I have 3 records where the zip codes are 06531,
> 21134-4121, 85441, the 06531 and 85441 zips are fine but 21134-4121 is
> null. I've tried using a schema.ini file but it either doesn't fix this
> problem or I'm using it incorrectly. Do I have to point to the schema.ini
> file in some way (it's in the same folder as the text file I'm importing)?
> Do I have to specify each column and it's type? Any help would be
> appreciated.
>
> Thanks
>
> Jim
> "yoram ayalon" <yoram.ayalon@structuredweb.com> wrote in message
> news:1190045159.038779.6590@o80g2000hse.googlegroups.com...
>>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!
>>
>
>

I'm having a problem similar to the one Yoram was having. I'm importing a
set of names and addresses from a .csv file. Every thing works fine except
for the Zip Code field. If the field contains a standard 5 digit zip
everything is fine. But if the field contains an extended zip code like
21134-4121 the field is imported as a null for that particular zip only. In
other words, if I have 3 records where the zip codes are 06531, 21134-4121,
85441, the 06531 and 85441 zips are fine but 21134-4121 is null. I've tried
using a schema.ini file but it either doesn't fix this problem or I'm using
it incorrectly. Do I have to point to the schema.ini file in some way (it's
in the same folder as the text file I'm importing)? Do I have to specify
each column and it's type? Any help would be appreciated.

Thanks

Jim
"yoram ayalon" <yoram.ayalon@structuredweb.com> wrote in message
news:1190045159.038779.6590@o80g2000hse.googlegroups.com...
>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!
>

Yes, I believe that is what the syntax is. It is not case sensitive. I am
not sure about the omitted columns requirement. you can try it and see, but
to be safe, I'd go ahead and include them all just in case you need them in
the future.

Lance
"James D. Houston" <jd_houston@earthlink.net> wrote in message
news:O151Maj%23HHA.1168@TK2MSFTNGP02.phx.gbl...
> Lance,
>
> Should the line for zip read Col12=zip Text? Is it case sensitive? And
> can I skip over blank columns or columns I don't want to import?
>
> Thanks for your help
>
> Jim
>
> "Lance Wynn" <LanceWynn@community.nospam> wrote in message
> news:uSZFV4h%23HHA.2752@TK2MSFTNGP06.phx.gbl...
>> You do have to specify each field, and it's type. The Zip field should
>> be text.
>>
>> "James D. Houston" <jd_houston@earthlink.net> wrote in message
>> news:e8yWddh%23HHA.4476@TK2MSFTNGP06.phx.gbl...
>>> I'm having a problem similar to the one Yoram was having. I'm importing
>>> a set of names and addresses from a .csv file. Every thing works fine
>>> except for the Zip Code field. If the field contains a standard 5 digit
>>> zip everything is fine. But if the field contains an extended zip code
>>> like 21134-4121 the field is imported as a null for that particular zip
>>> only. In other words, if I have 3 records where the zip codes are
>>> 06531, 21134-4121, 85441, the 06531 and 85441 zips are fine but
>>> 21134-4121 is null. I've tried using a schema.ini file but it either
>>> doesn't fix this problem or I'm using it incorrectly. Do I have to
>>> point to the schema.ini file in some way (it's in the same folder as the
>>> text file I'm importing)? Do I have to specify each column and it's
>>> type? Any help would be appreciated.
>>>
>>> Thanks
>>>
>>> Jim
>>> "yoram ayalon" <yoram.ayalon@structuredweb.com> wrote in message
>>> news:1190045159.038779.6590@o80g2000hse.googlegroups.com...
>>>>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!
>>>>
>>>
>>>
>>
>>
>
>

Lance,

Should the line for zip read Col12=zip Text? Is it case sensitive? And can
I skip over blank columns or columns I don't want to import?

Thanks for your help

Jim

"Lance Wynn" <LanceWynn@community.nospam> wrote in message
news:uSZFV4h%23HHA.2752@TK2MSFTNGP06.phx.gbl...
> You do have to specify each field, and it's type. The Zip field should be
> text.
>
> "James D. Houston" <jd_houston@earthlink.net> wrote in message
> news:e8yWddh%23HHA.4476@TK2MSFTNGP06.phx.gbl...
>> I'm having a problem similar to the one Yoram was having. I'm importing
>> a set of names and addresses from a .csv file. Every thing works fine
>> except for the Zip Code field. If the field contains a standard 5 digit
>> zip everything is fine. But if the field contains an extended zip code
>> like 21134-4121 the field is imported as a null for that particular zip
>> only. In other words, if I have 3 records where the zip codes are 06531,
>> 21134-4121, 85441, the 06531 and 85441 zips are fine but 21134-4121 is
>> null. I've tried using a schema.ini file but it either doesn't fix this
>> problem or I'm using it incorrectly. Do I have to point to the
>> schema.ini file in some way (it's in the same folder as the text file I'm
>> importing)? Do I have to specify each column and it's type? Any help
>> would be appreciated.
>>
>> Thanks
>>
>> Jim
>> "yoram ayalon" <yoram.ayalon@structuredweb.com> wrote in message
>> news:1190045159.038779.6590@o80g2000hse.googlegroups.com...
>>>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!
>>>
>>
>>
>
>

On Tue, 18 Sep 2007 14:58:26 -0700, "James D. Houston" <jd_houston@earthlink.net> wrote:

¤ Lance,
¤
¤ Should the line for zip read Col12=zip Text? Is it case sensitive? And can
¤ I skip over blank columns or columns I don't want to import?
¤

You can find more info on the schema.ini file definition at the following site:

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


Paul
~~~~
Microsoft MVP (Visual Basic)