Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Compare Column value from Two Different Files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare Column value from Two Different Files
# 1  
Old 03-29-2007
Compare Column value from Two Different Files
Hi, I need help to write a korn shell script to

1. Check and compare the first file contains single record from the /scp/inbox directory against the badpnt.dat file from the pnt/badfiles directory contains multiple records based on the fam_id column value start at position 38 to 47 from the record below. Here is an example of the record from the file in both directories:
PNT0220060503081122003700100000091049000005629001005146417001407712SFirstname Lastname
2. If fam_id is matched then move current file from the /scp/inbox directory into the pnt/badfiles directory.
If not then continue the normal process
This is what I have so far

pntcnt=`ls -l /$ROOTDIR/scp/inbox/badpnt | grep "PNT." | wc -l`
if [[ $pntcnt -gt 0 ]] then
ls /$ROOTDIR/scp/inbox/badpnt | grep "PNT." > /$ROOTDIR/scp/inbox/badpnt/pntprocess.dat
pntcnt=`wc -l /$ROOTDIR/scp/inbox/badpnt/pntprocess.dat|awk '{print $1}`
sleep 10
curcnt=1
while (( $curcnt <= $pntcnt ))
do
badpnt_chck=`head -$curcnt /$ROOTDIR/scp/inbox/badpnt/pntprocess.dat | tail -1`
fi

pntcnt1=`ls -l /$ROOTDIR/scp/inbox | grep "PNT." | wc -l`
if [[ $pntcnt1 -gt 0 ]] then
ls /$ROOTDIR/scp/inbox | grep "PNT." > /$ROOTDIR/scp/inbox/pntprocess.dat
pntcnt1=`wc -l /$ROOTDIR/scp/inbox/pntprocess.dat|awk '{print $1}`
sleep 10
curcnt1=1
do
inbox_chck=`head -$curcnt1 /$ROOTDIR/scp/inbox/pntprocess.dat | tail -1`
fi

Thank You
Last edited by hanie123; 03-29-2007 at 11:45 AM..
# 2  
Old 03-30-2007
do u mean tht,

/scp/inbox directory has multiple files which contains sigle record.
Now badpnt.dat is the file from the pnt/badfiles directory, which contains multiple records..
now we need to compare contents of every file(from /scp/inbox dir) with badpnt.dat file.
if matching record found in file, move it to pnt/badfiles direcory..

is this interpretation correct? if not plz explain it..
# 3  
Old 03-30-2007
I have the same interpretation of what asmita had.Based on that here is the script.
I have created data files which has one record below
Code:
cat>PNT.1
PNT0220060503081122003700100000091049000005629001005146417001407712SFirstname Lastname
cat>PNT.2
PNT0220060503081122003700100000091049010005629001005146417001407712SFirstname Lastname

The other file in which we will search key if found then move PNT files to another dir.
Code:
cat>testdata
PNT0220060503081122003700100000091049000005629001005146417001407712SFirstname Lastname
PNT0220060503081122003700100000091049010005629001005146417001407712SFirstname Lastname

The script is
Code:
for file in `ls PNT.*` #need to give pathname of files if not in working directory here PNT.1 PNT.2.....are file nam
es to be moved based on search,replace ls /dir/dir1....
 do
line=`sed '1q' $file` #takes first line of the file which will be moved to another dir if pattern found

x=`echo "$line" | awk '{ print substr( $0, 38, 10 ) }'` #take id from this file to search in other file
while read searchline
do
y=`echo "$searchline" | awk '{ print substr( $0, 38, 10 ) }'`
if [ "$x" -eq "$y" ]
then
echo "file moved $file"
mv $file test  #instead of test give directory where the file will be moved
break
fi
done < testdata #need to give full pathname of file (in which file we make the search)

done #end of for loop

Last edited by Dhruva; 03-30-2007 at 05:00 AM..
# 4  
Old 03-30-2007
Thank you for your time Asmita and Dhruva. Your assumption was right. I'll give it a try and get back to you soon.
Thanks
# 5  
Old 03-30-2007
Dhruva, thank you for your help.

Intead of compare the string ID to the /saswork/output/tma/pnt/test/badpnt/badpnt.dat file, can I compare the string ID to files like PNT1, PNT2, PNT3,... in the /saswork/output/tma/pnt/test/badpnt directory?

This is a separate issue that I need to deal with is some how during the ETL process of the file PNT1 from /saswork/output/tma/pnt/test directory through SAS program got hang. I have to manually go in to move a current PNT1 file from /saswork/output/tma/pnt/test directory to /saswork/output/tma/pnt/test/badpnt/ directory and I also need to go to Home directory and remove the PNT.touch file in order to keep the process running again. I have come up with a logic below to resolve the problem.
I copy a current process file PNT1 from /saswork/output/tma/pnt/test and put it into the /pnt/tmp directory.
#Check if copy file successful
if [ cp /saswork/output/tma/pnt/test/PNT1 /pnt/tmp ]
rm /pnt/tmp/PNT.2*
cp /saswork/output/tma/pnt/test/PNT1 /pnt/tmp
#If not
else
mv /saswork/output/tma/pnt/test/PNT1 /saswork/output/tma/pnt/test/badpnt
rm PNT.touch file
and perform the script below. Please give me your thoughts


#need to give pathname of files if not in working directory here PNT.1 PNT.2.....are file names to be moved based on search,
replace ls /dir/dir1....
for file in `ls -l /saswork/output/tma/pnt/test "PNT.2*"`
do
line=`sed '1q' $file` #takes first line of the file which will be moved to another dir if pattern found

x=`echo "$line" | awk '{ print substr( $0, 38, 10 ) }'` #take id from this file to search in other file

while read searchline
do
y=`echo "$searchline" | awk '{ print substr( $0, 38, 10 ) }'`
if [ "$x" -eq "$y" ]
then
echo "file moved $file"
mv $file /saswork/output/tma/pnt/test/badpnt #instead of test give directory where the file will be moved
break
fi
done < /saswork/output/tma/pnt/test/badpnt/badpnt.dat #need to give full pathname of file (in which file we make the search)

done #end of for loop
# 6  
Old 03-30-2007
hanie,

If posting code, please use code tags.
# 7  
Old 04-02-2007
I got the error for the code below. Please help. Thanks
-rwxrwxr-x
rm: /saswork/output/tma/pnt/tmp/*: A file or directory in the path name does not exist.
cp: Not a recognized flag: w
Usage: cp [-fhipHILPU] [-r|-R] [-E{force|ignore|warn}] [--] src target
or: cp [-fhipHILPU] [-r|-R] [-E{force|ignore|warn}] [--] src1 ... srcN directory

Code:
#!/bin/ksh
pntfiles=`ls -l /saswork/output/tma/pnt/test/PNT.2*`
filename=`echo $pntfiles | cut -f 1 -d " "`
echo $filename
if [ -r "/saswork/output/tma/pnt/tmp/$filename" ];
then
 mv /saswork/output/tma/pnt/test/$filename /saswork/output/tma/pnt/test/badpnt
 rm /saswork/output/tma/pnt/tmp/*
fi
if ! [ -r "/saswork/output/tma/pnt/tmp/$filename" ];
then
 rm /saswork/output/tma/pnt/tmp/*
 cp -p $filename /saswork/output/tma/pnt/tmp/
fi

And I had fixed the code below to compare the string ID from the current PNT file to all the files from /saswork/output/tma/pnt/test/badpnt directory

#need to give pathname of files if not in working directory here PNT.1 PNT.2.....are file names to be moved based on search,
replace ls /dir/dir1....
Code:
for gfile in `ls -1 /$ROOTDIR/output/tma/pnt/test/PNT.2*`
 do
echo $gfile
gline=`sed '1q' $gfile` #takes first line of the file which will be moved to another dir if pattern found
echo $gline
x=`echo "$gline" | awk '{ print substr( $0, 38, 9 ) }'` #take id from this file to search in other file
echo $x
for bfile in `ls -1 /$ROOTDIR/output/tma/pnt/test/badpnt/PNT.2*`
 do
echo $bfile
bline=`sed '1q' $bfile` #takes first line of the file which will be moved to another dir if pattern found
echo $bline
y=`echo "$bline" | awk '{ print substr( $0, 38, 9 ) }'`
echo $y
if [ "$x" -eq "$y" ]
then
echo "file moved $file"
mv $gfile /$ROOTDIR/output/tma/pnt/test/badpnt  #instead of test give directory where the file will be moved
break
fi
done
# done < /$ROOTDIR/output/tma/pnt/test/badpnt/badpnt.dat #give full pathname of file (in which file we make the search)
 
done #end of for loop

Last edited by hanie123; 04-02-2007 at 07:07 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

Compare 3rd column in 2 files

I have the following 2 files. File 1 08FB,000192602673,10000000c9a6b240 0121,000192602673,20000025b550101f 0121,000192602673,20000025b550100f 08FA,000192602673,10000000c9a6b240 File 2 18F2,000195702363,10000000c9a6b240 18F3,000195702363,10000000c9a6b240... (2 Replies)
Discussion started by: kieranfoley
2 Replies

3. Shell Programming and Scripting

Compare two files based on column

Hi, I have two files roughly 1200 fields in length for each row, sorted on the 2nd field. I need to compare based on that 2nd column between file1 and file2 and print lines that exist in both files into separate files (I can't guarantee that every line in file1 is in file2). Example: File1: ... (1 Reply)
Discussion started by: origon
1 Replies

4. Shell Programming and Scripting

Compare first column of 2 files and replace

Hi All, I have 2 files in the following format : File 1 S00999999|BHANU|TEST|007 JOHN DOE APT 999||VENGA HIGHWAY|MA|09566|SCO DUAL|20140201|20140331|20140401|20140630|20140327| S00888888|BU|TES|009 JOHN DOE APT 909||SENGA HIGHWAY|MA|08566|SCO... (1 Reply)
Discussion started by: nua7
1 Replies

5. Shell Programming and Scripting

Compare 1 column in 2 files

Hi all, I have two two-column tab-separated files with the following input: inputA dog A dog B cat A.... inputB dog C mouse A output dog I need to compare the 1st column of each file and output those shared items. What is the best unix solution for that? (5 Replies)
Discussion started by: owwow14
5 Replies

6. Shell Programming and Scripting

Compare two files with different column entries..:-(

Dear All, I would appreciate any help..At the moment, my work is stuck cos of my inability to resolve this issue. Which is following: I have two files with the arrngment like this file-1 190645 12 3596022 190645 12 3764915 190645 16 3803981 190645 12 3854102 190645 12 4324593 190645... (12 Replies)
Discussion started by: emily
12 Replies

7. Shell Programming and Scripting

How to compare 2 files column's more than 5?

Hi All I am just trying to compare 2 file using column information using following code awk ' NR==FNR {A=$9; next} {B=A; print $0,B""?B:" Not -In file" } ' OFS="\t" file1 file2if file1 matches with file2 then print $9 content in file 1 along with file2 $0 suppose if I keyed on only $1 in... (17 Replies)
Discussion started by: Akshay Hegde
17 Replies

8. Shell Programming and Scripting

Compare Two Files(Column By Column) In Perl or shell

Hi, I am writing a comparator script, which comapre two txt files(column by column) below are the precondition of this comparator 1)columns of file are not seperated Ex. file1.txt 8888812341181892 1243548895685687 8945896789897789 1111111111111111 file2.txt 9578956789567897... (2 Replies)
Discussion started by: kumar96877
2 Replies

9. Shell Programming and Scripting

Compare files column to column based on keys

Here is my situation. I need to compare two tab separated files (diff is not useful since there could be known difference between files). I have found similar posts , but not fully matching.I was thinking of writing a shell script using cut and grep and while loop but after going thru posts it... (2 Replies)
Discussion started by: blackjack101
2 Replies

10. Shell Programming and Scripting

column compare of files

Hi i want to compare files a.txt 12345,23 34567,76 65456,10 13467,01 b.txt 12346,23 34567,76 23333,90 65456,10 13467,03 i want o/p in 3 files common.txt both have (2 Replies)
Discussion started by: aaysa123
2 Replies
Login or Register to Ask a Question