Tutorials & Examples Web-help Blog

Compare two Excel sheets with EasyMorph


#1

Here is a utility EasyMorph project which compares data between two Excel sheets from different files (or from the same file).

CompareExcelSheets.morph (25.3 KB)

You can run it with “Call another project” action. Mode of the calling action should be set to Return table and you’ll have to specify file and sheet names for both files as parameters of the called project:

This project will compare column names from both sheets. And if column names are equal, the project will compare all the following rows in the sheets.

The project will return a single R column. The column will be empty when both sheets have the same column and data. Otherwise, the column will contain a single cell with a short description of found differences like:

Numbers of columns in the specified files (7 and 5) don’t match. Columns in the first file: [Date],[Open],[High],[Low],[Close],[Volume],[Ad.Close]. Columns in the second file: [Open],[High],[Low],[Close],[Volume]

Number of rows in the second file (1) is different from the number of rows in the first file (26)

Only 23 rows of 26 are matching

You can examine the actual differences in the sheets data by choosing “Mismatching rows” table in the Mode option of “Call another project” action. The returned dataset will contain mismatching rows from both sheets with an additional RowNo column.

You can make this project to compare data between any two data sources, supported by EasyMorph, just by replacing “Import Excel spreadsheet” action with any other import action and modifying parameters list accordingly.


#2

Hi,

Tried the downloaded file, and it seems to have an error on ‘R’ calculation in table ‘Compare matching rows number’ on tab ‘Compare data’.
It came with following error:

Error: Operator or separator is missing after Not matchi…
Source: Action “Calculate new column(s)” in table “Compare matching rows number” in project

Comma separator is missing in line 12:

11 if([MatchingRowsNumber] = 0,
12 “Not matching rows found”
13 “Only " & [MatchingRowsNumber] & " rows of " & [ActualRowsNumber] & " are matching”)
14 )

Should be:

11 if([MatchingRowsNumber] = 0,
12 “Not matching rows found”,
13 “Only " & [MatchingRowsNumber] & " rows of " & [ActualRowsNumber] & " are matching”)
14 )


#3

Hi Christoffer,

Thank you for the fix. I have updated the project.