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.
- On the Import specifications tab, click New Import Specification.
- In the Define import specification window, type or select the
- 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
- 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.
- Click Save. The import specification is displayed in the Import specifications list. The red dot means that the specification is not complete or invalid.
- Click the name of the import specification.
- 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
+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.
- Optional: If the import specification is for a full trial balance, then you
can click Options and do the following steps:
- Select the Perform Reconciliation check box if you want to run a debit/credit reconciliation between accounts while data is being imported.
- In the Account for positive difference drop-down list, select the account to post a positive difference to after reconciliation.
- In the Account for negative difference drop-down list, select the account to post a negative difference to after reconciliation.
- 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.
- 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.