topic Re: Open Database() with csv/txt example? in Discussions

Open Database() with csv/txt example?

djhanson — Mon, 08 Oct 2012 19:42:14 GMT

Does anyone have an Open Database() example for ODBC'ing to a .csv or .txt file? I can get ODBC to work fine with Excel 2007 .xlsx but haven't deduced the text file connection string correctly for opening a .csv file. I'm even referencing the correct "User DSN" name string via Windows ODBC Data Source Administrator. So I'm wondering if I have the wrong "DriverId" shown below? thx... dj

This works:

dt_excel=open database("DSN=Excel Files xlsx;DBQ=C:\Data.xlsx;DriverId=790;MaxBufferSize=2048;PageTimeout=5;","SELECT *","Result");

This doesn't work:

dt_csv=open database("DSN=Text File;DBQ=C:\Data.csv;DriverId=27;MaxBufferSize=2048;PageTimeout=5;","SELECT *","Result");

Re: Open Database() with csv/txt example?

Anonymous — Mon, 08 Oct 2012 23:26:30 GMT

I don't think I have ever referenced "DriverId" in my DSN strings. Will it work if you leave it out altogether?

Re: Open Database() with csv/txt example?

djhanson — Tue, 09 Oct 2012 17:45:20 GMT

Thanks Chad. I tried removing the "DriverId" string and other subsequent strings and get the same error message. It's as if it cannot find my Text File driver (eventhough it appears fine in the Windows ODBC manager). Else, I've got some sort of typo/mismatch in my connection string? Odd, as this whole thing works great in Excel (but my data files are csv's so I'd rather not have to convert them to .xlsx files to use ODBC).

This doesn't work:

dt_csv=open database("DSN=Text File;DBQ=C:\Data.csv;","SELECT *","Result");

Error message that results:

[Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Re: Open Database() with csv/txt example?

ms — Wed, 10 Oct 2012 10:29:17 GMT

Have you tried this variant? It worked with a sample file, however I had problems with delimiters. But I at least got a table with (messed up) data from the text file.

dt_csv = Open Database( "DSN=Text File;DBQ=C:\;", "SELECT * FROM Data.csv", "Result" );

Re: Open Database() with csv/txt example?

djhanson — Wed, 10 Oct 2012 13:58:59 GMT

Hi MS, yep, late last night I finally figured it out! Yes, it is exactly what you posted above syntax-wise. Thanks for replying to this thread. For the record, here's what I learned about doing an ODBC to a text file. It really has nothing to do with JSL, but rather the industry convention/syntax required to do ODBC to a text file itself:

a. Create schema.ini - must create this file to support the text file format. Place it in the same directory as the text file. Easy to create via Notepad. Google schema.ini for ODBC text file for details.

b. Use correct ODBC syntax - unlike say Excel ODBC DBQ string, text file ODBC DBQ string does not reference the path+filename but rather just the path. This is what I got hung up on. So this is what works (just as you posted above - the key is to reference the DBQ=<path> and not DBQ=<path+filename> and then split up the filename into the SQL FROM statement):

dt_csv=open database("DSN=Text File;DBQ=C:\;","SELECT * FROM Data.csv","Result");

c. Minor detail - just a note. If you're using Win7, there are two different locations for the ODBC Manager. One is the 32 bit location and the other is the 64 bit location. Microsoft named them like Greenland and Iceland (sort of misleading). Just be aware of this when/if you're adding/modifying say the ODBC Text File driver. I hear in Win8 that they may make these separate names or identifiers somehow.

Win7 ODBC Managers:

32 bit: C:\Windows\SysWOW64\odbcad32.exe

64 bit: C:\Windows\System32\odbcad32.exe

Anyways, the above a.) + b.) does allow me to use JSL to ODBC to a raw text file. Very nice... ...dj