Compare files to pull changed records only


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare files to pull changed records only
# 1  
Old 08-01-2016
Compare files to pull changed records only

Hi,
I am using Sun Solaris - SunOS. I have two fixed width files shown below. I am trying to find the changes in the records in the Newfile.txt for the records where the key column matches. The first column is a key column (example: A123).
If there are any new or deletion of records in the Newfile, I do NOT want that in the output.
All I am try to achieve is the output of the changed records where the key column matches.

File1:OldFile.txt
Code:
A123   Sim   Firstname1          Lastname1  123 JESSE DR.     Atlanta   GA32839   Sampleemail@YAHOO.COM
B234   TWD   Firstname2          Lastname2  123 FORTHILL1 DR. Atlanta   GA32839   Sampleemail2@YAHOO.COM
C567   TWD   Firstname3          Lastname3  123 FORTHILL2 DR. Atlanta   GA32839   Sampleemail3@YAHOO.COM
D89012 TWD   Firstname3          Lastname3  123 FORTHILL2 DR. Atlanta   GA32839   Sampleemail3@YAHOO.COM

File2:NewFile.txt
Code:
A123   Sim  UpdatedNewFirstName1 UpdatedNewLastname1  123 JESSE DR.      Atlanta   GA32839  sampleemail@YAHOO.COM
B234   TWD  Firstname2           Lastname2            123 FORTHILL1 DR.  Atlanta   GA32839  Sampleemail2@YAHOO.COM
C5676  TWD  Firstname3           Lastname3            123 FORTHILL2 DR.  Atlanta   GA32839  Sampleemail3@YAHOO.COM
Z12345 TWD  Firstname3           Lastname4            123 FORTHILL2 DR.  Atlanta   GA32839  Sampleemail3@YAHOO.COM

So in above example: Output would be as shown below. The UpdatedNewFirstName1 and UpdatedNewLastname1 for key A123 is changed.
A123 Sim UpdatedNewFirstName1 UpdatedNewLastname1 123 JESSE DR. Atlanta GA32839 sampleemail@YAHOO.COM

and ignore the two records below:
D89012 TWD Firstname3 Lastname3 123 FORTHILL2 DR. Atlanta GA32839 Sampleemail3@YAHOO.COM -- This record dropped in new file. I do not want this in my output.

Z12345 TWD Firstname3 Lastname4 123 FORTHILL2 DR. Atlanta GA32839 Sampleemail3@YAHOO.COM -- This record added in new file. I do not want this either.

PS: Please ignore the format of the files as I created a sample file above, which might be slightly off.

All I need a CHANGED records where the first field keys matches.

Thanks

Last edited by Don Cragun; 08-01-2016 at 02:37 PM.. Reason: Change HTML tags to CODE tags and remove EMAIL tags.
# 2  
Old 08-01-2016
Code:
awk '
NR==FNR {$1=$1; a[$1]=$0;  next}
a[$1] {l=$0; $1=$1; if (a[$1] != $0) print l}
' File1 File2


Last edited by rdrtx1; 08-01-2016 at 03:29 PM.. Reason: new and improved
This User Gave Thanks to rdrtx1 For This Post:
# 3  
Old 08-01-2016
Since you're using a Solaris/SunOS system, you'll need to use /usr/xpg4/bin/awk or nawk instead of awk. I don't see the need for the three arrays that rdrtx1 used... I think the following should run a tiny bit faster and use less memory while it is running:
Code:
/usr/xpg4/bin/awk '
NR == FNR {
	for(i = 2; i <= NF; i++)
		f1[$1] = f1[$1] SUBSEP $i
	next
}
$1 in f1 {
	f2 = ""
	for(i = 2; i <= NF; i++)
		f2 = f2 SUBSEP $i
	if(f1[$1] != f2)
		print
}' OldFile.txt NewFile.txt

This User Gave Thanks to Don Cragun For This Post:
# 4  
Old 08-01-2016
Slightly different approach:
Code:
awk '
                {gsub (/  */, " ")
                 IX = $1
                 sub ("^" $1, _)
                }

NR == FNR       {TMP[IX] = $0
                 next
                }

match ($0, TMP[IX])     {next
                        }

                {n = split (TMP[IX], CMP)
                 for (i=1; i<=n; i++) if ($i != CMP[i]) printf "%s ", $i
                 print "for key", IX, "is changed."
                 print IX, $0
                }


' file1 file2
UpdatedNewFirstName1 UpdatedNewLastname1 sampleemail@YAHOO.COM for key A123 is changed.
A123  Sim UpdatedNewFirstName1 UpdatedNewLastname1 123 JESSE DR. Atlanta GA32839 sampleemail@YAHOO.COM

This User Gave Thanks to RudiC For This Post:
# 5  
Old 08-09-2016
Thanks a bunch. This was very helpful.

....Saanvi
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. Shell Programming and Scripting

Compare multiple files, identify common records and combine unique values into one file

Good morning all, I have a problem that is one step beyond a standard awk compare. I would like to compare three files which have several thousand records against a fourth file. All of them have a value in each row that is identical, and one value in each of those rows which may be duplicated... (1 Reply)
Discussion started by: nashton
1 Replies

3. Shell Programming and Scripting

Compare two files with different number of records and output only the Extra records from file1

Hi Freinds , I have 2 files . File 1 |nag|HYd|1|Che |esw|Gun|2|hyd |pra|bhe|3|hyd |omu|hei|4|bnsj |uer|oeri|5|uery File 2 |nag|HYd|1|Che |esw|Gun|2|hyd |uer|oi|3|uery output : (9 Replies)
Discussion started by: i150371485
9 Replies

4. Shell Programming and Scripting

Compare 2 files having different number of columns and records

Hi , My requirement is to Compare 2 files having different number of columns and records and get the ouptut containing all the non-matching records from File A(with all column values ) .Example data below : File A contains following : Aishvarya |1234... (4 Replies)
Discussion started by: aishvarya.singh
4 Replies

5. Shell Programming and Scripting

How to compare data from 2 zip files and capture the new records from file2 to a new file

I have 2 zip files which have about 20 million records in each file. file 2 will have additional records than file 1. I want to compare the records in both the files and capture the new records from file 2 into another file file3. Please help me with a command/script which provides me the desired... (8 Replies)
Discussion started by: koneru
8 Replies

6. UNIX for Dummies Questions & Answers

pull date from header and append to all records

I did some searches, but couldn't really find what I'm looking for. I have a file formatted as below: BOF ABC CO - XYZ COMM DATA OF 07/05/2011 EBA00000001 sdfa rtyus uyml EBB00000001 54682 984w3 EBA00000002 mkiyuasdf 98234 I want to pull the date from the header record and add it... (4 Replies)
Discussion started by: keeferb
4 Replies

7. Shell Programming and Scripting

Compare Records between to files and extract it

I am not an expert in awk, SED, etc... but I really hope there is a way to do this, because I don't want to have to right a program. I am using C shell. FILE 1 FILE 2 H0000000 H0000000 MA1 MA1 CA1DDDDDD CA1AAAAAA MA2 ... (2 Replies)
Discussion started by: jclanc8
2 Replies

8. Shell Programming and Scripting

compare files in two directories and output changed files to third directory

I have searched about 30 threads, a load of Google pages and cannot find what I am looking for. I have some of the parts but not the whole. I cannot seem to get the puzzle fit together. I have three folders, two of which contain different versions of multiple files, dist/file1.php dist/file2.php... (4 Replies)
Discussion started by: bkeep
4 Replies

9. Shell Programming and Scripting

Awk Compare Files w/Multiline Records

I'm trying to compare the first column values in two different files that use a numerical value as the key and output the more meaningful value found in the second column of file1 in front of the matching line(s) in file2. My problem is that file2 has multiple records. For example given: FILE1... (4 Replies)
Discussion started by: RacerX
4 Replies

10. Shell Programming and Scripting

How to compare two flat files and get changed data

Hi, I need to compare two flat files (yesterday & today's data) and get only the changed data from flat files. In flat file i dont have data column or anything its just a string data in flat file.Can any one please let me know the script With Regds Shashi (3 Replies)
Discussion started by: jtshashidhar
3 Replies
Login or Register to Ask a Question

Featured Tech Videos