TextQL: Execute SQL Against CSV or TSV

Post by banku_brougham » Sat, Apr 07, 2018, 6:46 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';

Post by gaius » Sat, Apr 07, 2018, 6:56 PM UTC

TAForObvReasons wrote: post #14

gaius wrote: post #11

Earlier quoted context omitted.

But it is SQLite under the covers? A better title then would be an SQLite wrapper for use in shell pipelines.

That's a silly complaint. It's a tool powered by SQLite. Under your extended definition, tools like web browsers (which use SQLite to power various things) would be SQLite wrappers.

Post by ravinizme » Sat, Apr 07, 2018, 7:07 PM UTC

Post by MR4D » Sat, Apr 07, 2018, 8:01 PM UTC

jssmith wrote: post #21

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?

As a command-line tool it could serve as a more powerful replacement for unix utilities that I often use for simple analysis jobs: sort, uniq, wc, awk, sometimes cut and sed. I'm going to give it a try. It looks like textql loads everything into an in-memory SQLite instance, whereas I'd really like to see an approach that uses the SQLite's virtual table mechanism ( https://sqlite.org/vtab.html ), which would avoid th…

Post by codemac » Sat, Apr 07, 2018, 8:29 PM UTC

TAForObvReasons wrote: post #7

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?

It's discussed in the README: https://github.com/dinedal/textql#key-differences-between-te... > 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.

    #!/usr/bin/env rc
    
    row_headers=''
    output_mode=column
    
    fn usage {
      echo $0 usage
      echo   -o  '#' which output mode to use
      echo   -r                      '#' if present, output row headers
      echo   -h                      '#' this message
    }
    
    while(~ $1 -*) {
      switch($1) {
        case -o
          output_mode=$2
          shift
        case -r
          row_headers='.headers on'
        case -h
          usage
          exit 0
        case *
          echo Bad args: $*
          usage
          exit 1
       }
       shift
    }
    
    test $#* -ne 1 && echo Wrong number of arguments && usage && exit 1
    
    sql_statement=$1
    sqlite_bin=''
    { which sqlite > /dev/null >[2=1] && sqlite_bin=sqlite } || { which sqlite3 >/dev/null >[2=1] && sqlite_bin=sqlite3 }
    
    table_name=atable
    stdin_file=/proc/$pid/fd/0
    {
      echo .mode csv
      echo .import $stdin_file $table_name
      echo $row_headers
      echo .mode $output_mode
      echo $sql_statement
    } | $sqlite_bin