TextQL: Execute SQL Against CSV or TSV

Re:TextQL: Execute SQL Against CSV or TSV by daamien on Saturday April 07, 2018 @01:13PM (#16781623)

PostgreSQL does this out of the box with :

  CREATE EXTENSION file_fdw;
  CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
  CREATE FOREIGN TABLE foo (
    col1 text,
    col2 text,
    ...
  ) SERVER import 
  OPTIONS ( filename '/path/to/foo.csv', format 'csv' );
  SELECT col1 FROM foo WHERE col2='x';

[ Parent | on HN ]

Re:TextQL: Execute SQL Against CSV or TSV by daamien on Saturday April 07, 2018 @01:21PM (#16781662)

... and with dozens of other data sources:

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

[ Parent | on HN ]

Re:TextQL: Execute SQL Against CSV or TSV by techtalsky on Saturday April 07, 2018 @02:15PM (#16781926)

However this is something you can put in /use/local/bin and use alongside other command line tools with pipes

[ Parent | on HN ]

Re:TextQL: Execute SQL Against CSV or TSV by banku_brougham on Saturday April 07, 2018 @02:46PM (#16782117)

Having to define the schema is the major hangup for me in my workflow. I would prefer a wrapper that could run within unix pipelines around arbitrary text data files, but they all use SQLite.

I personally have been using harelba’s q (the un-googleable utility), which is just fine.

It would be great not to shift gears into SQLite syntax and date formatting all the time. Does anyone know of a similar tool that runs over postgres?

[ Parent | on HN ]

Re:TextQL: Execute SQL Against CSV or TSV by chrisweekly on Saturday April 07, 2018 @10:10PM (#16784200)

> "prefer a wrapper that could run within unix pipelines around arbitrary text data files"

That sounds like [lnav](https://www.lnav.org).

For queries, it does use SQLite under the hood, but before querying your "arbitrary text data" files, you can use regex to define a custom format (eg with named groups and back references), providing structure against which standard SQL is an ideal tool to query.

It's simpler than I'm probably making it sound. Highly recommended.

[ Parent | on HN ]

Re:TextQL: Execute SQL Against CSV or TSV by Myrmornis on Saturday April 07, 2018 @02:28PM (#16782011)

Which is exactly why a convenient alternative was desired.

[ Parent | on HN ]