Join two database tables stored in the excel worksheets with xlCompare – excel file diff and merge tool
Consolidate Databases
xlCompare is not only excel file compare tool. This is utility for consolidating and merging excel sheets with any structure – generate excel spreadsheet, database table, invoice, price list, cash flow statement, etc. We give you best excel compare tool to quickly complete the spreadsheet operations you have on the daily basis.
Join Excel Tables by Primary Key
Primary Key is a fundamental part of the database theory. Excel file compare tool gives you handy and flexible commands to define and manage primary key columns and rows in your excel file.
Merge Excel Sheets into One
Sometimes you need to combine the information, stored on the several excel worksheets in your file into one single sheet (consolidate data from the excel sheets). xlCompare has a command to do this in one click. Just select master sheet that receives data and press a button to complete the operation!
How to use Excel File Compare Tool to Merge Tables
In this article we are talking about merging database tables with excel diff tool, so we would like to point your attention on the Primary Keys and how they are using by our xl comparator.
If two excel sheets have same set of the primary columns, we can compare all rows in the excel tables by key values. For the database tables this is the only right method. All other algorithms give you approximate results and sometimes records that does not match are shown as equal.
Can the Primary Keys be duplicated? In general case – no. The definition of the Primary Key says that this is unique value. But usually, we are getting tables where key columns have duplicated records. You can say, that in this case primary key you have is not primary. This is just a set of fields. And you will be absolutely right – this is not a primary key column. But in the real excel worksheet in the most cases we don’t have an ID column with unique value. So, we need to compare the data with such flexible key columns and key rows. So, xlCompare works with duplicated key values. This is not a problem for our excel diff tool.
Defining Primary Keys on your sheet
Select the entire columns you plan to use as primary key and show a Right Click Menu. There is a command Set as Primary Key. It marks this column as a key column for comparison and merge operations with this excel worksheet. You can select multiple columns at one and mark all of them as key. In this case you have a composite primary key.
Right Click Menu command Clear Primary Keys removes all primary key rows and columns from the worksheet. So, you can define them from scratch.
The same operation works for rows in the excel sheet. Select the entire row and in the right click menu mark it as a primary key.
Can I merge two unsorted excel worksheets into one with xlCompare?
Yes, sure. Excel File Compare Tool works with unsorted data. Our algorithms accept database records that comes in the random order.
Before you compare worksheets, we ask you if you would like to sort the data. After comparison is completed, sorted data looks much better and allows us to align comparison report. So, we recommend you enable this option and let excel diff tool to sort worksheet rows before merging.
Step by step instruction to merge excel tables into one file
1 Drop the Excel Files into excel file compare tool window. After this step, your excel tables are located side by side and ready for processing.
2 Make sure, that Primary Keys on your worksheets are correct. If needed, clear them with a right click menu and define new keys on the sheet. We recommend you to not skip step. It is important for getting accurate merge results with excel diff tool.
3 Press Merge button on the Home Tab on the Ribbon. This command compares worksheets on the left and right panels, merges them into left file. So, you need to drop your master file into the left panel.
That’s all. In one click you get your worksheets consolidated with excel file compare tool!
Conclusion: xlCompare is a powerful tool to save your time for performing various spreadsheet consolidation tasks
Our team of developers works on improving the software every day. We bring new features and improve existing ones to give you the best excel compare tool.
Armed with xlCompare excel diff tool you are able to quickly perform the tasks you have on the daily basis.