Join 3 files using key column in a mapping file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join 3 files using key column in a mapping file
# 1  
Old 06-30-2009
Question 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 these 3 files come from a mapping file. It's basically as following:

MappingFile:
===========
job1 a_name1
job2 c_name2
job3 b_name3
job4 e_name4

file1_toJOIN:
============
b_name3 12:00:03 15:00:09
e_name4 10:30:00 11:00:00
c_name2 09:40:00 10:12:00
a_name1 22:00:00 23:00:00

file2_toJOIN:
=============
job2 tom 22
job1 dan 18
job4 jim 25
job3 kim 20

the spreadsheet format to generate:
===========================
job1 22:00:00 23:00:00 01:00:00 dan 18
......

Appreciate your help!
# 2  
Old 07-01-2009
Try this!

try this

for i in `cat map | awk 'BEGIN { FS = " " }; { print $1}'`
do
mapkey1=$i
mapkey2=`cat map | grep $mapkey1 `
mapkey2=`echo $mapkey2 | awk 'BEGIN { FS = " " }; { print $2}'`
file1key=`cat file1 | grep $mapkey2`
file1key=`echo $file1key | awk 'BEGIN { FS = " " }; { print $2,$3}'`
file2key=`cat file2 | grep $mapkey1`
file2key=`echo $file2key | awk 'BEGIN { FS = " " }; { print $2,$3}'`
echo $i $file1key $file2key
done


map - your map file
file1 - your file1_toJOIN
file2 - file2_toJOIN:

The o/p i got is :
job1 22:00:00 23:00:00 dan 18
job2 09:40:00 10:12:00 tom 22
job3 12:00:03 15:00:09 kim 20
job4 10:30:00 11:00:00 jim 25
# 3  
Old 07-01-2009
Code:
nawk 'NR==FNR{
arr[$1]=$2
brr[$2]=$1
}
NR!=FNR{
	if (arr[$1]=="")
		brr[$1]=sprintf("%s %s",$2,$3)
	else
		print $1" "brr[arr[$1]]" "$2" "$3
}' map file1 file2

# 4  
Old 07-02-2009
Bug Join 3 files using key colume in a mapping file

Appreciate both of you.

I've tried the first script. It works perfectly. I feel a bit difficult to understand array, as a shell script beginner. But I'll try tomorrow, as it looks really compact. I'd like to use in the future.
# 5  
Old 07-03-2009
With comments for better understanding

#Opening the file
for i in `cat map | awk 'BEGIN { FS = " " }; { print $1}'`
do

#Column1 in map file is key to file2
mapkey1=$i

mapkey2=`cat map | grep $mapkey1 `

#Column2 in map file is key to file1

mapkey2=`echo $mapkey2 | awk 'BEGIN { FS = " " }; { print $2}'`

#Selecting values from file1 based on mapkey2 from map file
file1key=`cat file1 | grep $mapkey2`
file1key=`echo $file1key | awk 'BEGIN { FS = " " }; { print $2,$3}'`

#Selecting values from file2 based on mapkey1 from map file
file2key=`cat file2 | grep $mapkey1`
file2key=`echo $file2key | awk 'BEGIN { FS = " " }; { print $2,$3}'`

#Diplay the results
echo $i $file1key $file2key
done
# 6  
Old 07-04-2009
Thank you, Prasperl.

I used ksh, for some reason, it gave me awk syntex error. After I removed field seperator, it let me through. Do you know the reason?
# 7  
Old 07-05-2009
Could you please post the code u used and the error message?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join and merge multiple files with duplicate key and fill void columns

Join and merge multiple files with duplicate key and fill void columns Hi guys, I have many files that I want to merge: file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: (5 Replies)
Discussion started by: yjacknewton
5 Replies

2. Shell Programming and Scripting

Linux - Join 2 csv files with common key

Hi, I am trying to join 2 csv files, to create a 3rd output file with the joined data. Below is an example of my Input Data: Input File 1 NAME, FAV_FOOD, FAV_DRINK, ID, GENDER Bob, Fish, Coke, 1, M Lisa, Rice, Water, 2, F Jenny, Noodle, Tea, 3, F Ken, Pizza, Coffee, 4, M Lisa,... (7 Replies)
Discussion started by: RichZR
7 Replies

3. Shell Programming and Scripting

Join 2nd column of multiple files

Dear All, I have many files formatted like this: file1.txt: 1/2-SBSRNA4 18 A1BG 3 A1BG-AS1 6 A1CF 0 A2LD1 1 A2M 1160 file2.txt 1/2-SBSRNA4 53 A1BG 1 A1BG-AS1 7 A1CF 0 A2LD1 3 A2M 2780 (5 Replies)
Discussion started by: paolo.kunder
5 Replies

4. UNIX for Dummies Questions & Answers

Join 2 files based on certain column

I have file input1.txt 11103|11|OTTAWA|City|AA|CAR|0|0|1|-1|0|8526|2014-09-07 23:00:14 11103|11|OTTAWA|City|BB|TRAIN|0|0|2|-2|6|6359|2014-09-07 23:00:14 11104|11|CANADA|City|CC|CAR|0|0|2|-2|0|5947|2014-09-07 23:00:14 11104|11|CANADA|City|DD|TRAIN|0|0|2|-2|1|4523|2014-09-07 23:00:14... (5 Replies)
Discussion started by: radius
5 Replies

5. UNIX for Dummies Questions & Answers

Join files by second column

I have file input file1 1/1/2013 A 553.0763397 96 16582 1/1/2013 B 459.8333588 195 11992 1/2/2013 A 844.2973022 306 19555 1/2/2013 B 833.9300537 457 20165 1/3/2013 A 563.6917419 396 13879 1/3/2013 B 632.0749969 169 ... (1 Reply)
Discussion started by: radius
1 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 based on one column

Hi All, I am trying to join to files based on one common column. Cat File1 ID HID Ab_1 23 Cd 45 df 22 Vv 33 Cat File2 ID pval Ab_1 0.3 Cd 10 Vv 0.0444 (3 Replies)
Discussion started by: newpro
3 Replies

8. Shell Programming and Scripting

Join 3 or more files using matching column

Dear Forum, Full title of the topic would be: "Join 3 or more files using matching column without full list in any of these columns" I have several, typically 3 or 4 files which I need to join, something like FULL JOIN in slq scripts, all combinations of matches should be printed into an... (3 Replies)
Discussion started by: cyz700
3 Replies

9. UNIX for Dummies Questions & Answers

Join 2 files using first column

Hi, I'm trying to compare the first column of two files (tab or whitespace delimited, either way's fine, I`ve got both) and print the lines that are identical for the first column of both files. Something like this: File1 AAA 26 49 7 27 36 33 46 75 73 69 AAAAA 4 10 4 7 10 18 21... (2 Replies)
Discussion started by: vanesa1230
2 Replies

10. 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
Login or Register to Ask a Question