CSV.BAT - CSV file manipulation with SQL syntax

[rojo]

SQL statements can become quite long - perhaps too long to fit on the command line.

A nice feature would be an option to read the statement from a file. Even better would be the abililty to include multiple statements in a single script file.


Dave Benham

Aww, man, let me have a few moments of satisfaction for fixing yesterday's problems. I thought I was done for now. :)

Your suggestions are excellent, of course. They'll be next on my list.

[rojo]

Version 1.1

Added:

• Now accepts either a filename or a SQL string as an argument. If filename, the script reads its SQL commands from the file, one command per line.
• /E switch added. Since the script can handle a file containing multiple SQL commands, having the option either to continue or halt on caught error seemed like a good idea.
• Since the /E switch allows the script to keep playing through the pain, exit code is now the number of errors caught. 0 on success, greater than 0 with errors.

Dave's suggested additions weren't as trivial as I thought they'd be when I started. The MS Jet OLEDB driver is really a pain when it's asked to perform multiple operations within the same thread. It doesn't error, but it doesn't provide the correct results, either. (I'd almost rather it throw errors.) Anyway, to work around the issue, each iteration through the SQL command file is spawned as a new process. Problem solved.

[rojo]

Version 1.1.1

Fixed:

• logic mistake with SQL command file iteration (skipped line 2)

Added:

• DESCRIBE TABLE filename.csv
• TRUNCATE TABLE filename.csv

The "DESCRIBE TABLE filename" shows how the Jet OLEDB engine interprets your CSV file's data types. It has the driver SELECT * INTO tempfile FROM filename WHERE 0, thereby copying only the column names; but it also generates a Schema.ini by doing this. Schema.ini describes the constraints on each column, as well as the charset used and so forth. I will probably make further use of this later. I like the idea of letting the script generate a Schema.ini automatically.

[rojo]

Version 1.1.2

Added:

• /S switch to auto generate Schema.ini for all text files in the specified directory

If your CSV files are being interpreted incorrectly by the script, generating a Schema.ini and tweaking the column data types might help. See the Schema.ini documentation for supported column data types.

[npocmaka_]

Great tool


Do you have any idea why pointing directly to HKEY_LOCAL_MACHINE\\SOFTWARE\\Wow6432Node\\Microsoft\\Jet\\4.0\\Engines\\Text\\ does not work but it requires 32bit version of cscript?

Jet engine also has ability to process excel/access files without installing office or office viewers I think. May be I'll try to create something similar for excel .

[rojo]

Great tool :-)


Do you have any idea why pointing directly to HKEY_LOCAL_MACHINE\\SOFTWARE\\Wow6432Node\\Microsoft\\Jet\\4.0\\Engines\\Text\\ does not work but it requires 32bit version of cscript?

Thanks! I haven't had any practical use for it yet, but maybe it'll save somebody's life some day.

The Microsoft OLE DB Provider for Jet and the Jet ODBC driver are available in 32-bit versions only. A 64-bit driver is available for download though. I'm sure that doesn't really answer your question, other than to say a 32-bit driver must be loaded within a 32-bit environment.

[Squashman]

Still want to get around to trying this out but it is still way over my head on how it all works. Can you post an example of a schema.ini and how I would use that schema.ini to run the bat file?
Thanks

[rojo]

Still want to get around to trying this out but it is still way over my head on how it all works. Can you post an example of a schema.ini and how I would use that schema.ini to run the bat file?
Thanks

OK. Basic example. Say you've got a directory containing a file called "test.tsv" with the following tab-delimited data:

Code: Select all

year    make    model      color   worth
2015    Toyota  Tacoma     black   $32,000.00
2008    Dodge   Ram        silver  $18,500.00
1999    Chevy   Silverado  red     6-pack of beer

If you were to simply:

Code: Select all

csv.bat /d tab select * from test.tsv

... the 1999 Chevy Silverado "worth" value would be output as "undefined", because the column is assumed to be currency; and "6-pack of beer" isn't decipherable under that constraint. Now, say you'd prefer the data type of the worth column to be a string, rather than currency. In this case, you'd need to generate a Schema.ini. To do this, you can let the script auto generate it for you with the /s switch.

Code: Select all

csv.bat /s

When it's done, a Schema.ini will have been generated for every text file in the directory. Open it and find the section for test.tsv. Change the data type for column 5 to "Text" (or whatever other data type you feel is appropriate), then save the change. Your modified Schema.ini will look like this:

Code: Select all

[test.tsv]
ColNameHeader=True
CharacterSet=1252
Format=TabDelimited
Col1=year Integer
Col2=make Char Width 255
Col3=model Char Width 255
Col4=color Char Width 255
Col5=worth Text

And the next time you run the query:

Code: Select all

csv.bat select * from test.tsv

... the 99 Silverado's "6-pack of beer" value will be preserved. Also note that generating Schema.ini allows you to omit the /d tab arguments from the command line. Since the delimiter is specified within Schema.ini, it no longer needs to be specified as an argument to the batch script.

Anyway, hope this helps!

[Squashman]

So if my files have no header and are pipe delimited what do I change the Format be?

Code: Select all

[test.tsv]
ColNameHeader=False
CharacterSet=1252
Format=??????
Col1=year Integer
Col2=make Char Width 255
Col3=model Char Width 255
Col4=color Char Width 255
Col5=worth Text

[rojo]

So if my files have no header and are pipe delimited what do I change the Format be?

Code: Select all

[test.tsv]
ColNameHeader=False
CharacterSet=1252
Format=??????
Col1=year Integer
Col2=make Char Width 255
Col3=model Char Width 255
Col4=color Char Width 255
Col5=worth Text

Try this:

Code: Select all

Format=Delimited(|)

I would've thought that

Code: Select all

csv.bat /s

... would auto detect the pipe delimiter though. Did it not?

[Squashman]

... would auto detect the pipe delimiter though. Did it not?

But I have no header record that defines my fields. So I just wanted to manually create the schema.ini for all my files. Actually I already have a bunch of SAS programs with the layouts of my files defined in them. I am going to edit those SAS programs to output a schema.ini file as well. That way I can use your utility to do certain things real quick instead of writing a whole SAS program to do it.

[rojo]

My time has come. This is the moment I've been waiting for. My script has become useful to someone. All my toil and strife has not been in vain.

[foxidrive]

My time has come. This is the moment I've been waiting for. My script has become useful to someone. All my toil and strife has not been in vain.

[npocmaka_]

A note about this script.

If the CSV has more than one dot character in its file the script will fail with "The Microsoft Jet database engine could not find the object .." -> https://stackoverflow.com/questions/101 ... ect-sheet1

may be a check can be put in the script.

[Ed Dyreen]

why don't you use SQL scripts to create, manage, convert update or delete databases ?

Can your SQL program not do that ?