shell script for matching 2 files

Shell Programming and Scripting
1

Hi,
Would anyone be able to suggest on this shell script query? =)
From the contents of the 2 sample files below, if both files' 1st fields matched (ex: XYZ1234=XYZ1234), I want to append the 4th field (semi-colon delimited) from File2 to the end of that line in File1.
If no match found, then simply append 0000.00 (zero)

File1:
XYZ1234 |Golden State | 500000.00| 10-JUNE-2008| 0654321|Model Railroad Museum
ABC5678 |Philadelphia | 350000.50| 10-JUNE-2008| 0135790|Avenue of Arts
LMN3579 |Seattle | 1180000.00| 15-JUNE-2008| 0427508|Elysian Brewing
RQP8642 |Charlotte | 98500.50| 17-JUNE-2008| 0315386|Paramount's Carowind
TYU4938 |Milwaukee | 85000.50| 17-JUNE-2008| 0526874|Betty Brinn Museum

File2:
XYZ1234;250;TYREQW; 150000.00;10-JUNE-2008;5463789KJH
RQP8642;999;FGHDSA; 55000.50;17-JUNE-2008;4637826TGF
LMN3579;345;CVXZBN; 1000000.00;15-JUNE-2008;8763943NBD
ABC5678;075;PYTIRY; 105000.50;10-JUNE-2008;3792541XOR
XKW4739;123;EKFZHI; 95000.50;15-JUNE-2008;1468953WLC

Required output (new File1):
XYZ1234 |Golden State | 500000.00| 10-JUNE-2008| 0654321|Model Railroad Museum | 150000.00
ABC5678 |Philadelphia | 350000.50| 10-JUNE-2008| 0135790|Avenue of Arts | 105000.50
LMN3579 |Seattle | 1180000.00| 15-JUNE-2008| 0427508|Elysian Brewing | 1000000.00
RQP8642 |Charlotte | 98500.50| 17-JUNE-2008| 0315386|Paramount's Carowind | 55000.50
TYU4938 |Milwaukee | 85000.50| 17-JUNE-2008| 0526874|Betty Brinn Museum | 0000.00

Thanks much in advance.

2

Does it have to be shell script or you can use Perl?

3

Yes, shell script please.
Thanks.

4

What have you done to attempt to solve this problem yourself?
Post your sample script, and we'll see how we can assist.

Regards

5

Hi Sir Franklin,
Sincerely appreciate your response.
I actually have a little idea of how i want to go about this, but i just seem to have a hard time putting everything exactly in code.

I want to loop the File1 line by line (storing the 1st fields, i.e XYZ1234) then use this variable to search in File2, and then get the 5th field from that same equation.
Ex: grep XYZ1234 File2 | awk -F";" '{print $4}'
Then will append that output to the end of the 1st line in File1.
Same will be done for the rest of the lines in File1.

Thanks so much in advance.

6

This should gives the desired output if the format of your files are exact as the given samples:

awk '
NR==FNR{FS=";";$0=$0;a[$1]=$4;next}
{FS=" |";$0=$0}
a[$1]{print $0" |"a[$1];next}
{print $0"| 0000.00"}
' File2  File1

If you get errors use nawk, gawk or /usr/xpg4/bin/awk on Solaris.

Regards

7

Hi Sir Franklin,

Do you mind giving a brief explanation of the above code pls? =)
So i can use it in the future as well.

Thanks so much.

8

Hi Gholdbhurg

this code may help you:

i=1
while true
do
#Get the line from File1
line=`sed -n ${i}p File1`

\#Get the first field in the line
code=\`echo $line | cut -d'|' -f1\`

\#EOF
if [ -z "$code" ]
then
	exit 0;
fi

\#Get the 4th field of the File2
data=\`sed -n '/'$code'/p' File2 | cut -d ';' -f4

\#Join the data
output="$\{line\}|$\{data\}"

\#append it to the file
echo $output>>newFile1

i=\`expr $i \+ 1\`

done

9

Sure.

NR==FNR{FS=";";$0=$0;a[$1]=$4;next}

# Create an associative array with the 1st field as key and the 4th field as value of File2

a[$1]{print $0" |"a[$1];next}

# if the 1st field of File1 exists in the array as index print the value of the array after the line and get the next line

{print $0"| 0000.00"}

# else print the line with "| 0000.00" after the line

' File2  File1

# read File2 first

Regards