Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Compare file using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare file using awk
# 1  
Old 01-20-2015
Compare file using awk
Hi All,

I am trying to compare two comma separated csv file with x number of columns and y number of rows. I want to find out the difference in file with not just complete row but with exact value.
For example below line are from file

File1
Code:
"BALANCE","FOLDER","FOL_Retirement","11.05_BALANCE_FOLDER","ML_FOLDER_BANALCE","FOL_BALANCE","","Total BALANCE","","2011/12/18 12:22:19 AM","2014/08/15 03:52:50 AM","BALANCEISET","False","10/28/2010","","Y","N","Y","99559999","Approved","","","64674232","37652234"

File2
Code:
"BALANCE","FOLDER","FOL_Retirement","11.05_BALANCE_FOLDER","ML_FOLDER_BANALCE","FOL_BALANCE","","Total BALANCE","","2011/12/18 12:22:19 AM","2014/08/15 03:52:50 AM","BALANCEISET","True","10/28/2010","","Y","N","Y","99559999","Approved","","","64674232","37652234"

I want to show the difference as below:

Code:
File1:

Row 	Value
1	False

File2:

Row 	Value
1	True

I can get the difference with diff command but that shows entire line but i want exact difference in value.
I went through some online solution which suggest to use awk but not sure how to use it. Please suggest.

Thanks.
# 2  
Old 01-20-2015
Would you know which field is different if it were e.g. one of those date fields, or one of those integer fields towards the end?
# 3  
Old 01-20-2015
It can be any field.. Purpose if this comparison is validate the files from two tools but different version. So ideal scenario is no difference but if any we need to find what. So it can be any value.
# 4  
Old 01-20-2015
How about
Code:
awk     'BEGIN          {print "Row\tCol\tfile1\tfile2"}
         FNR==NR        {T[NR]=$0; next}
                        {split (T[FNR], W)
                         for (i=1; i<=NF; i++)
                           if ($i != W[i]) print FNR, i, $i, W[i]}
        ' FS=, OFS="\t" file[12]
Row    Col    file1    file2
1      13    "True"    "False"

This User Gave Thanks to RudiC For This Post:
ankitpandeyit
# 5  
Old 01-20-2015
Thanks for the response RudiC.

I tried to run your code by replacing file[12] with file1 file2 but the output was very weird.
Then i shuffled the code like below and saved in script.sh file.

Code:
awk     'BEGIN          {FS=","; OFS="\t"; print "Row\tCol\tfile1\tfile2";}
         FNR==NR        {T[NR]=$0; next}
                        {split (T[FNR], W)
                         for (i=1; i<=NF; i++)
                           if ($i != W[i]) print FNR, i, $i, W[i]}
        '

and then executed it like below:
Code:
sh script.sh file1 file2

and i am getting no output other than below:
Code:
Row     Col     file1   file2

Please suggest how to execute as it looks i am not running it correct.
# 6  
Old 01-20-2015
What was the weird output? Pls. post.

And, if using in script, you'll have to add the positional parameters $1 and $2 after the awk command to access and open the respective files.

---------- Post updated at 18:52 ---------- Previous update was at 18:51 ----------

EDIT: And, sorry, swap $i and W[i] so they fit the header.
# 7  
Old 01-20-2015
Below is the execution output:

Code:
$ awk     'BEGIN          {print "Row\tCol\tfile1\tfile2"}
>          FNR==NR        {T[NR]=$0; next}
>                         {split (T[FNR], W)
>                          for (i=1; i<=NF; i++)
>                            if ($i != W[i]) print FNR, i, $i, W[i]}
>         ' FS=, OFS="\t" file1 file2
Row     Col     file1   file2
\t1\t"BALANCE"\t
\t2\t"FOLDER"\t
\t3\t"FOL_Retirement"\t
\t4\t"11.05_BALANCE_FOLDER"\t
\t5\t"ML_FOLDER_BANALCE"\t
\t6\t"FOL_BALANCE"\t
\t7\t""\t
\t8\t"Total BALANCE"\t
\t9\t""\t
\t10\t"2011/12/18 12:22:19 AM"\t
\t11\t"2014/08/15 03:52:50 AM"\t
\t12\t"BALANCEISET"\t
\t13\t"False"\t
\t14\t"10/28/2010"\t
\t15\t""\t
\t16\t"Y"\t
\t17\t"N"\t
\t18\t"Y"\t
\t19\t"99559999"\t
\t20\t"Approved"\t
\t21\t""\t
\t22\t""\t
\t23\t"64674232"\t
\t24\t"37652234"\t
\t1\t"BALANCE"\t
\t2\t"FOLDER"\t
\t3\t"FOL_Retirement"\t
\t4\t"11.05_BALANCE_FOLDER"\t
\t5\t"ML_FOLDER_BANALCE"\t
\t6\t"FOL_BALANCE"\t
\t7\t""\t
\t8\t"Total BALANCE"\t
\t9\t""\t
\t10\t"2011/12/18 12:22:19 AM"\t
\t11\t"2014/08/15 03:52:50 AM"\t
\t12\t"BALANCEISET"\t
\t13\t"True"\t
\t14\t"10/28/2010"\t
\t15\t""\t
\t16\t"Y"\t
\t17\t"N"\t
\t18\t"Y"\t
\t19\t"99559999"\t
\t20\t"Approved"\t
\t21\t""\t
\t22\t""\t
\t23\t"64674232"\t
\t24\t"37652234"\t
$
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Awk: compare values in two columns of the same file

I'm trying to learn awk, but I've hit a roadblock with this problem. I have a hierarchy stored in a file with 3 columns: id name parentID 4 D 2 2 B 1 3 C 1 1 A 5 I need to check if there are any values in column 3 that are not represented anywhere in column 1. I've tried this: awk '{arr;}... (7 Replies)
Discussion started by: kaktus
7 Replies

2. Shell Programming and Scripting

awk to compare each file in two directores by storing in variable

In the below bash I am trying to read each file from a specific directory into a variable REF or VAL. Then use those variables in an awk to compare each matching file from REF and VAL. The filenames in the REF are different then in the VAL, but have a common id up until the _ I know the awk portion... (15 Replies)
Discussion started by: cmccabe
15 Replies

3. Shell Programming and Scripting

Compare two files and write data to second file using awk

Hi Guys, I wanted to compare a delimited file and positional file, for a particular key files and if it matches then append the positional file with some data. Example: Delimited File -------------- Byer;Amy;NONE1;A5218257;E5218257 Byer;Amy;NONE1;A5218260;E5218260 Positional File... (3 Replies)
Discussion started by: Ajay Venkatesan
3 Replies

4. Shell Programming and Scripting

How to compare the values of a column in a same file using awk?

Dear Unix experts, I have got a file where I would like to compare the values of second column if first column is same in such a way that the difference between the values is >50. If not, I would like to discard both values. For example, my input file looks like - comp275_c0_seq2 73... (7 Replies)
Discussion started by: utritala
7 Replies

5. Shell Programming and Scripting

awk - compare records of 1 file with 3 files

hi.. I want to compare records present in 1 file with those in 3 other files and print those records of file 1 which are not present in any of the files. for eg - file1 file2 file3 file4 1 1 5 7 2 2 6 9 3 4 5 6 7 8 9 ... (3 Replies)
Discussion started by: Abhiraj Singh
3 Replies

6. Shell Programming and Scripting

awk compare column n replace with in one file

hi Friends need to compare columns in one file where the data looks like below laptop,IBM phone,samsung car,rental user1,laptop user2,laptop user3,phone want to get output as laptop,IBM phone,samsung car,rental user1,IBM user2,IBM user3,samsung need to seach $2 in array of $1 and... (4 Replies)
Discussion started by: arun1401
4 Replies

7. UNIX for Dummies Questions & Answers

Help with AWK - Compare a field in a file to lookup file and substitute if only a match

I have the below 2 files: 1) Third field from file1.txt should be compared to the first field of lookup.txt. 2) If match found then third field, file1.txt should be substituted with the second field from lookup.txt. 3)Else just print the line from file1.txt. File1.txt:... (4 Replies)
Discussion started by: venalla_shine
4 Replies

8. Shell Programming and Scripting

awk command to compare a file with set of files in a directory using 'awk'

Hi, I have a situation to compare one file, say file1.txt with a set of files in directory.The directory contains more than 100 files. To be more precise, the requirement is to compare the first field of file1.txt with the first field in all the files in the directory.The files in the... (10 Replies)
Discussion started by: anandek
10 Replies

9. Shell Programming and Scripting

(awk) compare files in dir with an index file

Using awk I have an index file which has been seperated into 5 fields. The first field contains file names. What I need to do is check to see if a file exists in my current directory that is not in the first field of my index file. If its not i print out a message. Please help! (4 Replies)
Discussion started by: xthexonex
4 Replies
Login or Register to Ask a Question