CSV.BAT - CSV file manipulation with SQL syntax

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Message
Author
rojo
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#16 Post by rojo » 16 Jan 2015 10:37

dbenham wrote: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
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#17 Post by rojo » 16 Jan 2015 21:30

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
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#18 Post by rojo » 17 Jan 2015 07:41

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
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#19 Post by rojo » 18 Jan 2015 09:18

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_
Posts: 479
Joined: 24 Jun 2013 17:10
Location: Bulgaria
Contact:

Re: CSV.BAT - CSV file manipulation with SQL syntax

#20 Post by npocmaka_ » 18 Jan 2015 09:41

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
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#21 Post by rojo » 18 Jan 2015 12:33

npocmaka_ wrote: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
Expert
Posts: 4013
Joined: 23 Dec 2011 13:59

Re: CSV.BAT - CSV file manipulation with SQL syntax

#22 Post by Squashman » 21 Oct 2015 11:19

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
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#23 Post by rojo » 23 Oct 2015 13:37

Squashman wrote: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
Expert
Posts: 4013
Joined: 23 Dec 2011 13:59

Re: CSV.BAT - CSV file manipulation with SQL syntax

#24 Post by Squashman » 23 Oct 2015 13:51

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
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#25 Post by rojo » 23 Oct 2015 14:05

Squashman wrote: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
Expert
Posts: 4013
Joined: 23 Dec 2011 13:59

Re: CSV.BAT - CSV file manipulation with SQL syntax

#26 Post by Squashman » 23 Oct 2015 14:22

rojo wrote:... 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
Posts: 26
Joined: 14 Jan 2015 13:51

Re: CSV.BAT - CSV file manipulation with SQL syntax

#27 Post by rojo » 23 Oct 2015 16:38

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
Expert
Posts: 6033
Joined: 10 Feb 2012 02:20

Re: CSV.BAT - CSV file manipulation with SQL syntax

#28 Post by foxidrive » 25 Oct 2015 00:48

rojo wrote: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_
Posts: 479
Joined: 24 Jun 2013 17:10
Location: Bulgaria
Contact:

Re: CSV.BAT - CSV file manipulation with SQL syntax

#29 Post by npocmaka_ » 23 Nov 2018 04:03

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.
Last edited by npocmaka_ on 28 Nov 2018 13:30, edited 1 time in total.

Ed Dyreen
Expert
Posts: 1527
Joined: 16 May 2011 08:21
Location: Flanders(Belgium)
Contact:

Re: CSV.BAT - CSV file manipulation with SQL syntax

#30 Post by Ed Dyreen » 24 Nov 2018 12:22

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

Can your SQL program not do that ?

Post Reply