Skip to content
Execute SQL against structured text like CSV or TSV
Go Yacc Other
Branch: master
Clone or download

Latest commit

Latest commit 1d6fef5 Feb 3, 2020

Files

Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
inputs Changes golang package for getting base of filenames to "path/filepath" Apr 13, 2018
man Add `-pretty` as a command flag, output pretty printed tables using g… Dec 18, 2015
outputs don't print blank results for create table queries Apr 15, 2018
sqlparser The magic short SQL syntax will also escape the table name Apr 13, 2018
storage Ensure windows can also load `libgo-sqlite3-extension-functions` Feb 2, 2020
test_util Small refectoring, many improvements in testing, started adding stora… Jun 19, 2015
textql Added support for tagged filename (label:path) to specify tablename Mar 23, 2019
util prevent slice bounds out of range Dec 16, 2015
.gitignore Fix misnamed resulting binary in normal go install Dec 15, 2015
.travis.yml fix travis.yml Feb 3, 2020
Dockerfile add sqlite3 to docker image for option "-console" May 22, 2018
Dockerfile.alpine reduce docker image to 19MB May 22, 2018
LICENSE MIT License added, closes #16 Feb 6, 2014
Makefile Update from godep / Gopkg to go modules. Feb 2, 2020
Readme.md Closes #54 - Adds a way to get more mathematical functions. Feb 2, 2020
TODO.txt Add todo text to track final changes as release approaches Aug 24, 2015
VERSION Version bump Dec 17, 2015
go.mod Update from godep / Gopkg to go modules. Feb 2, 2020
go.sum Update from godep / Gopkg to go modules. Feb 2, 2020
textql_usage.gif Update usage gif Aug 24, 2015

Readme.md

TextQL

Build Status Go Report Card

Allows you to easily execute SQL against structured text like CSV or TSV.

Example session:

textql_usage_session

Major changes!

In the time since the initial release of textql, I've made some improvements as well as made the project much more modular. There've also been additional performance tweaks and added functionality, but this comes at the cost of breaking the original command-line flags and changing the install command.

Changes since v1

Additions:

  • Numeric values are automatically recognized in more cases.
  • Date / Time / DateTime values are automatically recognized in reasonable formats. See Time Strings for a list for accepted formats, and how to convert from other formats.
  • Added join support! Multiple files / directories can be loaded by listing them at the end of the command.
  • Directories are read by reading each file inside, and this is non-recursive.
  • You can list as many files / directories as you like.
  • Added flag '-output-file' to save output directly to a file.
  • Added flag '-output-dlm' to modify the output delimiter.
  • Added "short SQL" syntax.
    • For the case of a single table, the FROM [table] can be dropped from the query.
    • For simple selects, the SELECT keyword can be dropped from the query.
    • This means the v1 command textql -sql "select * from tbl" -source some_file.csv can be shortened to textql -sql "*" some_file.csv

Changes:

  • The flag '-outputHeader' was renamed to '-output-header'.

Removals:

  • Dropped the ability to override table names. This makes less sense after the automatic tablename generation based on filename, joins, and shorter SQL syntax changes.
  • Removed '-source', any files / paths at the end of the command are used, as well as piped-in data.

Bug fixes:

  • Writing to a directory no longer fails silently.

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.

Is it any good?

Yes

Requirements

  • Go 1.4 or later

Install

Latest release on Homebrew (OS X)

brew install textql

Build from source

go get -u github.com/dinedal/textql/...

Docker

First build the image.

docker build -t textql .

Now use that image mounting your current directory into the container.

docker run --rm -it -v $(pwd):/tmp textql [rest_of_command]

Alias

You can add the following alias to your system to provide quick access to TextQL:

alias textql='docker run --rm -it -v $(pwd):/tmp textql '

Usage

  textql [-console] [-save-to path path] [-output-file path] [-output-dlm delimter] [-output-header] [-pretty] [-quiet] [-header] [-dlm delimter] [-sql sql_statements] [path ...]

  -console
        After all statements are run, open SQLite3 REPL with this data
  -dlm string
        Input delimiter character between fields -dlm=tab for tab, -dlm=0x## to specify a character code in hex (default ",")
  -header
        Treat input files as having the first row as a header row
  -output-dlm string
        Output delimiter character between fields -output-dlm=tab for tab, -dlm=0x## to specify a character code in hex (default ",")
  -output-file file
        Filename to write output to, if empty no output is written (default "stdout")
  -output-header
        Display column names in output
  -quiet
        Suppress logging
  -pretty
        Pretty print output
  -save-to file
        SQLite3 db is left on disk at this file
  -sql string
        SQL Statement(s) to run on the data
  -version
        Print version and exit

I want stdev, average, other functions

Just follow the install directions at go-sqlite3-extension-functions and textql will automatically load this library.

Full function list:

  • Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi.
  • String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter.
  • Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile

License

New MIT License - Copyright (c) 2015, 2016 Paul Bergeron http://pauldbergeron.com/

See LICENSE for details

You can’t perform that action at this time.