Defining an import specification

An import specification defines the rules for reading an import file.

About this task

You can choose to run a debit/credit reconciliation during the data import. The reconciliation is run for the accounts found in the file on the total debit/credit and the difference in the file will be on the total extended dimensions levels. You must designate an account for positive difference and an account for negative difference. Accounts selected for both positive and negative difference cannot be accounts analyzed by extended dimensions. You can see a report that shows the calculated differences and amounts booked in the import log.


  1. On the Import specifications tab, click New Import Specification.
  2. In the Define import specification window, type or select the following fields:
    Name of the import specification
    Description of the import specification
    File type
    File type can be .csv or .xlsx
    Field delimiter
    The field delimiter in the data file. Applies to .csv files only.
    Decimal separator
    The character used to separate data values in the import file. Values are ,or .
    Digit grouping
    Specify whether a comma is used to group digits in a numerical value.
    Start row
    Row number to start reading the file at.
    End row
    Row number to stop reading the file at. Specify when you want to import only a portion of the file. If left empty, the file will be read to the last row.
    Skip amount 0
    If this check box is selected, numerical values of zero are not imported.
  3. Click Save.
    The import specification is displayed in the Import specifications list. The red dot means that the specification is not complete or invalid.
  4. Click the name of the import specification.
  5. Under Fields, you must define the import specifications for Account, Actuality, Amount, Company, Period, up to four extended dimensions, Counter Company, Counter Dimension, Transaction Currency, Transaction Amount, and Comment fields. Select the check box beside field name to enable it.
    For each field, you can define the following items:
    The available operations depend on the field. For example, for alphanumeric fields, the operation can be Substring or Concatenate. For Amount and Transaction Amount, the operation can be Round or Divide. Select the operation and then click the + to specify the arguments of the operation.
    Column index
    Column number in the file that contains the value.
    Lookup table
    Applies to Account, Company, Counter Company, and the four extended dimensions.
    If a lookup table is defined for the Cognos Controller object, select this check box and then select the lookup table from the list that displays.
    Note: Only lookup tables that you define appear in the list. If you want to use lookup tables that were defined by someone else, your administrator has to give you access to it. Invalid lookup tables do not appear in the list.
    Get from context
    Applies to Actuality, Company, and Period only.
    Select this check box to have Cognos Controller get the value from the context. The context is the project and company you selected earlier.
  6. Optional: If the import specification is for a full trial balance, then you can click Options and do the following steps:
    1. Select the Perform Reconciliation check box if you want to run a debit/credit reconciliation between accounts while data is being imported.
    2. In the Account for positive difference drop-down list, select the account to post a positive difference to after reconciliation.
    3. In the Account for negative difference drop-down list, select the account to post a negative difference to after reconciliation.
    4. To clear data from forms before the import, select the Clear data from forms before import check box and in the list of forms, select the forms to clear.
      The selected accounts are cleared even if the import file doesn't contain data for the accounts. If you do not select this check box and you import a new file for the same period as the one for which you have already imported values, only the values for the present accounts in the most recently loaded file are overwritten.
  7. Click Save.
    The import specification is displayed in the Import specifications list. If the specification is complete and valid, a green dot is displayed beside it.