Sponsored Content
Top Forums Shell Programming and Scripting Linux - Join 2 csv files with common key Post 302965711 by RudiC on Tuesday 2nd of February 2016 09:12:30 AM
Old 02-02-2016
On top of what RavinderSingh13 said, spaces aren't used consistently as well, esp. in the end of lines. So, don't expect a perfect result, either. Howsoever, try
Code:
awk '   
FNR == NR       {T[$1,$2] = $0
                 sub ("^" $1 FS $2 FS $3, "", T[$1,$2])
                 next
                }
$1,$4 in T      {print $0  T[$1,$4]
                }
' FS=, file2 file1
NAME, FAV_FOOD, FAV_DRINK, ID, GENDER, COUNTRY
Bob, Fish, Coke, 1, M , 31, Ireland
Lisa, Rice, Water, 2, F , 22, UK
Jenny, Noodle, Tea, 3, F , 18, France
Ken, Pizza, Coffee, 4, M , 12, Iceland
Lisa, Cake, Milk, 2, F , 22, UK
Ken, Sandwich, Juice, 4, M , 12, Iceland

These 2 Users Gave Thanks to RudiC For This Post:
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join 3 files using key column in a mapping file

I'm new of UNIX shell scripting. I'm recently generating a excel report in UNIX(file with delimiter is fine). How should I make a script to do it? 1 file to join comes from output of one UNIX command, the second from another UNIX command, and third from a database query. The key columes of all... (7 Replies)
Discussion started by: bigsmile
7 Replies

2. 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

3. Shell Programming and Scripting

Join multiple files based on 1 common column

I have n files (for ex:64 files) with one similar column. Is it possible to combine them all based on that column ? file1 ax100 20 30 40 ax200 22 33 44 file2 ax100 10 20 40 ax200 12 13 44 file2 ax100 0 0 4 ax200 2 3 4 (9 Replies)
Discussion started by: quincyjones
9 Replies

4. 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

5. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

file1: 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... (2 Replies)
Discussion started by: mindfreak
2 Replies

6. 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

7. Shell Programming and Scripting

Join two files with common and range identifiers

I have a problem joining two files. The first file abc.txt has 10k lines and has lots of fields but two fields fff1 and ppp1 to merge by. The second file xyz.txt is a master file with 1k lines and lots of fields but three fields to merge by fff1; rrr1 and qqq1. The two files need to be merged... (9 Replies)
Discussion started by: cfiles2012
9 Replies

8. UNIX for Dummies Questions & Answers

How to join 2 .txt files based on a common column?

Hi all, I'm trying to join two .txt file tab delimitated based on a common column. File 1 transcript_id gene_id length effective_length expected_count TPM FPKM IsoPct comp1000201_c0_seq1 comp1000201_c0 337 183.51 0.00 0.00 0.00 0.00 comp1000297_c0_seq1 ... (1 Reply)
Discussion started by: alisrpp
1 Replies

9. Shell Programming and Scripting

Merging files with common IDs without JOIN

Hi, I am trying to merge information across 2 files. The first file is a "master" file, with all IDS. File 2 contains a subset of IDs of those in File 1. I would like to match up individuals in File 1 and File 2, and add information in File 2 to that of File 1 if they appear. However, if an... (3 Replies)
Discussion started by: hubleo
3 Replies

10. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies
DIFF3(1)						      General Commands Manual							  DIFF3(1)

NAME
diff3 - 3-way differential file comparison SYNOPSIS
diff3 [ -exEX3 ] file1 file2 file3 DESCRIPTION
Diff3 compares three versions of a file, and publishes disagreeing ranges of text flagged with these codes: ==== all three files differ ====1 file1 is different ====2 file2 is different ====3 file3 is different The type of change suffered in converting a given range of a given file to some other is indicated in one of these ways: f : n1 a Text is to be appended after line number n1 in file f, where f = 1, 2, or 3. f : n1 , n2 c Text is to be changed in the range line n1 to line n2. If n1 = n2, the range may be abbreviated to n1. The original contents of the range follows immediately after a c indication. When the contents of two files are identical, the contents of the lower-numbered file is suppressed. Under the -e option, diff3 publishes a script for the editor ed that will incorporate into file1 all changes between file2 and file3, i.e. the changes that normally would be flagged ==== and ====3. Option -x (-3) produces a script to incorporate only changes flagged ==== (====3). The following command will apply the resulting script to `file1'. (cat script; echo '1,$p') | ed - file1 The -E and -X are similar to -e and -x, respectively, but treat overlapping changes (i.e., changes that would be flagged with ==== in the normal listing) differently. The overlapping lines from both files will be inserted by the edit script, bracketed by "<<<<<<" and ">>>>>>" lines. For example, suppose lines 7-8 are changed in both file1 and file2. Applying the edit script generated by the command "diff3 -E file1 file2 file3" to file1 results in the file: lines 1-6 of file1 <<<<<<< file1 lines 7-8 of file1 ======= lines 7-8 of file3 >>>>>>> file3 rest of file1 The -E option is used by RCS merge(1) to insure that overlapping changes in the merged files are preserved and brought to someone's atten- tion. FILES
/tmp/d3????? /usr/libexec/diff3 SEE ALSO
diff(1) BUGS
Text lines that consist of a single `.' will defeat -e. 7th Edition October 21, 1996 DIFF3(1)
All times are GMT -4. The time now is 11:11 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy