TextQL: Execute SQL Against CSV or TSV

Post by gaius » Sat, Apr 07, 2018, 4:35 PM UTC

lowboy wrote: post #8

gaius wrote: post #5

In SQLite you can just do sqlite> .import myfile.csv mytable sqlite> select ... What does this tool give you that SQLite doesn't do out of the box?

From the README: Key differences between textql and sqlite importing sqlite import will not accept stdin, breaking unix pipes. textql will happily do so. textql supports quote escaped delimiters, sqlite does not. textql leverages the sqlite in memory database feature as much as possible and only touches disk if asked.

Post by [deleted] » Sat, Apr 07, 2018, 4:51 PM UTC

ConceitedCode wrote: post #3

This looks very simple and easy to use. Are there any differences with using this instead of CSVKit? https://csvkit.readthedocs.io/en/1.0.3/ It includes a tool called csvsql. Example usage - csvsql --query "select name from data where age > 30" data.csv > new.csv

Post by jhoechtl » Sat, Apr 07, 2018, 5:00 PM UTC

kibwen wrote: post #10

If you work with CSV a lot, then also check out xsv, "a command line program for indexing, slicing, analyzing, splitting and joining CSV files", from Andrew Gallant (burntsushi of Go and Rust fame): https://github.com/BurntSushi/xsv

Post by daamien » Sat, Apr 07, 2018, 5:13 PM UTC

  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';

Post by daamien » Sat, Apr 07, 2018, 5:21 PM UTC

daamien wrote: post #17

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';