Excel importer errors and validation steps

To import TCRs into the TCR Tool, the predefined Excel or XML file structure can be used. In this way, a massive import of TCRs can be done at once. 

The following steps are necessary:

  • Click on “Document upload” (red marked-1)

  • Click the button near to the "File" field or in the "File" field itself.
  • A file browser window is opened. Use the file browser window to select the Excel template mentioned in the precondition.
  • The “Excel” file has been selected (red marked-2).
  • Click the “Validate” button to process the chosen file.
  • In case of errors, the TCR tool responds with a detailed error report (figure 19). The error report gives a detailed explanation of what is wrong and should be corrected before a next

  • After correcting all errors, start again from the very first step  (by clicking the “Document upload” button)
  • If no validation and/or consistency errors occur after the validation, the “Import” button will be available at the bottom of the report

 

  • Click on “Import” button for the recently uploaded and validated file.
  • All the imported TCRs will be presented in the “All TCRs” under the “Task” group of functions
  • The recently imported TCRs are displayed in the “Processed & Released” list by records in bold.

In case you try to import the file with data that is already imported into the TCR tool, the tool will give you warnings for each of the records that this data already exists

If you need to make some modifications to already imported record/s and import these records again in the tool, it is possible. It is needed to do the necessary modification in the excel file and after that, you have to update the “Last updated” cell with a new date in each row in which you did changes.

 

Details on validation

After processing the document import, the user will be informed via a validation report if any errors occurred. Basically, the upload will validate against:

  • the user’s authorisation to create a new TCR depending on the provisioned transactional data
  • if there are any mandatory attributes not provided in the import template
  • if there are consistency issues with related master data

An imported TCR is created in the state imported.

An import of TCRs from an Excel File will be done in two basic steps:

  1. Validation: Scan the Excel file and check data consistency. 

    A report will be generated which contains a list of identified errors and warnings.
    If the report contains errors, the user must correct the data in the import file (either by changing the data in a national system that produces the import file or in the file directly). No data is created in the system. The updated file shall be validated as long as the report contains no errors.

  2. Import: When the validation report contains no errors, the TCRs defined in the file are created in the system. A workflow is started for each imported TCR.

The import-validation parses through all TCRs in the Excel file row by row and performs some checks for each line. The result of the checks is written in the validation report. The following validation-phases are performed for each row (=TCR) in the given order:

Syntactic Checks: all columns of the Excel file are checked to comply with the allowed type/values. This check will e.g. identify if a numeric field contains other characters than [0-9].

Semantic Checks: all columns of the Excel file are checked to comply with the defined constraints and that the mandatory fields are not empty

Checks against existing data: all rows are checked as illustrated in the following figure. During this phase, each TCR in the import will be checked one by one. The currently checked TCR is called TCR-I in the illustration. The goal is to check which import mode shall be applied to the TCR-I. Possible modes are:

* NEW: no TCR exists in the DB with the same ID as TCR-I

* UPDATE: there exists a TCR-DB in the database with the same ID as TCR-I. This TCR-DB will be updated according to the values of the TCR-I

* CANCEL: there exists a TCR-DB in the database with the same ID as TCR-I. This TCR-DB will be canceled

* IGNORE: the TCR-I will not be imported

Furthermore, the check identifies conflicting situations by raising an ERROR for TCR-I, e.g. if a TCR-DB shall be updated which is not editable by the importing IM.

Groups audience