Connection string for Piped Delimiter CSV

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Yan said:
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
Click to expand...

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.
 
¤ > 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)
 
Back
Top