How to print value with a common key from two files but in a specific column in awk

Question

I have a main file bla.tsv (FS=\t):

id hub fil plop
34  ji ji
54  jo jo
46  ja ja

And a info file hub.csv (FS=,):

34,fuse
46,fose

And I want to print the info of hub.csv into the column hub of bla.tsv like this:

id hub fil plop
34 fuse ji ji
54  jo jo
46 fose ja ja

I managed to print the correct info in the correct line but at the column after the last one:

awk -v OFS="\t" 'NR==FNR{a[$1]=$2;next}{print $0,a[$1]}' FS="," hub.csv FS="\t" bla.tsv > result.tsv

How can I fill the second column of bla.tsv with the value found in hub.csv (if any)? Note that the structure of bla.tsv is already "correct", i.e. there is an empty second column in all lines after the header (two consecutive \t after the ID).

Answer 1

Since the format of your bla.tsv is already correct with an empty second column, there is only a minimal modification to your approach necessary:

awk -v OFS="\t" 'NR==FNR{a[$1]=$2;next} ($1 in a){$2=a[$1]} 1' FS="," hub.csv FS="\t" bla.tsv > result.tsv

This will, when processing bla.tsv, check if the ID has a matching hub value in the array a. If so, it sets the second field to that value, an in general, print the current line (including all modifications made).