4

I'm trying to join two files together based on their common field, and output it to a file, using the join command.

File 1:

C01:Nancy:Jones:njones@abc.com 
C02:Barbara:Madison:bmadison@bcd.com 
C03:Tim:Adams:tadams@cde.com
C04:Sarah:Moore:smoore@def.com
C05:John:Polk:jpolk@efg.com
C06:Paula:Jacobs:pjacobs@fgh.com

File 2:

R001:07/04/15:123.45:C01
R002:12/20/15:167.50:C03
R003:03/14/16:298.00:C06
R005:09/15/16:36.50:C03
R005:11/27/16:58.00:C02
R006:02/28/17:72.98:C05 

Expected Output

C01:Nancy:Jones:njones@abc.com:R001:07/04/15:123.45
C02:Barbara:Madison:bmadison@bcd.com:R005:11/27/16:58.00
C03:Tim:Adams:tadams@cde.com:R002:12/20/15:167.50
C03:Tim:Adams:tadams@cde.com:R004:09/15/16:36.5
C05:John:Polk:jpolk@efg.com:R006:02/28/17:72.9
C06:Paula:Jacobs:pjacobs@fgh.com:R003:03/14/16:298.00

I've tried sorting File2 based on field 4 first and then outputting it to a new file

sort -t: -k 4 File2 > File22  

then joining

join -t: -1 1 -2 4 -o '1.1 1.2 1.3 1.4 2.1 2.2 2.3' File1 File22 > File 3  

but I always get a blank output file. I'm also not sure how to handle the omission of row 4 from File1 and the fact that C03 comes up twice in File2.

  • 2
    Nicely worded question: example input, expected output, demonstration of attempts to solve the problem yourself. +1 – cryptarch Jan 9 '19 at 20:57
  • Your code works fine here, it produces the expected output. Are you working with dos files or unix files ? – don_crissti Jan 9 '19 at 21:02
  • @don_crissti Not here. Since File 3 is not surrounded by quotation marks I get an error join: extra operand '3'; and an empty file named File is created. Furthermore, when I run the command without redirection, the records for C04 and C05 are missing from the output, whereas Jim has stated they should appear in the output. – cryptarch Jan 9 '19 at 21:06
  • @cryptarch - I assume File 3 is a typo and the OP meant File3 (or whatever the real name of the file is). There is no mention of an error in the post. As to the records missing, only C04 is missing from the output here - which is correct, according to the "Expected output" posted by the OP. As I said, I get the exact output that is expected. I'm not sure how to read the part that says "how to handle the omission of row 4 from File1 and the fact that C03 comes up twice in File2" maybe the OP can elaborate on that... – don_crissti Jan 9 '19 at 21:14
  • @don_crissti Yeah the File 3 was a typo meant File3. To clarify what you're saying, when you ran this using those two files you got the expected output? I created both files in the vi editor, i'll go back over them, maybe I mistyped something. – Jim Jan 9 '19 at 21:18
2

There are two things you need to do to get things working the way you want. First of all, by appending > File 3 to the end of the command, you are redirecting the output to File and also adding the extra argument 3 to the join command.

To explain why this happens, I recently (and coincidentally!) answered a similar question at Strange behaviour redirecting man pages. Basically, it has to do with Bash grammar, which you can read more about in man bash.

To fix that behaviour, you should either ensure there are no spaces in the output file, or you should escape the space, or you should surround the filename with quotation marks. Any of the following redirections would work:

... > File3
... > File\ 3
... > "File 3"

Now to address the missing records from the output. This is handled by the -a flag. From man join:

-a FILENUM
       also print unpairable lines from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2

Putting it all together, the following command works for me (I will use tee rather than a redirection to show the output):

$ join -t: -1 1 -2 4 -o '1.1 1.2 1.3 1.4 2.1 2.2 2.3' -a 1 File1 <(sort -t: -k4 File2) | tee "File 3"
C01:Nancy:Jones:njones@abc.com :R001:07/04/15:123.45
C02:Barbara:Madison:bmadison@bcd.com :R005:11/27/16:58.00
C03:Tim:Adams:tadams@cde.com:R002:12/20/15:167.50
C03:Tim:Adams:tadams@cde.com:R005:09/15/16:36.50
C04:Sarah:Moore:smoore@def.com:::
C05:John:Polk:jpolk@efg.com:::
C06:Paula:Jacobs:pjacobs@fgh.com:R003:03/14/16:298.00
| improve this answer | |
0


with Miller http://johnkerl.org/miller/doc/reference.html the command is

mlr --csv --implicit-csv-header --headerless-csv-output --fs ":" join --ul -l 4 -r 1 -j 4 --lp l --rp r -f file2.csv file1.csv

And you have

C01:R001:07/04/15:123.45:Nancy:Jones:njones@abc.com
C02:R005:11/27/16:58.00:Barbara:Madison:bmadison@bcd.com
C03:R002:12/20/15:167.50:Tim:Adams:tadams@cde.com
C03:R005:09/15/16:36.50:Tim:Adams:tadams@cde.com
C05:R006:02/28/17:72.98:John:Polk:jpolk@efg.com
C06:R003:03/14/16:298.00:Paula:Jacobs:pjacobs@fgh.com
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.