Sponsored Content
Top Forums UNIX for Dummies Questions & Answers how to join two files using "Join" command with one common field in this problem? Post 302623061 by mindfreak on Friday 13th of April 2012 05:36:20 AM
Old 04-13-2012
Question how to join two files using "Join" command with one common field in this problem?

file1:
Code:
Toronto:12439755:1076359:July 1, 1867:6
Quebec City:7560592:1542056:July 1, 1867:5
Halifax:938134:55284:July 1, 1867:4
Fredericton:751400:72908:July 1, 1867:3
Winnipeg:1170300:647797:July 15, 1870:7
Victoria:4168123:944735:July 20, 1871:10
Charlottetown:137900:5660:July 1, 1873:2
Regina:996194:651036:September 1, 1905:8
Edmonton:3183312:661848:September 1, 1905:9
St.John's:517000:405212:March 31, 1949:1
Yellowknife:42800:1346106:July 15, 1870:11
Whitehorse:31200:482443:June 13, 1898:12
Iqaluit:29300:2093190:April 1, 1999:13

file2:
Code:
Alberta:9:AB
British Comumbia:10:BC
Manitoba:7:MB
New Brunswick:3:NB
Newfoundland and Labrador:1:NL
Northwest Territories:11:NT
Nova Scotia:4:NS
Nunavut:13:NU
Ontario:6:ON
Prince Edward Island:2:PE
Quebec:5:QC
Saskatchewan:8:SK
Yukon Territories:12:YT


here,I want output like this way-->> Toronto:Ontario:6:ON
so,I tried using "join" command this way -->> join -t: -j1 5 -j2 2 -o 1.1 2.1 2.2 2.3 file1 file2
which gives me this output,
"join: file 1 is not in sorted order
join: file 2 is not in sorted order
Edmonton:Alberta:9:AB"

So,i dont understand here why it's giving me only one output line and error that files are not sorted.Do i need to sort the files before joining them?
please help me,I'm a newbie here!
Thanks.

Last edited by radoulov; 04-13-2012 at 06:54 AM.. Reason: Code tags!
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

join two lines when the second line contains "US DOLLAR"

I want to join two lines together when the second line contains "US DOLLAR". How to do that with sed? Original file: # cat testdata Sep. 24, 2005 Sep. 26, 2005 PHARMA PLUS DRUGMART 1149 $5.85 Sep. 25, 2005 Sep. 27, 2005 99 RESTAURANT #015 $11.00 US DOLLAR @ 1.203636 $13.24 ... (2 Replies)
Discussion started by: powah
2 Replies

2. Shell Programming and Scripting

Join file contents via common field

I have 2 files with a common parm - Jobname File 1 0507 1202 JOBA 0507 1302 JOBB 0507 1452 JOBC 0507 1552 JOBA 0507 1553 JOBA File2 JOBA abcdefg server4 JOBB defghij server22 JOBC vwxyz12 server55 I would like to take each line from File1 and match the jobname with the jobname... (8 Replies)
Discussion started by: Northerner
8 Replies

3. UNIX for Dummies Questions & Answers

Explanation of "total" field in "ls -l" command output

When I do a listing in one particular directory (ls -al) I get: total 43456 drwxrwxrwx 2 root root 4096 drwxrwxrwx 3 root root 4096 -rwxrwxr-x 1 nobody nobody 3701594 -rwxrwxr-x 1 nobody nobody 3108510 -rwxrwxr-x 1 nobody nobody 3070580 -rwxrwxr-x 1 nobody nobody 3099733 -rwxrwxr-x 1... (1 Reply)
Discussion started by: proactiveaditya
1 Replies

4. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

5. Shell Programming and Scripting

Remove ":" and join lines in outline file

I have a vim outliner file like this: Title title 2 :Testing now :testing 2 :testing 3 title 3 :testing :ttt :ttg Is there a way to use a script or command to remove... (7 Replies)
Discussion started by: jostber
7 Replies

6. Web Development

Perl join two files by "common" column

Hello; I am posting to get any help on my code that I have been struggling for some time. The project is to join two files each with 80k~180k rows. I want to merge them together by the shared common column. The problem of the shared column is partially matching, not exactly the same. File1:... (5 Replies)
Discussion started by: yifangt
5 Replies

7. Shell Programming and Scripting

join files based on a common field

Hi experts, Would you please help me with this? I have several files and I need to join the forth field of them based on the common first field. here's an example... first file: 280346 39.88 -75.08 547.8 280690 39.23 -74.83 538.7 280729 40.83 -75.08 499.2 280907 40.9 -74.4 507.8... (5 Replies)
Discussion started by: GoldenFire
5 Replies

8. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

9. Shell Programming and Scripting

Problem of Perl's "join" function

$ perl -e '@f=("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","1","911"); print join("\t",@f)."\n";' aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ... (5 Replies)
Discussion started by: carloszhang
5 Replies

10. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies
COMM(1) 							   User Commands							   COMM(1)

NAME
comm - compare two sorted files line by line SYNOPSIS
comm [OPTION]... FILE1 FILE2 DESCRIPTION
Compare sorted files FILE1 and FILE2 line by line. With no options, produce three-column output. Column one contains lines unique to FILE1, column two contains lines unique to FILE2, and column three contains lines common to both files. -1 suppress column 1 (lines unique to FILE1) -2 suppress column 2 (lines unique to FILE2) -3 suppress column 3 (lines that appear in both files) --check-order check that the input is correctly sorted, even if all input lines are pairable --nocheck-order do not check that the input is correctly sorted --output-delimiter=STR separate columns with STR --help display this help and exit --version output version information and exit Note, comparisons honor the rules specified by 'LC_COLLATE'. EXAMPLES
comm -12 file1 file2 Print only lines present in both file1 and file2. comm -3 file1 file2 Print lines in file1 not in file2, and vice versa. GNU coreutils online help: <http://www.gnu.org/software/coreutils/> Report comm translation bugs to <http://translationproject.org/team/> AUTHOR
Written by Richard M. Stallman and David MacKenzie. COPYRIGHT
Copyright (C) 2013 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>. This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. SEE ALSO
join(1), uniq(1) The full documentation for comm is maintained as a Texinfo manual. If the info and comm programs are properly installed at your site, the command info coreutils 'comm invocation' should give you access to the complete manual. GNU coreutils 8.22 June 2014 COMM(1)
All times are GMT -4. The time now is 04:56 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy