# File Diff with SQLite Shell

> **TL;DR**
> Use SQLite's shell as a diff tool: import two files as tables, then run a `EXCEPT` query to find lines present in one file but not the other — ideal for reprocessing failed records.

While working on [OkHttp3 Scripting](okhttp3-scripting.html)

I needed to do a diff from the original input file, to successful cases

and rerun for failed queries.

This is the use case we are handling here:

original input file

`input.txt`

```TEXT
xxxx;B7;C7
xxxx;B12;C12
xxxx;B29;C29
xxxx;B15;C15
xxxx;B41;C41
xxxx;B58;C58
xxxx;B8;C8

```

We get some output in the form of

success.txt

```TEXT

xxxx;B7;C7
xxxx;B12;C12
xxxx;B29;C29
xxxx;B15;C15


```

failure.txt

```TEXT

xxxx;B41;C41
xxxx;B58;C58
xxxx;B8;C8

```

What we need is to only process (input - success) `input.txt`

```TEXT
xxxx;B41;C41
xxxx;B58;C58
xxxx;B8;C8
```

This is an A-B set operation that SQL handles well:

![sql-join.png](img/sql-join.png)

So let's make it into one :D

## SQLite Shell

SQLite Shell is perfect for this operation, I've tested it at least on a 50K+ records file and it returns almost immediately:

Get SQLite binaries. Work on a single directory if you wish

![sqlite.png](img/sqlite.png)

SQL file to run:

`diff.sql`

```SQL
create table file1(line text);
create index if1 on file1(line ASC);
create table file2(line text);
create index if2 on file2(line ASC);
-- comment: if you have | in your files then specify “ .separator ××any_improbable_string×× ”
.import 'success.txt' file1
.import 'input.txt' file2
.output input.txt
select * from file2 where line not in (select line from file1);
.q

```

Check the paths to your files and run `.read diff.sql` in the shell:

```SHELL
➜  sqlite git:(master) ✗ sqlite3
➜  sqlite git:(master) ✗ sqlite3
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read diff.sql
sqlite> 

```

input.txt is now overwritten with only the remaining elements to process:

`input.txt`

```TEXT
xxxx;B41;C41
xxxx;B58;C58
xxxx;B8;C8
```

You can rerun your script against failures. Some can be due to network issues, timeouts that can happen in [OkHttp3 Scripting](okhttp3-scripting.html), etc.

Those are worth the retry :)