11 Nov

Compare text files in SQL

Here’s a quick hack that allows loading text files as tables in a sqlite database. Why? It’s pretty nice to compare files in SQL.

txtsql can be run as follows: txtsql.exe file.txt file2.txt ...

Every text file is loaded as a table with two columns: row (line number) and data (the actual text). When saved with save and the first column is named row, it will be skipped. Thus, every loaded table and ever table created with new will be saved as a normal text file. Any other table will be saved with tab characters separating the cells.

The program is just a test to see if this is a viable concept (post a comment if you think it is). If it seems to be something to develop further, I’ll add something like transparent loading and saving of text files as tables when they are invoked (now you have to load them with the load command), common text operations (fuzzy searching etc.) and such.

Examples

Count lines:

load `file.txt`
select count(*) from `file.txt`

Save unique lines in a new file:

load `file.txt`
new `uniq.txt`
insert into `uniq.txt` (data) select distinct data from `file.txt`
save `uniq.txt`

Find similar lines:

load `1.txt`
load `2.txt`
select data from `1.txt` where data in (select data from `2.txt`)

txtsql.zip – Binaries (Win32) and source code