Merging of two csv files using awk based on common column

Question

I have one file with 11 columns with first column as primary id - P1 second csv with three columns with first column as same primary id - P1, though not at same level in both files, I am merging both files using below command:

awk 'NR==FNR {h[$2] = $3; next} {print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,h[$2]}' first.csv second.csv > final.csv

however, getting only three columns in new csv

Thomas Baruchel · Accepted Answer · 2015-10-18 15:30:38Z

1

You should see if join wouldn't be an easier solution. Type man join for that:

join - join lines of two files on a common field

answered Oct 18, 2015 at 15:30

Thomas Baruchel

7,5353 gold badges29 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aquaabhi Over a year ago

i have used join -t, <(sort -t, -k1 second.csv) <(sort -t, -k1 first.csv) > third.csv, however it is giving illegal byte sequence error

user000001 · Accepted Answer · 2015-10-18 15:32:13Z

0

If first.csv has 11 columns and second.csv has three, then you have your files are in the wrong order. Try like this:

awk 'NR==FNR {h[$2] = $3; next} {print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,h[$2]}' second.csv first.csv > final.csv

You are also not using the first column as keys in this example, but the second one.

answered Oct 18, 2015 at 15:32

user000001

33.7k14 gold badges86 silver badges113 bronze badges

Collectives™ on Stack Overflow

Merging of two csv files using awk based on common column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related