Connection string for Piped Delimiter CSV

[Yan]

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]

"Yan" <Ya*@discussions.microsoft.com> wrote in message news:B2**********************************@microsof t.com...

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/de...a_ini_file.asp
and specifically "Custom Delimited." This may work in the connection string, but I have never tried it.

--

Al Reid

[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

Al Reid wrote:

"Yan" <Ya*@discussions.microsoft.com> wrote in message news:B2**********************************@microsof t.com...

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/de...a_ini_file.asp
and specifically "Custom Delimited." This may work in the connection string, but I have never tried it.

--

Al Reid

[Paul Clement]

On Thu, 23 Jun 2005 07:27:05 -0400, "Al Reid" <ar*****@reidDASHhome.com> wrote:

¤ "Yan" <Ya*@discussions.microsoft.com> wrote in message news:B2**********************************@microsof t.com...
¤ > 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/de...a_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)

[Yan]

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

"shriop" wrote:

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 wrote:

"Yan" <Ya*@discussions.microsoft.com> wrote in message news:B2**********************************@microsof t.com...

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/de...a_ini_file.asp
and specifically "Custom Delimited." This may work in the connection string, but I have never tried it.

--

Al Reid

[Al Reid]

"Yan" <Ya*@discussions.microsoft.com> wrote in message news:59**********************************@microsof t.com...

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

Yes.