SQL-ing over text files with ODBC

[npocmaka_]

I thought it will be great if I could execute SQL queries over CSVs,TSVs.. from batch file and it looks almost possible with MS text driver through ODBC data source.It even does not require installations of additional tools.Here's my script (the simpliest possible case ) (Ive same question in SO and MSDN):

Code: Select all

@if (@X)==(@Y) @end /* JScript comment
@echo off
setlocal

::Delete the line bellow if you are running 32bit Windows.
path %windir%\sysWOW64\


rd /s /q C:\dbtest
md C:\dbtest
rem create a new "database"
(
echo "col1","col2"
echo "1","2"
) >>C:\dbtest\test.txt
odbcconf configdsn "Microsoft Text Driver (*.txt; *.csv)" "DSN=testdns1|description=test_dns_1|defaultdir=C:\dbtest"
 cscript //E:JScript //nologo "%~f0"
endlocal
exit /b 0

*/


var rs = new ActiveXObject("ADODB.Recordset");
var comm=new ActiveXObject("ADODB.Command");
var conn = new ActiveXObject("ADODB.Connection");
conn.ConnectionString="testdns1";
// read write
conn.Mode=3

conn.Open;
conn.Execute("Select  * FROM test");
conn.Close();

This code gives me the following error:

Code: Select all

 Microsoft OLE
DB Provider for ODBC Drivers: [Microsoft][ODBC Text Driver] The Microsoft Jet da
tabase engine cannot open the file '(unknown)'.  It is already opened exclusivel
y by another user, or you need permission to view its data.

I'm not very in the ODBC but will resume what I've found

Im able to connect only to a system datasource (stored in "HKEY_CURRENT_USER\Software\ODBC" - it is completely possible to create a datasource with REG command) My attempts with file datasource have failed.

It is possible to create an datasource also with odbcad32 , but odbcconf gives more control (and auto-detecting of delimiters does not work with odbcad32 ).

Here are the options that can be used with the text driver:

http://msdn.microsoft.com/en-us/library ... 85%29.aspx

The text driver is located in ODBCJT32.DLL - for 64 bit systems it is located

For 64bit systems it is located in %windwir%\sysWOW64\ which forces you to use also the odbcconf.exe located there.


For the text driver the database name is represented by an address to the directory where text files are taken for a tables.Whatever I've tried it only accepts files with .TXT extension , otherwise it wont find the files.

Hope a side look will found what I'm missing here

[einstein1969]

Hello,

I have read that is necessary a SCHEMA.INI

you can try to create one with this content:

Code: Select all

[EMP.TXT]
ColNameHeader = True
CharacterSet = ANSI
Format = TabDelimited
Col1=EmployeeID Integer
Col2=LastName Char Width 20
Col3=FirstName Char Width 10
Col4=Title Char Width 30
Col5=TitleOfCourtesy Char Width 25
Col6=BirthDate Date
Col7=HireDate Date
Col8=Address Char Width 60
Col9=City Char Width 15
Col10=Region Char Width 15
Col11=PostalCode Char Width 10
Col12=Country Char Width 15
Col13=HomePhone Char Width 24
Col14=Extension Char Width 4
Col15=Photo OLE
Col16=Notes LongChar
Col17=ReportsTo Integer 

rif:How to programmatically create a Schema.ini file in Access 2000

then you can create the EMP.TXT table using the name of columns how defined in schema.ini and use the code like this for interrogate:

Code: Select all

select * from EMP

I have not tried!

PS: try "Select * from EMP.TXT" too.

einstein1969

[npocmaka_]

@einstein1969 - thanks for the help. Here I've received the answer - http://stackoverflow.com/a/24841160/388389

The name of yout table is test.txt. So change

conn.Execute("Select * FROM test");
to

conn.Execute("Select * FROM [test.txt]");

The result also should wrapped in enumeration before the elements of the query could be accessed..
May be later will share more code.

[einstein1969]

thanks for replay... I see...

einstein1969

[npocmaka_]

here's an example that prints an CSV file:

Code: Select all

@if (@X)==(@Y) @end /* JScript comment
@echo off
setlocal
if defined ProgramW6432 path %windir%\sysWOW64\
rd /s /q C:\dbtest >nul 2>nul
md C:\dbtest

rem create a new "database"
(
echo "col1","col2"
echo "1x1","1x2"
echo "2x1","2x2"
echo "3x1","3x2"
echo "","4x2"
echo 5x1,
echo ,
echo "",""
echo 8x1,8x2
) >>C:\dbtest\test.txt

odbcconf configdsn "Microsoft Text Driver (*.txt; *.csv)" "DSN=testdns1|description=test_dns_1|defaultdir=C:\dbtest"
 cscript //E:JScript //nologo "%~f0"
endlocal
exit /b 0

*/


var rs = new ActiveXObject("ADODB.Recordset");
var comm=new ActiveXObject("ADODB.Command");
var conn = new ActiveXObject("ADODB.Connection");
conn.ConnectionString="testdns1";
// read write
conn.Mode=3

conn.Open;

rs=conn.Execute("Select  * FROM [test.txt]");
//var e = new Enumerator(rs);
while (!rs.EOF) {
  WScript.Echo(rs("col1") + "--" +rs("col2"));
  rs.MoveNext; 
}

conn.Close();

output:

1x1--1x2
2x1--2x2
3x1--3x2
null--4x2
5x1--null
null--null
null--null
8x1--8x2

Found also this : viewtopic.php?f=3&t=1957&start=15

Now start to think for some experiments to check how powerful SQL queries can be in this case - sorting ,distinct ,sort by date,what kind of delimiters can be set and what structure the file could have and etc.