awk compare files

01-20-2015

Registered User

23, 0

Join Date: Oct 2010

Last Activity: 21 January 2015, 1:41 PM EST

Posts: 23

Thanks Given: 1

Thanked 0 Times in 0 Posts

awk compare files

I have a below requirement and trying to compare the files using awk

File 1 - Already stored on a prev day

Code:

 id   | text       | email id 
---------------------------------
89564|this is line 1 | xyz@sample.txt
985384|this is line 2 | abc@sample.txt
657342|this is line 3 | def@sample.txt

File 2 - Arrived today

Code:

 id   | text       | email id 
---------------------------------
89564|this is line 4 | xyz@sample.txt
657342|this is line 3 | def@sample.txt
985384|this is line 2 | abc@sample.txt

Requested output

Code:

id   | text        | email id       | operation 
-------------------------------------------------
89564|this is line 4  | xyz@sample.txt | modified 
985384|this is line 2 | abc@sample.txt | deleted
657342|this is line 3 | def@sample.txt | inserted

We are trying to use hadoop but however because with pseudo database hive we don't have insert or update operation , hence we are trying to generate the operation code at the shell scripting .

I tried a code as below but it does not give me a desired ouput

Code:

 
 awk -F"|" 'NR==FNR{a[$1]=$2;next}{if (a[$1])print a[$1],$0;else print "inserted", $0;}' OFS="|" file1 file2

Last edited by Don Cragun; 01-20-2015 at 09:35 PM.. Reason: Fix CODE tags.

rakesh_411

View Public Profile for rakesh_411

Find all posts by rakesh_411

01-21-2015

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

I don't understand the logic behind your requested output.

For id 89564, the output line doesn't match either input line. (The text field in the output has an extra space that does not appear in File 2.)

For id 657342, both entries in the input files are identical. So why do you want to say it should be deleted???

For id 985384, both entries in the input files are identical. So why do you want to say it should be inserted???

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

01-21-2015

Registered User

23, 0

Join Date: Oct 2010

Last Activity: 21 January 2015, 1:41 PM EST

Posts: 23

Thanks Given: 1

Thanked 0 Times in 0 Posts

I am sorry for not stating my requirement properly , We are trying to accomplish incremental load of data by comparing two files

File 1

Code:

 id   | text       | email id 
---------------------------------
89564|this is line 1 | xyz@sample.txt
985384|this is line 14 | abc@sample.txt
657342|this is line 3 | def@sample.txt

File 2

Code:

 id   | text       | email id 
---------------------------------
89564|this is line 1 | xyz@sample.txt
985384|this is line 2 | abc@sample.txt

I would like output by comparing above two files which should display below results
Desired Output:
-------------------

Code:

 id   | text       | email id 
---------------------------------
89564|this is line 1 | xyz@sample.txt
985384|this is line 2 | abc@sample.txt | modified (Note :  second column got modified ) 
657342|this is line 3 | def@sample.txt | deleted

Here we are trying to compare two files and if we don't find a record in file 2 based on column 1 ( primary key ) we treat that entry as deleted and we generate a new file with its operational code(in this case deleted). Similarly we if we find record in both the files ( file 1 and file2) we would still have that record in file 3 ,and for modified if we see any column modifies in file 2 we add the entry as modified .

Last edited by Don Cragun; 01-21-2015 at 04:02 PM.. Reason: Add CODE tags.

rakesh_411

View Public Profile for rakesh_411

Find all posts by rakesh_411

01-21-2015

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

Please use code tags as required by forum rules!

Try

Code:

awk     'NR==FNR        {T[$1]=$0; next}
                        {printf "%s", $0}
         $1 in T        {if ($0 != T[$1]) printf "|modified."  
                         delete T[$1]
                         printf "\n"
                         next
                        }
                        {print  "|inserted"}  
         END            {for (t in T) print T[t], "deleted."} 
        ' FS="|" OFS="|" file1 file2
id | text | email id
---------------------------------
89564|this is line 1 | xyz@sample.txt
985384|this is line 2 | abc@sample.txt|modified.
885384|this is line 7 | abc@sample.txt|inserted
657342|this is line 3 | def@sample.txt|deleted.

This User Gave Thanks to RudiC For This Post:

RudiC

View Public Profile for RudiC

Find all posts by RudiC

Shell Programming and Scripting

awk compare files

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

[awk] Compare two files

Discussion started by: ettore8888

2. Shell Programming and Scripting

Compare 2 files, awk maybe?

Discussion started by: phaethon

3. HP-UX

Awk compare two files

Discussion started by: Eduardo Aceves

4. Shell Programming and Scripting

Compare files using awk

Discussion started by: Mary James

5. Shell Programming and Scripting

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

Discussion started by: anandek

6. Shell Programming and Scripting

Compare two files with awk

Discussion started by: freyr

7. UNIX for Dummies Questions & Answers

Using AWK to compare 2 files

Discussion started by: ladyAnne

8. Shell Programming and Scripting

compare two files using awk

Discussion started by: gpaulose

9. Shell Programming and Scripting

Compare two files using awk

Discussion started by: paul.o

10. Shell Programming and Scripting

awk compare 2 files

Discussion started by: kenray