User Manual - Data Import

Data Import

tms-Monitor provides a Data Import module as a quick way of getting data from other systems into your tms-Monitor database.

To protect your system and the integrity of your database, data to be imported must conform exactly to certain formatting rules:

1. Data to be imported must be contained in a text file with data values separated by a comma (known as a *.csv file).
2. Data fields in each of the *.csv files must be listed in the exact order specified in the import templates (sample import files). If your system has no data matching that field, you may leave the value between the corresponding commas blank.
3. NOTE: Data must be imported in a certain order. Clients must be imported before Notes, Identification or Transactions can be imported. Likewise, Transactions must be imported before their Proxy / Third Party details can be added.

During import, errors encountered are logged in the Import errors log file. A link to the error log will be provided during import. If any errors are reported, read the error log file, locate lines with errors and make the corrections in the import file before attempting import again.

Template Files

Data to be imported into tms-Monitor must be split into 5 separate comma delimited text files (*.csv) . Click on each file type below to download a sample template file.

1. 1 - Client Data
2. 2 - Client Identification History
3. 3 - Client Notes
4. 4 - Transactions
5. 5 - Transaction 3rd Party Details

Save a copy to your local hard drive - one for each of the template files you require. Using MS Excel, you can easily fill in the columns of data with data exported from an external system. During the import process, you will select these files one at a time to import data into tms-Monitor.

NOTE: Please follow the data rules that apply to each field in the template.

Order of Import

Data must be imported in a certain order.

• Clients must be imported before Notes, Identification or Transactions can be imported.
• Likewise, Transactions must be imported before their Proxy / Third Party details can be added.

1. Client Data
2. Client Identification History
3. Client Notes History
4. Transactions
5. Transaction 3rd Party Details - please enter one row per transaction.

Import Data Rules

Client Number

• Unique numeric identifier for each client in the external system;
• Numeric values only - no letters or special characters;
• 25 digits or less;
• Unique for each client or transaction being imported. Rows with dupliate ID's will not be imported;
• Include with each client row, AND in each client note, Identification, and Transaction record in order to relate them back to the Client record.

Dates

• Numeric values only, separated by dashes (-);
• Must be in YYYY-MM-DD format only;
• Do not include hours, minutes or seconds in dates for datestamp type fields;
• In general, dates for DOB, Transaction Date, and Identification Expiry Dates are required fields and may not be blank.

Currency, Payment, and Identification Type Codes

• Before attempting Client ID History data import, Identification Types in your import file must be replaced with the matching Identification type CODES from the tms-Monitor system.
• Before attempting transaction data import, transaction currency and payment types in your import file must be replaced with the matching currency or payment type CODES from the tms-Monitor system.
• List of current CODES can be found on the System Data Settings page;

True/False

• True/False fields are fields containing Yes/No, True/False, or 1/0 values;
• True/False fields may be left blank if your external system does not track it;
• Examples of True/False fields in the database:
  • Client Flagged as suspicious;
  • Checks Allowed;
  • Transactions Flagged as suspicious;
  • Transaction is for a 3rd Party;

Client Risk Level

• The import routine expects the numeral 1 for Low Risk, 2 for Medium Risk and 3 for High Risk clients.
• Before importing, replace your external Client Risk field with one of these values.

Invalid Character

There are a number of characters that may NOT be included in data entry and data import values. You will be warned to remove these characters when importing fields containing this data. Check the Error Log file for details, should any errors occur.

The following are considered to be invalid characters: % / \ ^ < > =

Bugs and feature requests

Have a bug or a feature request? Please open a new issue.