combine two csv files based on common column using awk or sed

user_4340040 0
Viewed 3632

I have a two CSV file which have a common column in both files along with duplicates in one file. How to merge both csv files using awk or sed?

CSV file 1

5/1/20,user,mark,Type1 445566
5/2/20,user,ally,Type1 445577
5/1/20,user,joe,Type1 445588
5/2/20,user,chris,Type1 445566

CSV file 2 

Type1 445566,Name XYZ11
Type1 445577,Name AAA22
Type1 445588,Name BBB33
Type1 445566,Name XYZ11

What I want is?

5/1/20,user,mark,Type1 445566,Name XYZ11
5/2/20,user,ally,Type1 445577,Name AAA22
5/1/20,user,joe,Type1 445588,Name BBB33
5/2/20,user,chris,Type1 445566,Name XYZ11

So is there a bash command in Linux/Unix to achieve this? Can we do this using awk or sed?

Basically, I need to match column 4 of CSV file 1 with column 1 of CSV file 2 and merge both csv's.

Tried following command:

Command:

paste -d, <(cut -d, -f 1-2 ./test1.csv | sed 's/$/,Type1/') test2.csv

Got Result:

5/1/20,user,Type1,Type1 445566,Name XYZ11

sed awk unix linux bash
2 Answers

If you are able to install the join utility, this command works:

join -t, -o 1.1 1.2 1.3 2.1 2.2 -1 4 -2 1 file1.csv file2.csv

Explanation:

-t, identify the field separator as comma (',')

-o 1.1 1.2 1.3 2.1 2.2 format the output to be "file1col1, file1col2, file1col3, file2col1, file2col2`

-1 4 join by column 4 in file1

-2 1 join by column 1 in file2

For additional usage information for join, reference the join manpage.

Edit: You specifically asked for the solution using awk or sed so here is the awk implementation:

awk -F"," 'NR==FNR {a[$1] = $2; next} {print $1","$2","$3","$4"," a[$4]}' \
    file2.csv \
    file1.csv

Explanation:

-F"," Delimit by the comma character

NR==FNR Read the first file argument (notice in the above solution that we're passing file2 first)

{a[$1] = $2; next} In the current file, save the contents of Column2 in an array that uses Column1 as the key

{print $1","$2","$3","$4"," a[$4]} Read file1 and using Column4, match the value to the key's value from the array. Print Column1, Column2, Column3, Column4, and the key's value.

user_129279090

The two example input files seem to be already appropriately sorted, so you just have to put them side by side, and paste is good for this; however you want to remove some ,-separated columns from file1, and you can use cut for that; but you also want to insert another (constant) column, and sed can do it. A possible command is this:

paste -d, <(cut -d, -f 1-2 file1 | sed 's/$/,abcd/') file2

Actually sed can do the whole processing of file1, and the output can be pided into paste, which uses - to capture it from the standard input:

sed -E 's/^(([^,]+,){2}).*/\1abcd/' file1 | paste -d, - file2
user_58252940
Related