:: loop through arguments until either a keyword or a filename is reachedfor %%I in (%*) do ( if "!_D!"=="found" set "_D=%%~I" if /i "%%~I"=="/d" set "_D=found" if /i "%%~I"=="/e" set "_E=1" if /i "%%~I"=="/s" set "_S=1" if /i "%%~I"=="/debug" set "_Debug=1" if not "!keywords!"=="!keywords:%%~I=!" ( goto break ) if exist "%%~I" ( set "sqlfile=%%~I" goto break )):break

if not defined sqlfile (

if "%_S%"=="1" ( set "sqlfile=%CD%" ) else (

set "args=%*" for %%I in (SELECT INSERT UPDATE DELETE DESCRIBE TRUNCATE /D) do ( if not "!args!"=="!args:%%I=!" set found=1 )

if "%_E%"=="1" goto usage

if not defined found goto usage set "tmpfile=%temp%\raw%time::=%.tmp" ))

Notes: * Values are treated as strings. Math operations (col+=int) won't work. * SELECT count(*) works, though. So does UPDATE file SET col1=col2. * If the csv file does not exist, INSERT will attempt to create it. * The csv file MUST include a header row. * The SQL keywords are not case-sensitive. Be lazy if you wish. * To avoid having to escape parentheses, you can enclose your statement in quotation marks.

/* The MS Jet OLEDB text driver does not natively support UPDATE or DELETE statements. Work around this by copying unaffected rows to a new file, appending modified rows, and replacing the original csv. */ case 'update': case 'delete': case 'truncate': // *shrug* Why not?

CSV.BAT allows interaction with CSV (or TSV or similar) files with SQL syntax. It's not as fast as direct search-and-replace manipulation of the text, but hopefully it will provide enough flexibility to be useful as a utility for other projects and a solution to CSV challenges. Rather than counting lines and tokens, you can use this script to treat CSV files as rudimentary databases, querying and manipulating data, and merging multiple CSV files with SQL JOIN syntax. You can manage the column data types with Schema.ini if you wish, and automatically generate a default Schema.ini for all text files in a directory with the /S switch. It will probably run on any Windows machine 2000 and newer, although a Windows Script 5.7 update might be needed on old Windows versions.

If CSV.BAT is executed from within another batch script, make sure you use CALL to allow your script to continue after CSV.BAT has exited.

CSV.BAT exits with an %ERRORLEVEL% of 0 on success, or 1 on fail. With the /E switch, it exits with status 0 on success, or the number of caught errors on fail. Use the following to see example usage:

So, why did I make this when Dave Benham has already shared his excellent CSV parser? It was a challenge that's been an occasional itch on my brain for a long time. I don't have a particular purpose in mind. I just finally got around to sitting down and writing code a couple days ago, and thought I'd share my efforts.

I'm still squashing bugs as I find them. For example, in describing this scenario I discovered a problem with the CSV INSERT auto detection of the headers where column names contain spaces. That's why I did ">applications.csv echo col1,col2,etc." before calling csv.bat. I'll squash that bug tomorrow. Edit: Squashed in 1.0.1, but a more reliable syntax would be "SELECT args INTO applications.csv FROM applicants.csv", etc. See note below.*

csv update properties.csv set available=1 where address like "%harbison%"

... or at least that'll work after I find and squash another bug... tomorrow. Edit: fixed in 1.0.2.

By the way, wonder how many posts I need before I don't need moderator approval for new posts and edits?

-----------------------------------------------

* Note on "SELECT INTO newfile" versus "INSERT INTO newfile SELECT": The former syntax allows the MS Jet driver to copy the header row exactly as it should be, rather than my regexp and for loop guesswork on the latter syntax. For example:

At first I thought you were crazy trying to bring SQL to the batch environment. But now that I see what you can do with it, I see a lot of potential.

I hope to God no one attempts to use this to maintain a database in CSV files. But it could be really useful for extracting data from, or transforming CSV files.

Have you thought about providing connectivity to a true relational database? That might be useful if you have a CSV file and you want to augment it with content from a proper relational database via lookup (join)

I've thought about it, but more in the context of reading csv and importing to MS Access or sqlite.

My more immediate goal is to fix the bugs I discovered this afternoon though. They're gnawing at me. Life afk is interfering at the moment though, so it'll have to wait. I hate leaving things broken....

Pretty sure this is going to come in handy. Can I still do queries if I do not have a header row that defines my delimited fields. I work with a lot of files like this. I know what the fields are already. I do most of my work on our mainframe with sas but this may come in handy for quick reporting on files. We have the files backed up on the network and mainframe storage. It is a pita on the mainframe to work with lots of files whereas in batch I can just point it at a directory.

dbenham wrote:I hope to God no one attempts to use this to maintain a database in CSV files.

Years ago I used flat files for pseudo databases with the pascal programs I wrote. Three programs I can remember were my VHS Video Collection, Golf Scores and Car parts inventory for a friend's dad who owned a salvage yard. That was over 20 years ago.

The first forum software I ran on my linux server used csv files. Ikonboard.

Squashman wrote:Pretty sure this is going to come in handy. Can I still do queries if I do not have a header row that defines my delimited fields. I work with a lot of files like this. I know what the fields are already. I do most of my work on our mainframe with sas but this may come in handy for quick reporting on files. We have the files backed up on the network and mainframe storage. It is a pita on the mainframe to work with lots of files whereas in batch I can just point it at a directory.

Not sure. I've not played with csv files with this script without a header row. I'm pretty sure at the very least you'll need to modify the oConn= line and set "HDR=No", and add a Schema.ini file to your CSV directory to define the columns. You might be able to reference the columns by index number without Schema.ini, but I'm not sure how much the script would need to be modified to allow that.

Edit: Since I added syntax for "describe table csvfile" I think this is doable. Just modify the oConn= line and set "HDR=No", then "csv describe table csvfile" to see the driver-detected column names and types of your CSV file. I'll probably add an /N option for no header in a future update.

Some time ago I developed FilePointer.exe auxiliary program that allows to move the file pointer of a redirected file inside a code block, and as example of such program I wrote a very simple Data Base Relational application (or something like that, the link is in that post) that uses files with .NDX extension as index files to process another file in certain order, or to directly take a record via a key. I wonder if such method could be used as DBR engine and then program the SQL query part in pure Batch. This way the application don't require JScript nor ADODB, but require FilePointer.exe instead.

setting var=number sometimes used to set var=column[number].value. Now it doesn't.

joined table.column arguments for better header generation

Added:

/debug switch to show intermediate sql operations. It's mostly for my own benefit, so I'll probably leave it undocumented.

Still struggling with:

In my [update... set where col like val] test case, [insert...select where not like] is somehow not the inverse of [select where like] as it should be. /debug switch shows the SQL commands being composed correctly, but this duck isn't quacking the way it's supposed to. On the other hand, if I directly [select where not like] and [select where like] I get the correct results. I'll have to sleep on it.... Edit: Fixed in 1.0.2.

Aacini wrote:Some time ago I developed FilePointer.exe auxiliary program that allows to move the file pointer of a redirected file inside a code block... blah blah math math math... This way the application don't require JScript nor ADODB, but require FilePointer.exe instead.

It seems the MS Jet driver has difficulty executing more than one WHERE clause per process thread (possibly only WHERE...LIKE?). Workaround by forking a query into a separate process. As an unintended benefit, this actually sped up execution a little. Cool.

"SELECT * INTO bar FROM foo" syntax was not recognized. It is now.

Exclusive lock error now sleeps and retries rather than failing outright.