Importing Pre-mapped Transactions for Reconciliation Compliance

You can import transactions into a reconciliation from a flat file. For example, you could export 10 invoices for Prepaid Expenses for July 2016 to a flat file from your source system. You import the transactions into the reconciliation for Prepaid expenses for July 2016 in Reconciliation Compliance.

File Format
  • Transaction import files are comma-separated value (.csv) files.
  • You may import up to one million transactions.
  • Separate rows are used to define each transaction. The first row contains the column headings that identify the data in the columns, for example, Transaction code. Required columns are Transaction Code and Open Date.
  • You must also specify the amount and currency for any enabled currency buckets that are not subject to currency translation. The fields must be labeled Amount and Amount Currency, with a number to indicate the currency bucket. For example, Amount1 and Amount Currency1 specify the amount and currency for the first currency bucket.

    Note:

    You do not need to include amount or currency for translated buckets.

  • If you do not import these values, the amounts are calculated using the default currency codes.

  • If you specify a currency but not an amount, the amount is calculated using the currency code provided.

  • If you specify an amount and a currency code, the amount is compared to the calculated translated amount, and if it differs, the amount is marked as overridden.

  • The backslash "\" is a special character in file imports. The system reads the next character after a backslash "as is". In order for data containing a backslash to load properly from the .csv file, you must use a double backslash \\.
Importing Transactions

To import transactions from a file:

  1. From the Home card, select Reconciliations, and then select a reconciliation with the status Open with Preparer for which you are the preparer. The Reconciliation actions dialog is available to Administrators, Power Users, and Users.

    Note:

    You may also access the option to Import Pre-Mapped Data from the Worklist and you can access this option while working within a single reconciliation.
  2. Select Actions, and then Import Pre-mapped Data, and then Import Pre-mapped Transactions.
    example of import pre-mapped transactions dialog

    Note:

    Administrators can import transactions for all reconciliations, Power Users can import for reconciliations included within their security profile and Users can import for reconciliations where the user is assigned the Preparer function. Note that if you import transactions that were previously imported, the transactions will be replaced by the new import but limited to the scope based on your user security profile.

    Note:

    When using the Replace All option during an import of transactions, existing pre-mapped transactions are not deleted. A column indicates how the transaction was created.

  3. Browse to the flat file of transactions you want to add. This is an example of a transactions flat file.

    Note:

    If you import transactions to a single reconciliation, the Reconciliation Account ID field is not needed in the flat file.

    example of a transaction flat file

    Note:

    If you have a URL attachment, you must add the following columns to your flat file with # representing a number:
    • Attachment Name#
    • Attachment Type#
    • Attachment URL#
    • Attachment Doc ID#
  4. Select the type of transaction: Explained Balance, Adjustment to Source System, or Adjustment to Subsystem.

  5. Select a Date Format from the drop down list of allowed date formats. Date formats are not translated. By default, the date format is set to the locale date format of the exported file location.

    For example:

    • MM/dd/yyyy

    • dd/MM/yyyy

    • dd-MMM-yy

    • MMM d, yyyy

  6. Select a file delimiter for the import file (for example, comma or tab). Or, choose Other to specify any single character as the delimiter.
  7. Click Import.

Pre-mapped transaction imports are processed immediately, and the user is unable to leave the page until the import has completed. Results of the import are presented on the same page.