Solved

Cygwin / Bash - Join command not working as expected

Posted on 2010-09-10
4
888 Views
Last Modified: 2013-11-15
I have two CSV files I need to combine.
Both of them share a key column (ID), but each file has unique columns otherwise.
Not all of the IDs in the first file have matches in the second file.  

I would like the output to include ALL rows from the first file whether they match or not.  The rows that did have a match need the additional columns appended.

This seems to be a fairly common question floating around on the internet. The most common solution I found was to use 'join'.  However, it isn't behaving as I was expecting.



$ ls
test1.csv  test2.csv

$ cat test1.csv
22,Charles,Stross,388
33,William,Shakespeare,389
44,Michael,Jackson,385
55,Tom,Green,103
58,Edgar,Poe,22
66,John,Doe,938
99,Jane,Doe,2388

$ cat test2.csv
22,Stories,10/27/1983
33,Plays,12/31/1980
44,Music,7/23/1937

$ join -a 1 -t , test1.csv test2.csv
,Stories,10/27/198388
,Plays,12/31/1980peare,389
,Music,7/23/1937on,385
55,Tom,Green,103
58,Edgar,Poe,22
66,John,Doe,938
99,Jane,Doe,2388


The output seems to be dropping the "key" value from any rows that match, and I have no idea why the text is overlapping (for example, the Date on output Line 3 is overwriting the name).

Any suggestions?  
0
Comment
Question by:Proots
  • 2
  • 2
4 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 33651437
join -a 1 -t , test1.csv test2.csv
22,Charles,Stross,388,Stories,10/27/1983
33,William,Shakespeare,389,Plays,12/31/1980
44,Michael,Jackson,385,Music,7/23/1937
55,Tom,Green,103
58,Edgar,Poe,22
66,John,Doe,938
99,Jane,Doe,2388

is it possible you have an extra \r character at the end of the lines in test1.csv?
what do you see with
join -a 1 -t , test1.csv test2.csv | cat -v
0
 
LVL 2

Author Comment

by:Proots
ID: 33652073
$ join -a 1 -t , test1.csv test2.csv
,Stories,10/27/198388
,Plays,12/31/1980peare,389
,Music,7/23/1937on,385
55,Tom,Green,103
58,Edgar,Poe,22
66,John,Doe,938
99,Jane,Doe,2388


$ join -a 1 -t , test1.csv test2.csv | cat -v
22,Charles,Stross,388^M,Stories,10/27/1983^M
33,William,Shakespeare,389^M,Plays,12/31/1980^M
44,Michael,Jackson,385^M,Music,7/23/1937
55,Tom,Green,103^M
58,Edgar,Poe,22^M
66,John,Doe,938^M
99,Jane,Doe,2388
0
 
LVL 84

Accepted Solution

by:
ozo earned 50 total points
ID: 33652098
So that's why the text is overlapping.
you may be able to remove it with
tr -d '\r'
0
 
LVL 2

Author Closing Comment

by:Proots
ID: 33653046
Ozo, you are a saint!
It works now; here is the entirety of what I had to do:

cat test1.csv | tr -d '\r' > testa.csv
cat test2.csv | tr -d '\r' > testb.csv
join -a 1 -t , testa.csv testb.csv
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now