Shell Programming and Scripting

Hello,

I am strugling from quite a some time to compare flat files with over 1 million records could anyone please help me.

I want to compare two pipe delimited flat files, file1 with file2 and output the unmatched rows from file2 in file3

Sample File1:

Code:

210|testdata1|0003431231|sjhhfskdf|5-NOV-99|
010|testdata2|00234001231|sjhhfskdf|5-NOV-99|
111|testdata3|0003421101231|sjhhfskdf|5-NOV-99|

Sample File2:

Code:

210|xxxxxxxxxxxx|0003431231|sjhhfskdf|5-NOV-99|
010|testdata2|00234001231|sjhhfskdf|5-DEC-99|
111|testdata3|0003421101231|sjhhfskdf|5-NOV-99|

Sample output in file3:

Code:

210|xxxxxxxxxxxx|0003431231|sjhhfskdf|5-NOV-99|
010|testdata2|00234001231|sjhhfskdf|5-DEC-99|

Can this be done using awk, any help on this is much appreciated.

Moderator's Comments:

Use code tags please, ty.

Try...

Code:

grep -vf File2 File1 > File3

Code:

cat file1 > final_file
cat file 2 >> final file 

uniq -u final_file >> result_file

Thank you dazdseg, with this I can find the uniq records/matching records in both the files, is there a way I can move diffrence records/unmatched records in a new file.

Code:

cat file1 > final_file
cat file 2 >> final file 

uniq -u final_file >> result_file

Sorry to be a wet blanket but neither the grep nor the uniq approach will fulfill the requirement, even if the data was in sorted order (which it isn't).

1) Do both files have exactly the same number of records and are you just looking for records which have changed? Does the order of the output into file3 matter?
2) If there can be more or less records in file2 than file1, does the order of the output into file3 matter?
Are you also interested in records which exist in file1 but do not exist in file2?
3) What percentage of differences do you expect? (This is really a performance question because some approaches would involve multiple lookups).
4) If this proves too difficult for shell programming, do you have a mainstream database engine?

---------- Post updated at 15:05 ---------- Previous update was at 14:20 ----------

One shell approach if the order of the output does not matter.
Tried with two approx 5 million record files of 500 Mb each. Took about 5 mins to run and the output only shows the mismatched records from file2. Actual performance will depend on how fast you computer is and how much memory you can give to sort.

Code:

#!/bin/ksh
cat file1 | sort > sortfile1
cat file2 | sort > sortfile2
comm -13 sortfile1 sortfile2

When sorting large files be sure to set $TMPDIR to somewhere with enough space for at least twice the size of the file being sorted.

Thanks for your time on this, its much appreciated

1) Do both files have exactly the same number of records and are you just looking for records which have changed? Does the order of the output into file3 matter?

File1 has 1803077 records
file2 has 1795370 records

2) If there can be more or less records in file2 than file1, does the order of the output into file3 matter?

I would prefer 1st row in file3 from file1 and 2nd row from file2 and so on

Are you also interested in records which exist in file1 but do not exist in file2?

Yes, and viceversa also, it would be good if we can copy the records to diffrent files say recordsonlyonfile1.txt and recordsonlyonfile2.txt

3) What percentage of differences do you expect? (This is really a performance question because some approaches would involve multiple lookups).

there are huge changes in the file it could be over 50%

4) If this proves too difficult for shell programming, do you have a mainstream database engine?

I have informix database I am not sure if this would not help me as there is no uniq key in the records



---------- Post updated at 15:05 ---------- Previous update was at 14:20 ----------

One shell approach if the order of the output does not matter.
Tried with two approx 5 million record files of 500 Mb each. Took about 5 mins to run and the output only shows the mismatched records from file2. Actual performance will depend on how fast you computer is and how much memory you can give to sort.

Code:

#!/bin/ksh
cat file1 | sort > sortfile1
cat file2 | sort > sortfile2
comm -13 sortfile1 sortfile2

When sorting large files be sure to set $TMPDIR to somewhere with enough space for at least twice the size of the file being sorted.[/QUOTE]

The database approach is winning because of the requirement to retain the original random order and to get enough speed to check each record in turn. Can't see an obvious way to keep any other form of compare in step when there is such a high volume of differences.

1) Load each external flat file into a separate table, using the whole record as the key.
2) Re-read each external flat file in turn checking the result of a seek of each record in the opposing file, outputting non-matches to a respective further external file.