Need help in comparing two files in UNIX with a mismatch


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help in comparing two files in UNIX with a mismatch
# 1  
Old 01-27-2015
Need help in comparing two files in UNIX with a mismatch

Hi Everyone,
I am comparing results of two environments using unix files.

I am writing two different csv file using spool and merging both the files using paste command

paste -d, file1.csv file2.csv > prod_uat_result.csv

and then finding the difference and attaching the same in a mail

Code:
cat prod_uat_result.csv |awk -F"," '{print $1,",",$2,",",$3,",",$4,",",$5,",",$6,",",$7,",",$8,",",$9,",",$10,",",$11,",",$12
,",",$13,",",$14,",",$15,",",$16,",",$17,",",$6-$14}' > prod_uat_result1.csv

uuencode prod_uat_result1.csv prod_uat_result1.csv > prod_uat_result1.txt

Under normal circumstances it is working fine.But if there is some record missing in file2.csv then it should skip the comparision.

ex
file1.csv
Code:
1,10
1,20
2,10
2,20
2,30
3,40
4,50

file2.csv
Code:
1,10
1,20
2,10
2,20
2,30
4,50

So,ideally (3,40) in file1.csv should not be compared with 4,50 of file2.csv to find the difference.

I have tried left outer join but it will not work out since there is no primary key,have also tried giving row number while writing to a file but that will also not compare the right results.

My requirement is, if row is not present in the second file (file2.csv) then the comparison should be skipped and compare next row.

Could you please help me with this

Last edited by rbatte1; 01-27-2015 at 08:30 AM.. Reason: Added CODE tags and corrected spelling.
# 2  
Old 01-27-2015
Please use code tags as required by forum rules!

Is there any common field in both files by which the comparison could be done, or the missing row be identified, respectively?
# 3  
Old 01-27-2015
Hello karthik adiga,

Kindly use code tags as per forum rules, assuming you need differances only with respect to the lines which are which are present in file1 and NOT present in file1 then following may help you. But it wouldn't tell differance lines which are present in file2 and NOT in file1.

Code:
awk -F, 'FNR==NR{X[$1 FS $2]=$1 FS $2;next} ($0 in X){print X[$0] " entry from file1 is present in file2.";delete X[$0]} END{for(i in X){print X[i] " entry NOT present in file2."}}' file1.csv file2.csv

Output will be as follows.
Code:
1,10 entry from file1 is present in file2.
1,20 entry from file1 is present in file2.
2,10 entry from file1 is present in file2.
2,20 entry from file1 is present in file2.
2,30 entry from file1 is present in file2.
4,50 entry from file1 is present in file2.
3,40 entry NOT present in file2.

Thanks,
R. Singh
# 4  
Old 01-27-2015
Thanks Ravindra for the solution.

I am getting syntax error when trying to execute the same.

Code:
cib-sokay2{u384283}167:awk -F, 'FNR==NR{X[$1 FS $2]=$1 FS $2;next} ($0 in X){print X[$0] " entry from file1 in file2.";
> delete  X[$0]} END{for(i in X){print X[i] " entry NOT present in file2."}}' file1.csv file2.csv
awk: syntax error near line 1
awk: bailing out near line 1

I am new to unix.Could you please help me in solving this

Last edited by rbatte1; 01-27-2015 at 08:35 AM..
# 5  
Old 01-27-2015
If you are on Solaris/SunOS system, change awk at the start of the script to /usr/xpg4/bin/awk or /usr/xpg6/bin/awk or nawk .
This User Gave Thanks to Akshay Hegde For This Post:
# 6  
Old 01-27-2015
Hello karthik,

Seems you are using a Solaris/SunOS system, on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
# 7  
Old 01-27-2015
Thanks a lot Ravinder and Akshay..its working Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. AIX

Compare two files and show the mismatch columns

I need to compare two files and find the mismatch columns in it for csv and fixed width file. Eg: file1 c1,c2,c3,c4<----columnname 1,a,4,d 2,b,5,e 3,c,6,f file2 c1,c2,c3,c4<----columnname 3,x,7,f 2,y,8,e 1,z,9,d output c2,c3<---- mismatch columname a,4 x,7 b,5 or y,8 Ok with... (3 Replies)
Discussion started by: sabzR
3 Replies

2. UNIX for Dummies Questions & Answers

Comparing 2 UNIX files

Hi, I am planning to automate the comparison of data between few tables in 2 different databases ( Teradata and sql server). Below is the approach which I think of. Please suggest any improvements/Modification : 1) The sql server file is having more records and I have to eliminate the... (5 Replies)
Discussion started by: Rahul Raj
5 Replies

3. Shell Programming and Scripting

Mismatch in summing a column in UNIX

Hello, I am facing issue in summing up a column in unix.I am displaying a column sum up to 4 decimal places and below is the code snippet sed '1d' abc.csv | cut -d',' -f7 | awk '{s+=$1}END{ printf("%.4f\n",s)}' -170552450514.8603 example of data values in the column(not... (3 Replies)
Discussion started by: karthik adiga
3 Replies

4. Shell Programming and Scripting

Comparing two files in UNIX and create a new file similar to equi join

I have 2 files namely branch.txt file & RXD.txt file as below Ex:Branch.txt ========================= B1,Branchname1,city,country B2,Branchname2,city,country B3,Branchname3,city,country B4,Branchname4,city,country B5,Branchname5,city,country RXD file : will... (11 Replies)
Discussion started by: satece
11 Replies

5. Shell Programming and Scripting

Comparing Select Columns from two CSV files in UNIX and create a third file based on comparision

Hi , I want to compare first 3 columns of File A and File B and create a new file File C which will have all rows from File B and will include rows that are present in File A and not in File B based on First 3 column comparison. Thanks in advance for your help. File A A,B,C,45,46... (2 Replies)
Discussion started by: ady_koolz
2 Replies

6. Shell Programming and Scripting

Count mismatch in UNIX

Hi, I have a requirement like below. client is sending the .txt filles.In that file we have 10 records but when I execute the below command it is showing 9 records. klena20> wc -l sample_file.txt|awk '{print $1}' It is showing the output as 9 But in a file records are 10. I found... (7 Replies)
Discussion started by: kirankumar
7 Replies

7. UNIX for Dummies Questions & Answers

Files count mismatch when used with Tar with find

Hi I have used the below steps and found some discrepancies step 1 : find ./ -type f -mtime +7 -name "*.00*" | wc -l = 13519 ( total files ) ( the size of this files is appx : 10GB ) step 2: find ./ -type f -mtime +7 -name "*.00*" | xargs tar zcvf Archieve_7.tar.gz step... (7 Replies)
Discussion started by: rakeshkumar
7 Replies

8. Shell Programming and Scripting

Compare two files and mismatch report

Hi I have two files f1 and f2 and comma separated file. I need to comapre two files by field by field and not by whole line. If they match then skip the line from both the files. If they don't match write the mismatch record from f1 to f3. Assume both the files are sorted on first field. ... (5 Replies)
Discussion started by: dgmm
5 Replies

9. Shell Programming and Scripting

Edited: compare two files and print mismatch

Using unix shell script, how to compare two files and print lines with mismatch? Below are the requirements: 1. The number of lines on the two files is not the same. 2. The difference/mismatch can be found on the second or third column. 3. The comparison is not between line 1 of file 1 and line... (16 Replies)
Discussion started by: kingpeejay
16 Replies

10. Shell Programming and Scripting

comparing two files and find mismatch

hi i have two files and i want to compare both the files and find out mismatch in 3rd file file1 00354|1|0|1|1|0|0|0|1|2 52424|1|0|1|1|0|0|0|1|2 43236|1|0|1|1|0|0|0|1|2 41404|1|0|1|1|0|0|0|1|2 79968|1|0|1|1|0|0|0|1|2 file2 00354|1|0|1|1|0|0|0|1|2 52424|1|0|1|1|0|0|0|0|2... (9 Replies)
Discussion started by: dodasajan
9 Replies
Login or Register to Ask a Question