0

I am experiencing problems trying to join the following two files using GNU join v8.22:

FILE1.TXT

KEY;COLUMN1;COLUMN2;COLUMN3;COLUMN4
A02046464;9.1.3;1.1.3;;A02054062
A02047835;9.1.3;1.2;;A12345678
A02047843;9.1.3;1.1.2;20121016;
A02048288;9.2.8.2.7;4.2.3.7;;

FILE2.TXT

KEY;COLUMN1;COLUMN2
A02046464;;
A02047835;;
A02047843;9.1.3;1.1.2
A02048288;9.2.8.2.7;4.2.3.7

Using the following command

LC_ALL=C join -j 1 -a 1 -a 2 -t ';' --header FILE1.TXT FILE2.TXT

But the output seems to be broken, as if the columns from FILE2.TXT were overwritting those from FILE1.TXT.

;COLUMN1;COLUMN2MN2;COLUMN3;COLUMN4
;;2046464;9.1.3;1.1.3;;A02054062
;;2047835;9.1.3;1.2;;A12345678
;9.1.3;1.1.21.3;1.1.2;20121016;
;9.2.8.2.7;4.2.3.77;4.2.3.7;;

Notice that the header and data seem to be overwritten by the 2 columns of FILE2. Also, the key column isn't shown at the output.

If I try to run the command using the -o FORMAT option explicitly (LC_ALL=C join -j 1 -a 1 -a 2 -t ';' --header -o 0,1.2,1.3,1.4,1.5,2.2,2.3 FILE1.TXT FILE2.TXT), the output is the same.

Interestingly, this only happens when I add fields from FILE2.TXT to the output. If I only show those from FILE1.TXT, it seems to work:

LC_ALL=C join -j 1 -a 1 -a 2 -t ';'  --header -o 0,1.2,1.3,1.4,1.5 FILE1.TXT FILE2.TXT
KEY;COLUMN1;COLUMN2;COLUMN3;COLUMN4
A02046464;9.1.3;1.1.3;;A02054062
A02047835;9.1.3;1.2;;A12345678
A02047843;9.1.3;1.1.2;20121016;
A02048288;9.2.8.2.7;4.2.3.7;;

But as soon as I add one field from FILE2.TXT, the key column disappears and this new column overwrite those from FILE1.TXT:

LC_ALL=C join -j 1 -a 1 -a 2 -t ';' --header -o 0,1.2,1.3,1.4,1.5,2.2 FILE1.TXT FILE2.TXT
;COLUMN1MN1;COLUMN2;COLUMN3;COLUMN4
;02046464;9.1.3;1.1.3;;A02054062
;02047835;9.1.3;1.2;;A12345678
;9.1.3843;9.1.3;1.1.2;20121016;
;9.2.8.2.79.2.8.2.7;4.2.3.7;;

Could someone provide me some help? Thanks!

lopezishere
  • 1
  • 2
  • You don't have `--header` added in with the options your calling – stefan_aus_hannover Sep 13 '21 at 15:48
  • Thanks for the comment. I forgot to put it in the question, but all the commands have been executed with --header in it, with the same results. I will update the question. – lopezishere Sep 13 '21 at 15:53
  • Very often "mysterious" results from `join` turn out to be down to something not being sorted in accordance with its fickle expectations. Does anything change e.g. if you move the delimiter option up before the other options? – tripleee Sep 13 '21 at 18:15
  • I can't repro either way, though: https://ideone.com/F7elkC - do your input files have DOS line endings by any chance? – tripleee Sep 13 '21 at 19:01
  • ... With that, I do get (some kind of) repro: https://ideone.com/DqhD1O – tripleee Sep 13 '21 at 19:06
  • Thnk you @tripleee ! Your last comment URL had the solution. I had windows-like line endings in my files. Using sed I converted it to linux-style and it worked flawlessly! – lopezishere Sep 14 '21 at 08:16

0 Answers0