Use the Unix JOIN command to merge two files

Question

This does not work, as I expect, despite all the research. Something is missing me ...

File 1 ...

# cat file1.csv 1 123 JohnDoe 1 456 BobDylan 1 789 BillyJean 

File 2 ...

 # cat file2.csv 111 123 DaddyDoe 222 456 DaddyDylan 666 777 Stranger 555 789 DaddyJean 444 888 Stranger 333 999 Stranger 

I am trying to join both second fields. When I do a left outer join and include only the fields from the first file, everything seems to be a dandy.

 # join -1 2 -2 2 -a 1 -o 1.2 1.3 file1.csv file2.csv 123 JohnDoe 456 BobDylan 789 BillyJean 

But as soon as I include the field from the second file, everything goes wrong.

 # join -1 2 -2 2 -a 1 -o 1.2 1.3 2.3 file1.csv file2.csv DaddyDoeoe DaddyDylann 789 BillyJean DaddyJean 

The last line looks perfect! What's up with others? Any ideas? Thanks in advance!

EDIT: Here is my attempt with actual CSV.

 # cat file1.csv 1,123,JohnDoe 1,456,BobDylan 1,789,BillyJean # cat file2.csv 111,123,DaddyDoe 222,456,DaddyDylan 666,777,Stranger 555,789,DaddyJean 444,888,Stranger 333,999,Stranger # join -t, -1 2 -2 2 -a 1 -o 1.2 1.3 2.3 file1.csv file2.csv ,DaddyDoeoe ,DaddyDylann 789,BillyJean,DaddyJean 

Answer 1

You used the -a option.

-a file_number

In addition to the default output, create a line for each unrecoverable line in file_number.

In addition, the odd rewrite behavior indicates that you have built-in carriages ( \r ). I would carefully study these fighters using cat -v or a text editor that does not try to be smart about Windows files.

Answer 2

Use the correct field separator in your command.

When I changed your data to true csv and used

 join -t, -1 2 -2 2 -a 1 -o 1.2 1.3 2.3 file1.csv file2.csv # ---^^^ 

I got

 123,JohnDoe,DaddyDoe 456,BobDylan,DaddyDylan 789,BillyJean,DaddyJean 

Hope this helps.

Answer 3

If you do this command line, why not use insert? paste -d, file1 file2 → file3

-smooth -d is a delimiter