Connection string for Piped Delimiter CSV

[Guest]

hi,
I need to connect a csv file as data source. the connection string currently
using is

cnxStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + CSVFolder +
";Extended Properties=""Text;HDR=No;FMT=Delimited;IMEX=1\"""

it's working, however it's only able to read a row as one column. I have
multiple columns in a row, delimited by pipe (|). What's the variables i need
to add/change to the connection string in order for it to be able to come out
with a multiple columns datatable.

Thanks

 

[Al Reid]

hi,
I need to connect a csv file as data source. the connection string currently
using is

cnxStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + CSVFolder +
";Extended Properties=""Text;HDR=No;FMT=Delimited;IMEX=1\"""

it's working, however it's only able to read a row as one column. I have
multiple columns in a row, delimited by pipe (|). What's the variables i need
to add/change to the connection string in order for it to be able to come out
with a multiple columns datatable.

Thanks

You may need to include a schema.ini file to specify the custom delimiter. Have a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
and specifically "Custom Delimited." This may work in the connection string, but I have never tried it.

 

[shriop]

The Microsoft Text Driver is the slowest CSV parser going. You should
atleast switch to using the Jet driver.

http://www.geocities.com/shriop/csv_benchmarks.html

 

[Paul Clement]

¤ > hi,
¤ > I need to connect a csv file as data source. the connection string currently
¤ > using is
¤ >
¤ > cnxStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + CSVFolder +
¤ > ";Extended Properties=""Text;HDR=No;FMT=Delimited;IMEX=1\"""
¤ >
¤ > it's working, however it's only able to read a row as one column. I have
¤ > multiple columns in a row, delimited by pipe (|). What's the variables i need
¤ > to add/change to the connection string in order for it to be able to come out
¤ > with a multiple columns datatable.
¤ >
¤ > Thanks
¤ >
¤
¤ You may need to include a schema.ini file to specify the custom delimiter. Have a look at
¤ http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
¤ and specifically "Custom Delimited." This may work in the connection string, but I have never tried it.

The connection string doesn't support custom delimiters so a schema.ini file must be used, as you
mentioned.

[Test.csv]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

does Jet driver support Pipe delimiter? Can it use scheme.ini as well?

The Microsoft Text Driver is the slowest CSV parser going. You should
atleast switch to using the Jet driver.

http://www.geocities.com/shriop/csv_benchmarks.html

 

[Al Reid]

does Jet driver support Pipe delimiter? Can it use scheme.ini as well?

Yes.