2

I have two different files and I'd like to do a merge of their information using the first column.

File1.txt

A,info1,info2
234,info3,info4
CD,info5,info6

File2.txt

234,ccc,bb
CD,aaa,dd

Expected output.csv

A,info1,info2,,
234,info3,info4,ccc,bb
CD,info5,info6,aaa,dd

I tried with awk (not my script) join and grep but I didn't obtain the desired result.

awk -F "," 'FNR==NR {h[$1] = $2;next} BEGIN{ OFS = "\t"} {print $0,$2?h[$1]:"0"}' file1.txt prova2.txt

and

join -a 1 <(sort file1.txt) <( sort file2.txt) > output.csv

Could someone help me please?

1 Answer 1

5

if the number of fields in both files are the same then you can use -o auto to fill-up the number of fields in each line based on the first line of each file (by default it fills the missing fields with the value of -e option which by default it's space character but you can change it to any string you want);

$ join -t, -a1 -o auto <(sort file1) <(sort file2)
234,info3,info4,ccc,bb
A,info1,info2,,
CD,info5,info6,aaa,dd

we also added -t, to specify the field separators for the input &ouptut files.
if you want to add lines which are only exist in file2, add -a2 to the command.

above command is the shortened version of below command which we explicitly saying which fields to output:

join -t, -a1 -o0,1.2,1.3,2.2,2.3 <(sort file1) <(sort file2)

-o #.k prints the kth field from the file number #.
-o 0 here outputs the un-pairable lines.


Or using awk:

awk 'BEGIN{ FS=OFS=","; na="" }
              { key=$1; sub(/[^,]*,/, "") }
NR==FNR       { file1[key]=$0; next }
(key in file1){ print key, file1[key], $0; delete file1[key] }

END{ for(key in file1) print key, file1[key], na, na }' file1 file2

In the { key=$1; sub(/[^,]*,/, "") } action, we take a backup from the first column then with sub() we remove that column by striping line upto the first comma character, so the remaining content will be the value for that key for the later uses.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .