UNIX one line cmd join 2 sets of data from 2 files


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers UNIX one line cmd join 2 sets of data from 2 files
# 1  
Old 08-08-2013
Wrench UNIX one line cmd join 2 sets of data from 2 files

Hi all,
This is my first and undoubtedly many posts to come. I'm new to using unix and would like a hand with this problem I have. What i'm trying to do is match 2 sets of data from 2 files and put result into file 3. Sounds simply but there is a catch, the match is a "partial field" match, if this is possible? The data is sorted by file 1 via date field only. I have space delimited the fields for easier access, used SED to get rid of | and white spaces. There are no headings for the data sets either.

FYI: Using AIX unix

Code:
 
File 1 (Roughly 3 columns with 7000 rows)

ACEI234-123 34553455 20/07/2013
SIER424-012 32434342 19/07/2013
DEFG672-032 56344333 18/07/2013
SWQA744-932 24465394 15/07/2013
SIER424-009 34345535 12/07/2013
FGHJ722-099 45945834 12/07/2013
ACEI234-123 34355352 10/07/2013

Code:
File 2 (2 columns with 100 rows)

828 GHF
722 JKE
456 TRE
672 ERT
826 OKT
424 KLE
234 PRW
424 TWI
672 ERT

Code:
Desired output file
ACEI234-123 PRW 34553455 20/07/2013
SIER424-012 KLE 32434342 19/07/2013
DEFG672-032 ERT 56344333 18/07/2013
SWQA744-932     24465394 15/07/2013
SIER424-009 KLE 34345535 12/07/2013
FGHJ722-099 JKE 45945834 12/07/2013
ACEI234-123 PRW 34355352 10/07/2013

As you can see, I'm trying to match file 2 to file 1 and input the file 2, field 2 to a partial match in file 1, field 1 into my desired file 3

EG (File 1, field 1 ACEI234-123 is a match to File 2, Field 1 234 PRW)
I would like non matches to be also left blank and with equal white spaces in between. Eventually I would have to convert to csv for excel.

I hope I've explained clearly enough!

Thanks
# 2  
Old 08-08-2013
Hello tugar,

I'm going to assume that the values in column 1 of file 2 are unique.

There might be a way with the paste command, but I've not used that much myself. I'm sure an awk will be more than capable, but I have a shell option for you. This might be a bit slow, but try:-
Code:
#!/bin/ksh
while read f1_c1 f1_rest
do
   index="${f1_c1%-*}"               # Remove section after the hyphen
   index="${index#${line%%???}"    # Remove first 3 characters
   grep "^$index " file2 | read ind ref
   echo "$f1_c1 $ref $f1_rest"
done <file1 >file3



I hope that this helps. Let us know if this is acceptable or misses the point.

Robin
Liverpool/Blackburn
UK
# 3  
Old 08-08-2013
Try also
Code:
 awk 'NR==FNR {L[$1]=$2; next} {T=substr ($1, 5, 3); if (T in L) $1=$1" "L[T]; else $1=$1"    "} 1' file2 file1
ACEI234-123 PRW 34553455 20/07/2013
SIER424-012 TWI 32434342 19/07/2013
DEFG672-032 ERT 56344333 18/07/2013
SWQA744-932     24465394 15/07/2013
SIER424-009 TWI 34345535 12/07/2013
FGHJ722-099 JKE 45945834 12/07/2013
ACEI234-123 PRW 34355352 10/07/2013

 
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join the line on delimiter using sed/awk in UNIX

I've input as , abcd| ef 123456| 78| 90 Desired output as, abcdef 1234567890 Anyone please give the solution. (5 Replies)
Discussion started by: jinixvimal
5 Replies

2. Shell Programming and Scripting

UNIX cmd -find empty files in folder else sleep for 8hrs

Hello, I am trying to write a unix cmd , that if files in folder /path/FTP are all zero kb or empty then good to go, if not empty then sleep for 8 hrs. Following cmd list me the files which are not empty, But when I am incorporating IF ELSE cmd fails find /path/FTP. -type f -exec wc -l {}... (6 Replies)
Discussion started by: bluestarmoon
6 Replies

3. Shell Programming and Scripting

Unix cmd prompt how to get old cmd run?

Hi, I am using SunOS I want to serch my previous command from unix prompt (like on AIX we can search by ESC -k) how to get in SunOs urgent help require. (10 Replies)
Discussion started by: RahulJoshi
10 Replies

4. Shell Programming and Scripting

join lines on line break in files

i had a file where lines appear to be broken when they shouldn't eg Line 1. kerl abc sdskd sdsjkdlsd sdsdksd \ Line 2. ksdkks sdnjs djsdjsd i can do a shift join to combine the lines but i there are plenty of files with this issue Line 1. kerl abc sdskd sdsjkdlsd sdsdksd ksdkks sdnjs... (6 Replies)
Discussion started by: mad_man12
6 Replies

5. Shell Programming and Scripting

Finding Overlap between two sets of data

Hi everyone, I posted this earlier, but the idea changed since then and I figured it would make more sense if I repost with a clearer idea in hopes someone can help me out. I have two lists of data in file1 and file 2 file1 (tab separated - column1 column2 column 3) 1 91625106 ... (1 Reply)
Discussion started by: labrazil
1 Replies

6. Shell Programming and Scripting

Inserting Lines between data sets using SED?

Hello all and thanks in advance! What I'm looking to do is insert a blank line, anytime the first 9 characters of a given line don't match the first 9 characters of the previous line. i.e. Convert the data set 1 45 64 89 1 89 69 235 2 89 234 67 2 56 90... (1 Reply)
Discussion started by: selkirk
1 Replies

7. OS X (Apple)

Command line tool to join multiple .wmv files?

I need a simple command line executable that allows me to join many wmv files into one output wmv file, preferrably in a simple way like this: wmvjoin file1.wmv file2.wmv .... > outputfile.wmv So what I want is the wmv-equivalent of mpgtx I cannot find it on internet. Thanks. (2 Replies)
Discussion started by: karman
2 Replies

8. Virtualization and Cloud Computing

Clouds (Partially Order Sets) - Streams (Linearly Ordered Sets) - Part 2

timbass Sat, 28 Jul 2007 10:07:53 +0000 Originally posted in Yahoo! CEP-Interest Here is my follow-up note on posets (partially ordered sets) and tosets (totally or linearly ordered sets) as background set theory for event processing, and in particular CEP and ESP. In my last note, we... (0 Replies)
Discussion started by: Linux Bot
0 Replies

9. UNIX for Dummies Questions & Answers

How to join flat files together under unix

Hi, I have 7 big flat files, each contains 24 million records which have been sorted by the first field delimitered by Ctrl B (002). I want to join them together side by side, eg. File A: 1^Ba^Bb 2^Bx^By .... File B: 1^Bc^Bd 2^Bm^Bn .... After merged, it should look like :... (7 Replies)
Discussion started by: xli
7 Replies
Login or Register to Ask a Question