HomeActive Query BuilderCSV Connection

CSV Connection


Hi,

Is it possible to connect to a csv file through the query builder
(without converting the file to .xls)

10x
ronk | email
June 26 2008, 06:59 AM
Sure, it is possible. Just use the MS Jet 4 OLEDB Provider.
I have successfully connected and built queries against a folder containing many CSV files (each field value quoted by double quote and each field separated by semicolon) using this connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\tmp\csv;Extended Properties="text;Format=Delimited(;)";Persist Security Info=False

where "c:\tmp\csv" is a folder with CSV files.

When tuning this connection string, you can get help at
http://connectionstrings.com/?carrier=textfile
http://msdn.microsoft.com/en-us/library/ms709353.aspx
Sergey Smagin | email
21 hours, 3 mins since original post
Hi,
when trying to use this connection string, the udl wizard is ok, but I can not see anything on your control, I used the AQBDemo.htm to connect to csv file and the tree on the right side(the one that shows the database tables)
does not show anything

10x
ronk | email
3 days, 2 hours since original post
Dear Ron,

the Jet engine driver treats as CSV files ony the files with the "csv" extension, all other files will be ignored (I am not sure, but maybe this can be tuned in the schema.ini file - look at the links I gave you).

Please note: using this connection string, you can not "connect to csv file", but can connect to a folder containing CSV files. The one file represents one table, and each table appears as a node in the metadata tree.

Also, it is better to set the dialect to "MS Access 2003 (MS Jet Engine ver 4)". This connection string also works with the default "Universal" syntax. You can do it by click on the "Properties" button in the AQBDemo.htm demo.
Vitaly Sotnikov | email
3 days, 22 hours since original post
Hi,

I am sending to your email a rar file that contians my csv files folder with 1 csv file and schema.ini file
please try to connect to it using the AQBDemo.htm demo
I could not do it, the metadata tree did not show anything

Ron
ronk | email
3 days, 23 hours since original post
Dear Ron,

the MS Jet Text File driver uses the "List Separator" symbol from the Windows regional settings as the values separator from CSV file.
I suppose the ";" is the "List Separator" and "," is the "Decimal Separator" in the System Locale you use.
But there exists a way to redefine these symbols in the Schema.ini file. Use the following connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\tmp\text_datasources;Extended Properties="text;Format=Delimited";Persist Security Info=False

and the following Shema.ini file for the sample you sent to me:

[Infovista.csv]
CharacterSet=ANSI
CurrencyThousandSymbol=,
DecimalSymbol=.
Vitaly Sotnikov | email
4 days, 1 hour since original post
This topic is closed