&| What | '''Sqawk''' |&
&| Where | https://github.com/dbohdan/sqawk |&
&| Description | An awk-like program that uses SQL. Can parse, format, filter, and combine data from multiple files. Powered by SQLite. |&
&| Platforms | Linux, Windows XP/7/8.x/10, macOS, FreeBSD, NetBSD, OpenBSD. |&
&| Prerequisites | Tcl 8.5 or later, [Tcllib], [SQLite3] bindings for Tcl. |&&| Updated | 20214-085-310 (v0.234.10) |&
&| License | MIT |&
&| Contact | [dbohdan] |&
** Examples **
*** Sum up numbers ***
`find . -iname '*.jpg' -type f -printf '%s\n' | sqawk 'select sum(a1)/1024/1024 from a'`
*** Shuffle lines ***
`sqawk -1 'select a1 from a order by random()' < file`
*** Find duplicate lines ***
Print them and how many times they are repeated.
`sqawk -1 -OFS ' -- ' 'select a0, count(*) from a group by a0 having count(*) > 1' < file`
**** Sample output ****
======
13 -- 2
16 -- 3
83 -- 2
100 -- 2
======
*** Combine data from two files ***
**** Input files ****
`MD5SUM`
======
d2e7d4d1c7587b40ef7e6637d8d777bc 0005.jpg
4e7cde72529efc40f58124f13b43e1d9 001.jpg
e2ab70817194584ab6fe2efc3d8987f6 0.0.6-settings.png
9d2cfea6e72d00553fb3d10cbd04f087 010_2.jpg
3df1ff762f1b38273ff2a158e3c1a6cf 0.10-planets.jpg
0be1582d861f9d047f4842624e7d01bb 012771602077.png
60638f91b399c78a8b2d969adeee16cc 014tiles.png
7e7a0b502cd4d63a7e1cda187b122b0b 017.jpg
1e958dfe8f99de90bb8a9520a0181f51 01newscreenie.jpeg
f56fb95efa84fbfdd8e01222b4a58029 02_characterselect.jpg
[...]
======
`list`
======
drwxr-xr-x. 2 dbohdan dbohdan 94208 Apr 7 2013 .
drwxr-xr-x. 4 dbohdan dbohdan 4096 Apr 14 2014 ..
-rw-r--r--. 1 dbohdan dbohdan 136229 Apr 7 2013 0005.jpg
-rw-r--r--. 1 dbohdan dbohdan 112600 Apr 7 2013 001.jpg
-rw-r--r--. 1 dbohdan dbohdan 26651 Apr 7 2013 0.0.6-settings.png
-rw-r--r--. 1 dbohdan dbohdan 155579 Apr 7 2013 010_2.jpg
-rw-r--r--. 1 dbohdan dbohdan 41485 Apr 7 2013 0.10-planets.jpg
-rw-r--r--. 1 dbohdan dbohdan 2758972 Apr 7 2013 012771602077.png
-rw-r--r--. 1 dbohdan dbohdan 426774 Apr 7 2013 014tiles.png
[...]
======
**** Query ****
`sqawk 'select a.a1, b.b5, a.a2 from a inner join b on a.a2 = b.b9 where b.b5 < 10000 order by b.b5' MD5SUMS list`
**** Output ****
======
d50700db41035eb74580decf83f83184 615 z81.png
e1b64d03caf4615d54e9022d5b13a22d 677 init.png
a0fb29411c169603748edcc02c0e86e6 823 agendaroids.gif
3b0c65213e121793d4458e09bb7b1f58 970 screen01.gif
05f89f23756e8ea4bc5379c841674a6e 999 retropong.png
a49a7b5ac5833ec365ed3cb7031d1d84 1458 fncpong.png
80616256c790c2a831583997a6214280 1516 el2_small.jpg
1eee29eaef2ae740d04ad4ee6d140db7 1575 thrust-0.89f.gif
19edfd5a70c9e029b9c601ccb17c4d12 1665 xrockman.png
4e1105deeeba96e7b935aa07e2066d89 1741 xroarsnap.png
[...]
======
** See also **
* [Awk]
* [owh - a fileless tclsh]
*** Subprojects ***
* [Assemble (tool)%|%Assemble] — A code bundler and [preprocessor] for Tcl.
* [Tabulate] — Used by Sqawk for table output.
** Comments **
[JM] on windows, make sure you use double quotes for your SQL script
3/30-2016 [JM]: is it possible to have an option to save the SQLite database in a file?
* I guess there is a conversion to an in-memory database.
* This way I can save time on the conversion if the source file does not change often and want to use [SQLiteStudio] for example
3/31: answering to myself, I think I am missing the point,SQLite clients provide the import from csv file types already.
[dbohdan] 2017-11-18: Starting with version 0.21 you can use an on-disk SQLite database with the `-dbfile` option. Here is an example with CSV import.
======
$ cat test.csv
1,2,3
4,5,6
7,8,9
$ sqawk -dbfile csv-import.sqlite3 'select "only importing"' format=csv test.csv
only importing
$ sqawk -dbfile csv-import.sqlite3 -noinput 'select a1, a2 from a where a1 + a2 > 7'
4 5
7 8
======
<<categories>> Application | SQLite | String Processing