Merge 2 files using command line tools

Question

I am 2 csv files looking like this:

id, name, job
1, bob, fireman
3, alice, nurse
7, peter, policeman
...

And:

id, name, age
2, john, 26
4, craig, 32
5, mary, 45
6, lucy, 23
...

As you can see, they are both sorted by ids and the ids missing in the first csv are actually in the second csv.

Is it possible via a command line tool such as awk or something similar to merge these 2 csv into one looking like this?

id, name, job, age
1, bob, fireman,
2, john, , 26
3, alice, nurse,
4, craig, , 32
...

Many thanks for your help?

Answer 1

This should do:

awk -F, -v OFS=, 'FNR==NR && FNR>1 {a[$1]=$0;c++;next} FNR>1{$NF=" ,"$NF;a[$1]=$0;c++} END {print "id, name, job, age";for (i=1;i<=c;i++) print a[i]}' file1 file2
id, name, job, age
1, bob, fireman
2, john, , 26
3, alice, nurse
4, craig, , 32
5, mary, , 45
6, lucy, , 23
7, peter, policeman

How it works:

awk -F, -v OFS=, '              # Set input and output Field separator to ","
FNR==NR && FNR>1 {              # For first file except first record do:
    a[$1]=$0                    # Store records inn to array "a"
    c++                         # Increment "c" for every record
    next}                       # Skip to next record
FNR>1 {                         # For second file except first record do:
    $NF=" ,"$NF                 # Replace last record with an extra ","
    a[$1]=$0                    # Store records inn to array "a"
    c++}                        # Increment "c" for every record
END {                           # When all file is read do:
    print "id, name, job, age"  # Print header
    for (i=1;i<=c;i++)          # Loop "c" times
        print a[i]}             # Print records
' file1 file2                   # Read the files

FNR==NR are often used when reading several files to distinguish what file to work on