SQL queries over DSV files?

Question

Pretty straight forward thing.

Having a DSV file, say, data.txt

name;surname;age;money
Joe;Dun;13;4
Don;Dapper;44;100
Freddie;Mercury;45;888

I wish there was a program I could make Sqlquereis over that. like,

dsvsql -d\; -q "select sum(money) from data.txt where surname like 'D%'" data.txt

Which would I wish it outputs:

sum(money)
104

Now, I found this old question which doesn't have a satisfactory answer... and also there, they mention CsvJdbc which seems close, but its not quite it. What I want was something totally terminal based and that I wouldn't need to code in order to run.

I'm constantly dealing with lots of DSV files with different fields and analysing it. This would come super handy...

any thoughts?

cheers!

Is building your own terminal application using an existing CSV database driver like CSVJDBC or DBD::CSV out of the question? — Judge Maygarden, Jul 27 '11 at 14:38
Yeah.. I'm afraid so. I do see this option as viable though, csvjdbc seems pretty straight forward using to that end. Thanks. — filippo, Jul 27 '11 at 14:40

score 1 · Accepted Answer · 2011-07-28T20:54:56.997

CsvJdbc does not seem to support anything but the most basic SQL queries (not even sum() seems to be supported)

You can use HSQLDB (2.2) instead. It can read CSV files directly and offers your the full range of SQL features it has. The only drawback is that you need to create the tables manually. Just dropping files into a directory is not enough (as with CsvJdbc)

For details: http://hsqldb.org/doc/2.0/guide/texttables-chapt.html

For querying the tables you can use JDBC based query tools that can run on the commandline.

I know two of them:

henplus - http://henplus.sourceforge.net/
SQL Workbench/J - http://www.sql-workbench.net (batch, commandline or GUI mode)

``HSQL`` + SqlWorkbench did just the trick. Still, could be easier (I really think it should be just the command I said). Guess I'll be implementing that command when I get a minute. thanks anyway. — filippo, Jul 29 '11 at 16:09

score 0 · Answer 2 · answered Jul 28 '11 at 11:08

Microsoft has a freely downloadable program called Log Parser. It gives you a SQL-like interface to CSV files, among others. Windows only, of course. But maybe you should Google for "log parsing utilities", or something like that.

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

SQL queries over DSV files?

2 Answers2