The Data Import Process

To import data, open Integration Data- File Search. Select New (+) to create a new InFile.

Infile: As we saw earlier, an Infile is the parent "container" that aggregates data from one or many files. InFile fields include:

• InFileId: A unique name for the InFile
• Name: A descriptive name for the InFile. If the name is not specified, the InFile ID becomes the default name.
• PayoutTypeId: Pointer to the PayoutType record to which this record is associated. This field serves three purposes:
  • When executing the InEntity Posting process for a specific PayoutTypeID, only InFiles with the appropriate PayoutTypeID will be processed.
  • SQL Scripts and Batch Entity updates can be constrained to a specific PayoutType to ensure they aren’t unnecessarily executed for every Import/Post process.
  • During InFile/InEntity Posting, the PayoutTypeID is transferred from InFile to the resulting TranHead records. This serves to segment the transactions into distinct groups. Once a TranHead has been assigned to a particular PayoutType it can not be changed to another PayoutType.
• Effective Date: Defaults to the system date when the InFile is created, but can be set to any date.
• Billing Month: Defaults to the Bill Month of the current Active Period, but can be set manually.
• Billing Year: Defaults to the Bill Year of the current Active Period, but can be set manually.
• 
• Statistics: During the import, posting and payout process, APMe updates the status of the records that have been imported and marks them as their status changes. Statuses include:
  • Ready: The number of records have been successfully imported into the staging tables and are available to be posted.
  • Skipped: This status is only relevant for Transactions. A transaction is marked skipped when 2 or more transactions are loaded in the same infile with the same InpKey. APMe will mark the duplicate transactions as skipped and generate a new transaction with the accumulated values of the duplicate transactions.
  • Computed: This status is only relevant for Transactions. This number represents the number of records in this InFile that have been computed and posted to BrokerHistory and the payout has not yet been Finalized.
  • Finalized:This status is only relevant for Transactions. This number represents the number of records in this InFile that have been computed and posted to BrokerHistory and the payout has been Finalized.
  • Hold: This status indicates the number of the records that have been marked to be excluded from processing.
  • Error: This status indicates the number of records that have failed validation during the last posting attempt.
• Import Request: The Import Process imports data into staging (InFile) tables. An Import Request is a process used to execute existing import formats. While an import format tells the system how to format source data, the import request process tells the system how to integrate it into the staging table correctly. The components of an Import Request are:
  • ImportFormat: The formatted imports that map the data and files into the system. The Import Request process calls these formatted files to import into the InFile tables.
  • Date Processed: Date stamp when the Import Request was initiated.
  • Time From: Time stamp when the Import Request was initiated.
  • System Status: Process status of the Import Request.
  • File Name:  Name of the imported file.
  • Acknowledgement Status: Import requests that end in error status must be acknowledged before they can be posted. File Sweep can be configured to automatically acknowledge errors to prevent disruption of automated process.
  • 

The import process includes two default validation checks.

1. Data Type:Prohibits importing alpha characters into a numeric field.
2. Data Length:Prohibits importing a field longer that specified in the database schema.

Additional validations can be manually added in the Entity Edit functionality or in the Import Format field mapping definition.

In the example in the Import Request section, the Import request ended in Error. Select the View Messages or View Log icons to reveal descriptions of the error.

In this case, the CustomerId value was 48 characters, but the InCustomer.CustomerId field has a limit of 30 characters. 

Hint

Avoid relying on errors in Import Requests to ensure data integrity, as errors cannot be resolved in the user interface. The record(s) in error will need to be identified/resolved and reimported in another Import Request. The best practice is to add validations as part of the post process so the errors can be corrected in the user interface if possible.