Ubuntu – Add column from one .csv to another .csv file

bashcsvjoin;

file1.csv

A,,C,D
A,,C,D
A,,C,D
A,,C,D

file2.csv

A,B
A,B
A,B
A,B

desired Output.csv

A,B,C,D
A,B,C,D
A,B,C,D
A,B,C,D

I've tried using "join" and "paste" to no avail. Is there a bash command to do this? Column "A" is the same in both .csv files.

Best Answer

With only awk command:

awk -F, '{getline f1 <"file2" ;print f1,$3,$4}' OFS=, file1

Get a line from file1 and store it into local variable f1, then print the line that stored in f1 and finally print the third($3) and forth($3) fields from file1 which delimited with comma , altogether, and change the OFS(output field separator [space by default]) to comma(,).

The short command would be like this:

paste -d, file2 <(cut -d, -f3- file1)

 A,B,C,D  
 A,B,C,D  
 A,B,C,D  
 A,B,C,D

paste the file2, then cut and paste the third column to the next(-f3-) from file1.

With awk and paste (option A)

Below command also copies the last two columns (C,D) from file1 at the end of each line in file2:

paste -d',' file2  <(awk -F',' '{print $(NF-1)","$NF}' file1)

Above command paste the file2 content then print a comma delimiter(-d',') then paste the two last field(NF is the index of last field and $NF is the string which its index is NF. So $(NF-1) is the second field before last field ) from file1 when those index redefines or splits with comma spectator(-F',').

With awk and paste (option B)

This command also is the same as above($3 and $4 points to third and forth field of each line from file1 ):

paste -d',' file2  <(awk -F',' '{print $3","$4}' file1)

Or another solution with cut command:

paste -d, <(cut -d, -f1 file1) <(cut -d, -f2 file2) <(cut -d, -f3- file1)

cut command in above command first cut the first field(-f1 which indexed with comma delimiter(-d.)) from file1(cut -d, -f1 file1), then cut and paste the second field of file2(cut -d, -f2 file2) and finally cut and paste the third column(-f3) to the nexts(-) from file1(cut -d, -f3- file1) again.

This command also returns the same result:

paste -d, <(awk -F',' '{print $1}' file1) <(awk -F',' '{print $2}' file2) <(awk -F',' '{print $3","$4}' file1)

paste the second field from file1(awk -F',' '{print $1}' file1) then print a comma(-d,), then paste the second column from file2(awk -F',' '{print $2}' file2), finally paste the second and last column of file1(awk -F',' '{print $3","$4}' file1) again.

Related Question