CSVJoin2: Table Align & Merge tool

Tool to align and merge two tables containing different parts of the same data, without losing or duplication of it

Installation

From RubyGems:

gem install csvjoin

From GitHub (latest development version):

gem install specific_install
gem specific_install https://github.com/serg123e/csvjoin.git

Usage

csvjoin2 [options] FILE1 FILE2 [COL1=COL2,COL3=COL4]

If no columns are specified, columns with the same name in both files are used for comparison.

Options

Option	Description
`-o`, `--output FILE`	Write output to FILE instead of stdout
`--sep SEPARATOR`	Set separator for both input files (default: auto-detect)
`--sep1 SEPARATOR`	Set separator for first input file
`--sep2 SEPARATOR`	Set separator for second input file
`--out-sep SEPARATOR`	Set separator for output CSV (default: same as first file)
`-i`, `--ignore-case`	Case-insensitive comparison

Diff markers

Marker	Meaning
`===`	Rows matched in both files
`==>`	Row exists only in the left file
`<==`	Row exists only in the right file

Example

It's a typical task when automation meets the real world and we need to join two raw tables without a unique ID present in both. We can't join them in a SQL way and should rely on weaker criteria like name and row order.

Migration from Excel-based accounting to an ERP, where we have several tables to join and all of them contain almost-correct-but-with-some-errors data
Balance sheet account reconciliation
Bank reconciliation, looking for the difference between a book balance and bank balance
Invoices & Payments Reconciliation

one table with invoices, in file invoices.csv

invoice_id,client,price
13,Best,200.0
12,Fest,150.0
11,Test,100.0

and another with incoming payments, in payments.csv

name,payment_date,amount
Best,2020-05-01,200.0
Gest,2020-05-06,100.0
Test,2020-04-15,90.0
Test,2020-04-16,10.0

In order to check that all sales have been paid we need to compare these tables. Please notice that both tables have to be ordered using the same principle (by client name + by id, and by name + by date).

csvjoin2 invoices.csv payments.csv client=name,price=amount

invoice_id,client,price,diff,name,payment_date,amount
13,Best,200.0,===,Best,2020-05-01,200.0
12,Fest,150.0,==>,"","",""
"","","",<==,Gest,2020-05-06,100.0
11,Test,100.0,===,Test,2020-04-15,100.0
"","","",<==,Test,2020-04-16,10.0

Now we can see that:

2 invoices (Best, Test) matched their payments (===)
invoice for Fest has no corresponding payment (==>)
payments from Gest and the second Test payment have no corresponding invoices (<==)

CSVKit

There is a great suite CSVKit with a lot of handy CSV tools, including the tool csvjoin, but it does inner/outer joins which are not as helpful in cases like the one described above

csvjoin --outer -c name invoices.csv payments.csv

    invoice_id,name,price,name2,payment_date,amount
    13,Best,200.0,Best,2020-05-01,200.0
    12,Fest,150.0,,,
    11,Test,100.0,Test,2020-04-15,100.0
    **11,Test,100.0**,Test,2020-04-16,10.0
    ,,,Gest,2020-05-06,100.0

csvjoin

Development