Merging two CSVs based on matching first column (with header)

Question

I have two CSV files. One of which contains a lot of data:

main.csv

Order No.,invoiceNumber,invoiceAmount,invoiceAmountNet
20001,20010,500,5
20002,20011,400,4
20003,20012,300,3
20004,20013,200,2
20005,20014,200,1

While my other CSV file has less information:

october-2020.csv

Order No.,Timestamp,ID
20001,2020-10-01 12:00:00,123456
20002,2020-10-02 13:00:00,123457

And my goal is to create a new CSV file based on october-2020.csv and adding information from main.csv. The common information is the Order No.:

output.csv

Order No.,invoiceNumber,invoiceAmount,invoiceAmountNet,Order No.,Timestamp,ID
20001,20010,500,5,20001,2020-10-01 12:00:00,123456
20002,20011,400,4,20002,2020-10-02 13:00:00,123457

How could this be scripted? I appreciate any help.

Does this - Merge multiple files with join unix.stackexchange.com/q/364735/85046 answer your question? — Jakub Jindra
– Jakub Jindra, Commented Oct 9, 2020 at 5:54

Freddy · Accepted Answer · 2020-10-08 21:51:56Z

Using join on the first field (Order No.) of both files with , as field separator:

join -t, -o1.1,1.2,1.3,1.4,2.1,2.2,2.3 main.csv october-2020.csv > output.csv

and

$ cat output.csv
Order No.,invoiceNumber,invoiceAmount,invoiceAmountNet,Order No.,Timestamp,ID
20001,20010,500,5,20001,2020-10-01 12:00:00,123456
20002,20011,400,4,20002,2020-10-02 13:00:00,123457

Since both files are already sorted on the first field, we don't need to sort them manually. And since you want the Order No. to be present twice in the output, the fields are specified with the -o option (fields 1-4 of the first input file, fields 1-3 of the second file).

Marcin Ślusarz · Accepted Answer · 2021-01-09 01:55:48Z

0

csv-sqlite -i main.csv -i october-2020.csv \
   'select i1.invoiceNumber, i1.invoiceAmount, i1.invoiceAmountNet, i2.*
      from input1 i1, input2 i2
     where i1."Order No." = i2."Order No."' | csv-header --remove-types

csv-sqlite is from csv-nix-tools

answered Jan 9, 2021 at 1:55

Marcin Ślusarz

412 bronze badges

Add a comment |

Stack Exchange Network

Merging two CSVs based on matching first column (with header)

2 Answers 2

You must log in to answer this question.

Linked

Hot Network Questions

Merging two CSVs based on matching first column (with header)

2 Answers 2

You must log in to answer this question.

Linked

Related

Hot Network Questions