Shell Programming and Scripting

I have two csv files : say a.csv, b.csv
a.csv looks like this :

Code:

property1,property2,100
property3,property4,200

In a.csv, the combination of column1 and column2 will be unique


b.csv looks like this

Code:

property1,property2, 300, t1
property1,property2, 400,t2
property3, property4,800,t1
property3, property4,12200,t2

And I want the output to be :
A new column added which is the third column from a.csv matching first two columns of a.csv and b.csv

Code:

property1,property2, 300, t1,100
property1,property2, 400,t2,100
property3, property4,800,t1,200
property3, property4,12200,t2,200

I am not sure how we can achieve this.
Please help.

Thanks

Moderator's Comments:

Please use CODE tags as required by forum rules!

Any attempts/ideas/thoughts from your side? Did you consider searching these fora for similar problems, e.g. the links given at the bottom of this page?

RudiC has given you some good pointers to how you could solve your problem. This kind of problem is posted here frequently. What makes this problem stand out is the imperfections in the input files. b.csv contains spurious leading/trailing spaces in multiple fields, which will likely mess up the results.

So this can be fixed by getting rid of the space before storing and comparing, but also storing the imperfections, so they are not altered in the process:

Code:

awk '{r=$0; for(i=1; i<=NF; i++) gsub(/^ +| +$/,x,$i)} NR==FNR{A[$1,$2]=$3; next} {print r, A[$1,$2]}' FS=, OFS=, a.csv b.csv

Whereas with a proper input file, something like this would suffice:

Code:

awk 'NR==FNR{A[$1,$2]=$3; next} {print $0, A[$1,$2]}' FS=, OFS=,  a.csv b.csv

These approaches can be further augmented by first testing if a field pair is present in the other file..