1. 27
  1.  

  2. 3

    Note: this appears to be completely separate from Kx Systems’ q.

    1. 2

      I really liked the Philosophy section in the Rationale page: that’s a good drive for tools’ development.

      This tool has been designed with general Linux/Unix design principles in mind. If you’re interested in these general design principles, read the amazing book http://catb.org/~esr/writings/taoup/ and specifically http://catb.org/~esr/writings/taoup/html/ch01s06.html. If you believe that the way this tool works goes strongly against any of the principles, I would love to hear your view about it.

      1. 2

        This is very cool. Just a couple of hours ago I was looking to execute what (at least in my brain) is a SQL query against a csv file. Installed this just now and I’m impressed so far! Thanks for sharing.

        1. 16

          It’s a useful little tool. Back when I worked with CSVs daily I used q, but eventually just started using SQLite directly. The .import command works fabulously. It doesn’t do full CSV parsing, just dumb split-on-delimiter, but that worked for me.

          For example, given this CSV file:

          id,name
          1,peter
          

          You can do this:

          sqlite> .separator ","
          sqlite> .import test.csv test
          

          It pulls the column names automatically, as you can see:

          sqlite> .schema
          CREATE TABLE test(
            "id" TEXT,
            "name" TEXT
          );
          

          Unfortunately it makes every column TEXT, which is fine until you need numeric comparison. Which you often do. So I would import into a temp table, copy the schema from the .schema command, change the types appropriately, and do an insert-select. Like this:

          sqlite> .separator ","
          sqlite> .import test.csv test_import
          sqlite> .schema
          CREATE TABLE test_import(
            "id" TEXT,
            "name" TEXT
          );
          sqlite> CREATE TABLE test(
             ...>   "id" INTEGER,
             ...>   "name" TEXT
             ...> );
          sqlite> insert into test select * from test_import;
          

          q is faster for a small one-off, but having a full persistent SQLite database was surprisingly helpful for my work. A lot of the CSV files I was looking at had a particular schema, so in the end I had a number of schemas and queries saved as SQL scripts I would execute with .read.