Match tables with external mapping files

Use an external mapping file if you have to enter a large number of matches. This mapping file can contain information from your matching documents or your ETL (Extract, Transform, Load) tools.

You can match tables only or you can match tables and fields.

Requirements

The external file must be a text file and meet the following requirements:

  • Header in the first line is Source Table;Source Column;Target Table;Target Column.

  • The schema naming convention is schema.tablename. You can specify table names with or without schema.

  • If you want to match tables only, use asterisk syntax instead of column entries. For example: Source Table;*;Target Table;*. In this case, the Wizard auto-matches all fields in a table.

All inputs are case-sensitive.

Load the file

To load an external mapping file, follow the steps below:

  1. Click Load matches from file in the Match Method section.

Click Load matches from file

  1. In the subsequent dialog, select the file containing your matches and click Open.

  1. If you load matches from a file, the Wizard removes all existing matches. In the subsequent dialog, click Yes to confirm.

Confirm to overwrite existing matches

  1. The Wizard loads the selected file and automatically sets the Match Method from Auto to Manual.

    If you set the Match Method manually back to Auto, the Wizard goes back to automatic matching and replaces the matches from the loaded file with the automatic ones.

Change an existing field match

To change an existing field match, double-click the respective Source Table or Target Table in the top section, i.e. the one displaying all matched tables. In the subsequent dialog, select the field you want to use as your match and click OK.

Select a different table for a match

You can also add matches to the ones you have loaded from your mapping file (see chapter "Match tables manually").

The following example shows a multiple matching process with an external mapping file.

You have a source table named Address which you want to compare to a target table named Address.

The schema of both tables is Person.

Both tables contain several columns: AddressID, AddressLine1, AddressLine2, City, Postal Code, etc.

The goal of your test is to use the same table twice to match AddressID:

  • to match AddressLine1 with AddressLine1

  • to match AddressLine2 with AddressLine2

Create an external mapping file:

Example external mapping file

The Wizard goes through the lines from top to bottom.

  1. It matches AddressID from the source with AddressID from the target.

  2. Then it matches AddressLine1 from the source with AddressLine1 from the target.

  3. Once it gets to the second iteration of Person.Address;AddressID;Person.Address;AddressID, the Wizard recognizes that it has already found one such exact match.

  4. It creates a second match: AddressLine2 with AddressLine2.

Error log

Any errors, for example Table/Column not found or Invalid file format, result in an error message in the log file. The Wizard then proceeds with the next match in the file.

The Wizard displays the actual path to the log file in an error message once all matches are loaded.

The default path to the log file is C:\ProgramData\TRICENTIS\TOSCA Testsuite\ 7.0.0\logs\ToscaData Integrity\Matching errors - yyyyMMdd_HHmmss.txt.