Readme.md
1# TextQL
2
3[](https://travis-ci.org/dinedal/textql) [](http://goreportcard.com/report/dinedal/textql)
4
5Allows you to easily execute SQL against structured text like CSV or TSV.
6
7Example session:
8
9
10
11## Major changes!
12
13In 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.
14
15### Changes since v1
16
17Additions:
18
19- Numeric values are automatically recognized in more cases.
20- Date / Time / DateTime values are automatically recognized in reasonable formats. See [Time Strings](https://www.sqlite.org/lang_datefunc.html) for a list for accepted formats, and how to convert from other formats.
21- Added join support! Multiple files / directories can be loaded by listing them at the end of the command.
22- Directories are read by reading each file inside, and this is non-recursive.
23- You can list as many files / directories as you like.
24- Added flag '-output-file' to save output directly to a file.
25- Added flag '-output-dlm' to modify the output delimiter.
26- Added "short SQL" syntax.
27 - For the case of a single table, the `FROM [table]` can be dropped from the query.
28 - For simple selects, the `SELECT` keyword can be dropped from the query.
29 - This means the v1 command `textql -sql "select * from tbl" -source some_file.csv` can be shortened to `textql -sql "*" some_file.csv`
30
31Changes:
32
33- The flag '-outputHeader' was renamed to '-output-header'.
34
35Removals:
36
37- 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.
38- Removed '-source', any files / paths at the end of the command are used, as well as piped-in data.
39
40Bug fixes:
41
42- Writing to a directory no longer fails silently.
43
44## Key differences between textql and sqlite importing
45
46- sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.
47- textql supports quote-escaped delimiters, sqlite does not.
48- textql leverages the sqlite in-memory database feature as much as possible and only touches disk if asked.
49
50## Is it any good?
51
52[Yes](https://news.ycombinator.com/item?id=3067434)
53
54## Requirements
55
56- Go 1.4 or later
57
58## Install
59
60**Latest release on Homebrew (OS X)**
61
62```bash
63brew install textql
64```
65
66**Build from source**
67
68```bash
69go get -u github.com/dinedal/textql/...
70```
71
72## Docker
73
74First build the image.
75
76```bash
77docker build -t textql .
78```
79
80Now use that image mounting your current directory into the container.
81
82```bash
83docker run --rm -it -v $(pwd):/tmp textql [rest_of_command]
84```
85
86### Alias
87
88You can add the following alias to your system to provide quick access to TextQL:
89
90```bash
91alias textql='docker run --rm -it -v $(pwd):/tmp textql '
92```
93
94## Usage
95
96```bash
97 textql [-console] [-save-to path path] [-output-file path] [-output-dlm delimter] [-output-header] [-pretty] [-quiet] [-header] [-dlm delimter] [-sql sql_statements] [path ...]
98
99 -console
100 After all statements are run, open SQLite3 REPL with this data
101 -dlm string
102 Input delimiter character between fields -dlm=tab for tab, -dlm=0x## to specify a character code in hex (default ",")
103 -header
104 Treat input files as having the first row as a header row
105 -output-dlm string
106 Output delimiter character between fields -output-dlm=tab for tab, -dlm=0x## to specify a character code in hex (default ",")
107 -output-file file
108 Filename to write output to, if empty no output is written (default "stdout")
109 -output-header
110 Display column names in output
111 -quiet
112 Suppress logging
113 -pretty
114 Pretty print output
115 -save-to file
116 SQLite3 db is left on disk at this file
117 -sql string
118 SQL Statement(s) to run on the data
119 -version
120 Print version and exit
121```
122
123## I want stdev, average, other functions
124
125Just follow the install directions at [go-sqlite3-extension-functions](https://github.com/dinedal/go-sqlite3-extension-functions) and textql will automatically load this library.
126
127Full function list:
128
129- 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.
130- String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter.
131- Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile
132
133## License
134
135New MIT License - Copyright (c) 2015, 2016 Paul Bergeron [http://pauldbergeron.com/](http://pauldbergeron.com/)
136
137See LICENSE for details
138
){kind=link}
){kind=link}