Needed to do some very simple analysis, just beyond than what sed or awk would provide. Was about to break out python, but then I remembered the latest thing I’d installed, SQLite.
Installing SQLite is nice and easy. Download the dll and the executable (I’m using sqlitedll-3_3_4.zip and sqlite-3_3_4.zip). Create a directory (say sqlite3) and unzip the files in there. That’s it, now you have an sql database engine up and running.
To create your database instance:
sqlite3 test.sql3
where test.sql3 is the name of the database file you want to use or create. Note that you don’t have to do anything fancy to create the database, you can just start sqlite and it’s created for you. No user creation, permission setting, etc. It just works.
I had my input data as a text file. SQLite’s default field separator is “|”.
To import the data:
create table my_table (x string, y string);
.import test.txt my_table;
That’s it, now you have your data in your table and can query the data to your heart’s content via SQL. The rest was specific to what I needed to do, but you get the idea.
SQLite is quite nice for simple stuff like this where SQL is a convenient way of getting at the data. It’s nice to have a database that’s almost as easy to fire up as sed or awk. It implements most of SQL92 and has full APIs for a number of languages including Python, Perl, PHP, Tcl, and Ruby.
Update: Here’s how to import CSV into SQLite:
sqlite3.exe -separator , test.db
create table accounts ( accountid, nlistings );
.import ‘C:\path\to\the\csv\accounts.csv’ accounts