Today I learned: Why there aren't many tools for SQLing a csv

I wear a lot of hats, and I up using CSVs pretty frequently because it's such a common layperson data exchange format. I know a bit of SQL, and I've caught myself wondering a thousand times why there aren't many tools for running SQL against a spreadsheet.

I was doing a little sysadmin today and caught myself wondering the same thing again. This time, though... I took a minute to wrap my head around it.

And now I know: Sqlite

What I found was how dead. stinking. simple it is to translate a csv directly to a sqlite database. From there, I could go to town on it.

1. Find a csv and tidy-up if needed. (caveat: all your columns need a header - makes sense for a database, though, right?)

2. Start sqlite: sqlite3 some_db_file.db

3. Import the csv into a clean database
   

   .mode csv
   .import my_csv_file.csv some_db_name

Done! Sqlite automagically shuffled your spreadsheet into a new database. Run SQL against it, build a CLI app that consumes it, whatever - have fun!

Comments

[reidterror]

How good is sqlite for a small startup project is what i find myself wondering lately. Been doing a bit of django in my spare time and the default for that is sqlite.

[Kevin Ard]

idk if I'd recommend sqlite as the prime backend store for a production thing. It's default for a number of frameworks etc because it's great for RAD (embedded; no running server), and its sql-compliance means switching to the production backend is usually just a question of updating the driver in your ORM layer (assuming you're using one).

Translation: it's default for dev, not generally recommended for prod.