Merge 2 csv files with awk

Shell Programming and Scripting
1

I have 2 files pipe delimted and want to merge them based on a key

e.g file 1

123$aaa$yyy$zzz
345$xab$yzy$zyz
456$sss$ttt$foo
799$aaa$ggg$dee

file 2

123$hhh
345$ddd
456$xxx
888$zzz

so if the key is the first field, and the result should be the common key between file 1 and 2

i.e. result will be

123$aaa$yyy$hhh$zzz
345$xab$yzy$ddd$zyz
456$sss$ttt$xxx$foo

I've found this code with awk:

awk 'NR==FNR{x[$1]=$2;next}$1 in x&&$2=x[$1]' OFS="$" FS="$" file2 file1

but I can't insert at the right place :confused:

Please help me :b:

2

I asume there is a typo or I misunderstood the logic. From your description the output should look like:

$> join -t\$ -1 1 -2 1 -o 1.1,2.2,1.2,1.3,1.4 f1 f2
123$hhh$xxx$yyy$zzz
345$ddd$xab$yzy$zyz
456$xxx$sss$ttt$foo

In your output example, the row starting with 345 suddenly only has 4 fields instead of 5 but I can't see by what criteria this happens. For the line starting with 456, I can't see where the rule

is still correct. There is suddenly ddd from the row before in it and the order doesn't seem to be correct anymore.

3

The logic is : the second column of the second file must be inserted before the last element of the first file each time the key correspond.

Sorry for my unperfection :o

4

So this will work for you?

$> join -t\$ -1 1 -2 1 -o 1.1,1.2,1.3,2.2,1.4 f1 f2
123$aaa$yyy$hhh$zzz
345$xab$yzy$ddd$zyz
456$sss$ttt$xxx$foo
5

no ... nothing is displayed ...
I'm under HP-Solaris ... This should be the same syntax, isn't it ?

If all my elements are like this (in both files):

"123"$"xxx"$"yyy"$"zzz"
"345"$"xab"$"yzy"$"zyz"
"456"$"sss"$"ttt"$foo"

Should I use

-t\"$"

or just

-t\$

?

6

This does not really make a difference - I tried it on my Debian Linux box and on an AIX box:

$> join -t"$" -1 1 -2 1 -o 1.1,1.2,1.3,2.2,1.4 f1 f2
"123"$"aaa"$"yyy"$"hhh"$"zzz"
"345"$"xab"$"yzy"$"ddd"$"zyz"
"456"$"sss"$"ttt"$"xxx"$"foo"

And AIX is usually more picky.
The join command should be clear so you can reproduce or alter it yourself? -1 defines the key in file1 (f1) and -2 they key to take from the 2nd file. The digits following -o like 1.3 means from 1st file to display 3rd field.

7

Yo,

The prob comes from the ".

If I remove it from files it's working ....

---------- Post updated 16-12-10 at 04:31 AM ---------- Previous update was 15-12-10 at 11:34 AM ----------

ok, you give up :wink:

Thanks a lot for your precious help :b::b::b::b:

---------- Post updated at 04:32 AM ---------- Previous update was at 04:31 AM ----------

Last question about join command.

Is it possible to join with several column from both files ? :smiley: