Defining an import specification

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

1. To access the Import specification, on the Populate tab, click Import data, then click Import settings.
   The Import settings window opens.
2. Click the Specifications tab and click Create specification.
3. In the Create specification window, type or select the following fields:

   Name

   Name of the import specification

   Description

   Description of the import specification

   File type

   File type can be .csv or .xlsx

   Field delimiter (applies to .csv files only)

   The field delimiter in the data file. Applies to .csv files only.

   Decimal separator (applies to .csv files only)

   The character used to separate data values in the import file. Values are ,or .

   Digit grouping (applies to .csv files only)

   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.

   Looping by period

   If this check box is selected, the system will loop through a predefined range of data columns and import them.

4. Click Create.
   The import specification is displayed in the Specifications list. The red dot means that the specification is not complete or invalid.
5. Click the name of the import specification.
6. Under Fields, you must scroll down and 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.
   Note: If the Looping by period is selected, the Actuality and Period selection will be available only during the run time.
   Note: If the Looping by period is selected, the Amount field will have two new sub-fields:

   • Column index start
   • Column index end

   The number of columns that you can add depends on what you have specified in the No of Periods in One year field on the General Configuration > General 1 tab.
   For each field, you can define the following items:

   Operations

   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, Counter Dimension, and the four extended dimensions.

   If a lookup table is defined for the 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 Controller get the value from the context. The context is the project and company you selected earlier.

7. Optional: If the import specification is for a full trial balance, then in Options section 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.
8. Click Save.
   The import specification is displayed in the Specifications list. If the specification is complete and valid, a green dot is displayed beside it.