File comparison in UNIX columnwise


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting File comparison in UNIX columnwise
# 1  
Old 06-24-2014
Linux File comparison in UNIX columnwise

Hi all,

I want to compare two files with same number of rows and columns with records in same order.
Just want to highlight the differences in the column values if any.

file A

Code:
1,kolkata,19,ab
2,delhi,89,cd
3,bangalore,56,ef

file2:

Code:
1,kolkata,21,ab
2,mumbai,89,gh
3,bangalore,11,kl

considering column 1 as primary key, we have differences in other columns.
i want to highlight those differences

Output format may be (not sure ):

Code:
record_number , columns_with_diff
1  3
2  2,4
3  3,4

does diff or comm can solve my problem ? if yes then what would be the exact command.. m trying but not getting Smilie

Last edited by Scrutinizer; 06-24-2014 at 10:05 AM.. Reason: CODE tags ; removed empty lines
# 2  
Old 06-24-2014
Try

Code:
 $ awk -F, 'BEGIN{print "record_number , columns_with_diff"}
         NR==FNR{for(i=2;i<=NF;i++){A[$1,i]=$i}next}{for(i=2;i<=NF;i++){if($i!=A[$1,i]){p=p?p","i:i}}print $1,p;p=""}' file1 file2

record_number , columns_with_diff
1 3
2 2,4
3 3,4

This User Gave Thanks to pamu For This Post:
# 3  
Old 06-24-2014
That is awesome..!!

Thanx pamu.! Smilie
# 4  
Old 06-24-2014
Variation without using arrays:
Code:
awk -F, '{s=p=x; getline p<f; split(p,F); for(i=1; i<=NF; i++) if($i!=F[i]) s=s (s?FS:x) i; print NR, s}' f=file2 file1


Last edited by Scrutinizer; 06-24-2014 at 10:39 AM..
This User Gave Thanks to Scrutinizer For This Post:
# 5  
Old 06-24-2014
Thanks moderator..!!
# 6  
Old 06-24-2014
You are welcome... I just notice you want to use $1 as primary key instead of the line number. Then this adaptation would be required:
Code:
awk -F, '{s=p=x; getline p<f; n=split(p,F)} n<NF{n=NF} {for (i=2; i<=n; i++) if($i!=F[i]) s=s (s?FS:x) i; print $1, s}' f=file2 file1

This User Gave Thanks to Scrutinizer For This Post:
# 7  
Old 06-24-2014
One little modification Smilie

what if records are not in order and we need to compare them on keys say:

file A

Code:
1,kolkata,19,ab
2,delhi,89,cd
3,bangalore,56,ef

File B
Code:
2,mumbai,89,gh
1,kolkata,21,ab
3,bangalore,11,kl

I would need primary_key, columns_mismatching(Same requirement)

Thanks for ur help..!!

Last edited by Scrutinizer; 06-24-2014 at 11:17 AM.. Reason: code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

UNIX file comparison

I have two files which has component name and version number separated by a space cat file1 com.acc.invm:FNS_PROD 94.0.5 com.acc.invm:FNS_TEST_DCCC_Mangment 94.1.6 com.acc.invm:FNS_APIPlat_BDMap 100.0.9 com.acc.invm:SendEmail 29.6.113 com.acc.invm:SendSms 12.23.65 cat file2 ... (8 Replies)
Discussion started by: rakeshtomar82
8 Replies

2. UNIX for Dummies Questions & Answers

Reading Xml file and print the values into the text file in columnwise?

hi guys, i want help... Reding XML file and print the values into the text file using linux shell script file as per below xml file <sequence> <Filename>aldorzum.doc</Filename> <DivisionCode>US</DivisionCode> <ContentType>Template</ContentType> <ProductCode>VIMZIM</ProductCode> </sequence>... (4 Replies)
Discussion started by: sravanreddy
4 Replies

3. Shell Programming and Scripting

Trim a file columnwise

Hi All, I want to trim each columns leading & trailing (like sql trim function) of a ',' separated file.. I've a file like this.. manab , c gi lucky , home babu , maa I want the output as manab,c gi lucky,home babu,maa A one liner would be a great help. (2 Replies)
Discussion started by: manab86
2 Replies

4. Shell Programming and Scripting

Parse a File ColumnWise & Delete the Rows

Hi , I have a CSV file that has around 50K records in it. I have to delete all the duplicate rows from the file except one, depending upon data in column4. Lets say there are 3 rows for 'column4data' in the file. I have to retain only that line which has the smallest date value in column2.... (3 Replies)
Discussion started by: Sheel
3 Replies

5. UNIX for Advanced & Expert Users

Insert records in a file columnwise

Hello everyone!!!!! Need some help on inserting data in a file columnwise. Example: File1: 1|AAA|25|2 5|qqe|20|7 4|wer|31|81 I need to create a second file in which data can be inserted in a columnwise manner i.e. File2: AAA|25|1|2 qqe|20|5|7 wer|31|4|81 The order of columns in... (1 Reply)
Discussion started by: abhijeet1409
1 Replies

6. Shell Programming and Scripting

Insert records in a file columnwise

Hello everyone!!!!! Need some help on inserting data in a file columnwise. Example: File1: 1|AAA|25|2 5|qqe|20|7 4|wer|31|81 I need to create a second file in which data can be inserted in a columnwise manner i.e. File2: AAA|25|1|2 qqe|20|5|7 wer|31|4|81 The order of columns in... (1 Reply)
Discussion started by: abhijeet1409
1 Replies

7. Shell Programming and Scripting

Comparing files columnwise and print the differences in third file

Hello Everybody!!!!!!!!! Request you to help me with the below mentioned issue: I have 2 files say, File 1: a|4|7 b|3|2 c|8|8 d|8|9 File 2: a|4|6 b|2|2 c|8|8 d|9|8 The third file(output file) should have: Data mismatch in row 1 column 3 Data mismatch in row 2 coumn 2 Data... (3 Replies)
Discussion started by: abhijeet1409
3 Replies

8. UNIX for Dummies Questions & Answers

Comparison Unix and Windows file sysytem

i want to know the similarities and disimilarities of Unix and Windows file systems . methods of file , file orgsnization , meaning of file extension , file and disk fragments :confused::confused: (1 Reply)
Discussion started by: localp
1 Replies

9. Shell Programming and Scripting

rearranging the data in file (from columnwise to rowwise)

Hi I have one file which is having data like 10201 10202 10205 10206 10207 10208 10209 10210 10211 10213 10215 10801 10802 11406 11415 11422 11426 11513 11514 11515 11516 11517 11518 11519 11520 11521 11522 11523 11524 11525 11530 11604 11608 11611 11717 11718 11719 11722 11725... (3 Replies)
Discussion started by: reldb
3 Replies

10. UNIX for Dummies Questions & Answers

Unix comparison

I am very new to Unix. What are the similiarities and differences between ScoUnix and AIX5 if any? Where might i find the information? Which is better? (1 Reply)
Discussion started by: NewGuy100
1 Replies
Login or Register to Ask a Question