Join

Shell Programming and Scripting
1

Hi,
need some help in joining please,
FILE 1:
-------
1|05/20/2009|
2|04/21/2009|
2|03/21/2009|
FILE 2:
-------
1|Michel|Hawkins|05/10/2009|
1|Michel|Hawkins|03/10/2007|
2|Krish|Lander|09/10/2005|
FILE 3:
-------
1|M|32|03/22/2009|
2|M|42|04/22/2009|
2|M|41|03/10/2008|

I want to join these sorted files based on the first field of the first file, there can be multiples
in each file/s...Join should pick the top record matches with the first field of first file and pick
only certain fields from following files...
for example the joined file for above should look alike following...
1|Michel|Hawkins|M|32|
2|Krish|Lander|M|42|
any help is greatly appreciated...Thanks

2

I will let you shorten this -

gawk -F "|" 'FILENAME=="1.txt"{!a[$1]++;next}
                FILENAME=="2.txt"{if($1 in b)next  
                     else b[$1]=$2"|"$3;next}  
            FILENAME=="3.txt"{if($1 in d)next 
               else d[$1]=$2"|"$3;next}     
    END{for(i in a)print i,b,d}' OFS="|" 1.txt 2.txt 3.txt

-Devaraj Takhellambam

3

Hi,
I would like to Join the Below mwntioned TWO Files.
File 1.txt
---------
99|Table1|00|5
99|Table2|00|10
99|Table3|00|15
99|Table1|04|7

File 2.txt
---------
99|Table1|00|INF1
99|Table2|00|INF2
99|Table3|00|INF3
99|Table1|04|INF4
99|Table4|04|INF5
99|Table2|04|INF6

Expected Output:
------------------
99|Table1|00|INF1|5
99|Table2|00|INF2|10
99|Table3|00|INF3|15
99|Table1|04|INF4|7
99|Table4|04|INF5|0
99|Table2|04|INF6|0
99|Table2|00|INF6|10

Basically the First 3 Fileds of File 1.txt and 2.txt needs to be mathced, if it matches then pick the 4th filed from the 1.txt and append it to the 2.txt file record.

Can any one help me?

4

if you have Python

#!/usr/bin/env python
file1={}
for line in open("file1"):
    line=line.strip().split("|")    
    file1['|'.join(line[:3])] = line[-1]
for line in open("file2"):
    line=line.strip().split("|") 
    one='|'.join(line[:3])
    if file1.has_key(one):   
        print "%s|%s|%s" %(one,line[-1],file1[one])
    else:
        print "%s|%s|%s" %(one,line[-1],"0")

output

# ./test.py
99|Table1|00|INF1|5
99|Table2|00|INF2|10
99|Table3|00|INF3|15
99|Table1|04|INF4|7
99|Table4|04|INF5|0
99|Table2|04|INF6|0
5

Hi,
Thanks for providing the solution,Could u please explain how it works........

6

which solution you need explaining?

7

Hi,
I need the below mentioned Solution explanation.

#!/usr/bin/env python
file1={}
for line in open("file1"):
line=line.strip().split("|")
file1['|'.join(line[:3])] = line[-1]
for line in open("file2"):
line=line.strip().split("|")
one='|'.join(line[:3])
if file1.has_key(one):
print "%s|%s|%s" %(one,line[-1],file1[one])
else:
print "%s|%s|%s" %(one,line[-1],"0")

8 
#!/usr/bin/env python
file1={} # store file1 contents in a dictionary
for line in open("file1"):
    line=line.strip().split("|")     # split the line on "|"
    file1['|'.join(line[:3])] = line[-1] #  store the first 3 elements as the key and last element as the value

for line in open("file2"): #now iterate file 2
    line=line.strip().split("|")  # you should know what his does
    one='|'.join(line[:3]) # get the first 3 elements together
    if file1.has_key(one):    #check against file1 dictionary using the key
        print "%s|%s|%s" %(one,line[-1],file1[one]) #do the printing
    else:
        print "%s|%s|%s" %(one,line[-1],"0")

please review Python tutorial (see my sig) to see how Python is used.