Microsoft Text Driver dropping some data during reading

[yoram ayalon]

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 Wynn]

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....@structuredweb.com> wrote in message
news:1190045159....@o80g2000hse.googlegroups.com...

[yoram ayalon]

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

> > the value for RS("Field1") for the last row will by NULL!- Hide quoted text -
>
> - Show quoted text -

[James D. Houston]

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....@structuredweb.com> wrote in message
news:1190045159....@o80g2000hse.googlegroups.com...

[Lance Wynn]

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

"James D. Houston" <jd_ho...@earthlink.net> wrote in message
news:e8yWddh%23HHA...@TK2MSFTNGP06.phx.gbl...

[James D. Houston]

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" <Lanc...@community.nospam> wrote in message
news:uSZFV4h%23HHA...@TK2MSFTNGP06.phx.gbl...

[Lance Wynn]

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_ho...@earthlink.net> wrote in message

news:O151Maj%23HHA...@TK2MSFTNGP02.phx.gbl...

[Paul Clement]

On Tue, 18 Sep 2007 14:58:26 -0700, "James D. Houston" <jd_ho...@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)