| What | '''Sqawk''' | | Where | https://github.com/dbohdan/sqawk | | Description | An Awk-like program that uses SQL and can process multiple files at once. It is powered by SQLite. | | Platforms | Linux, Windows XP/7/8.x, OS X, FreeBSD. | | Prerequisites | Tcl 8.5 or newer, [Tcllib], [SQLite3] bindings for Tcl. | | Updated | 2017-11-03 (v0.21.0) | | License | MIT | ** 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 ====== <> Application | SQLite | String Processing