Compare Excel Files with Flexible Primary Keys
What means Primary Keys in context of the database?
This is unique value, that identifies record in the database table. Primary Key is usually implemented as numeric (counter) field, used to build relationships between database tables.
But, in the context of the Excel Tables we are working with, this definition needs some flexibility.
Often, we have a complex keys, that consists of several columns. In this case minor modification in one of the key values leads to inaccurate diff report for compared excel files. For example,
in the file you've received from a colleague full customer name is used as key column. But in this key you've just correct wrong second name - changed 2 letters. By using common database strategy
both values will be marked as unique.
But in the flexible comparison mode - they are marked as updated. This is important, as it properly determines corresponding values in xlCompare
Ability to compare Excel Files by Key Values is applicable if you are working with usual excel files and if you compare scv files
or text files with delimiter.
Allow Changed Primary Keys in Excel Tables
So, how to make Primary Keys in your Excel Tables flexible?
Go to the Options and select Database tab:
On the bottom of this window there is an option: Allow Changed Primary Keys in the Database Tables. This is the option, that turns ON or OFF
flexibility of Key Columns in your table.
By default this option is enabled, so excel diff tool
(xlCompare) already gives you diff report with changed keys instead of unique values.
Below this option there is a Accuracy of the Primary Key option. And you see probably 15% - default value.
What does this mean? xlCompare needs to determine which keys are changed and which ones are different. Imagive following situation:
There are two key values: ABCDEF12 and ABCDEF15, can we consider them as equal, but changed? Probably - YES. The values look quite similar. But what about ABCDEF12 and ABKLMN12 - are they equal?
There is no ideal way to determine which values are equal and which of them are different. So, we are using an algorithm which calculate deviation ratio between two key values.
With this option you can define limit, where values are treat as different. By default exce file compare tool
ratio to distinct updated and unique key values.
But if the excel spreadsheet files, you are working with, require strict comparison. 100% match of the primary keys. We suggest you to turn this option OFF and get the diff report for compared excel files with
common database comparison strategy.