Define Import Specifications using the Wizard

The Import Specification Wizard helps you, step-by-step, to define a basic import specification for importing external data files.

If you want to define a more complex import specification, use the Define Import Specification function.

Procedure

  1. On the Transfer menu, click External Data/Import Specification Wizard. The Import Specification Wizard window opens.
  2. To create a new specification, click New and enter a name in the Specification text box. If you want to change an existing specification, click the Show Valid Choices button and select an existing name from the Specification list box.
  3. Click the Show Valid Choices button next to the File text box and select the file you want to import and click OK. The path and file name are displayed in the text box and the content of the import file is displayed in the Import File window.
  4. In the General list box, click Fixed Format if the import file contains data in static positions.
  5. Click Delimited File if the import file contains data separated by a specific character, such as comma, semicolon, or tab. Enter the type of separator in the Separator text box. Note that you sometimes have to use trigraph symbols to describe the separator character, e.g. if the file is tab separated.
  6. Enter the Digit Grouping Symbol and the Decimal Symbol in the these two text boxes. If a value in the import file has the format 8.233,25, the grouping symbol is a period (.) and the decimal symbol is comma (,). Note that in comma delimited files, it is not possible to allow a digit grouping symbol to be a comma (,). Otherwise, the import will treat 12,453 as two separate items of information.
  7. In the Number of Times to Scan the Data File text box, enter the number of times the import file should be read. By default this is set to one but, for example, if the import file contains 12 monthly values on one row, the file should be read 12 times.
  8. Select the Copy Accounts According to CBA option if the imported value should be copied to a detail account, which is specified in the Reconciliation Between Accounts text boxes in the Define Account Structure window. Reconciliation rule + (Reconciliation for the same Period/Actuality +) and - (Reconciliation for the same Period/Actuality -) for account 1 will be applied.
  9. Select the Test Run option to import data to a test database. The result of a test run can only be checked by printing the test run report. See Log Reports. It is best practice to tick test run while proving that the specification will import data correctly.
  10. Select the Perform Reconciliation option if a reconciliation should be performed before importing the values from the external file. If you click the plus sign, the Account list box appears. Enter the account to which a possible difference should be booked. The balance control includes all accounts of types A, L, E, I and C.
  11. Click Font to change the font and font size of the import file window contents.
  12. In the Fields list box, the first field for entry, Period, is displayed.
  13. If you have defined that the file should be scanned more than once, the Dependent by current loop option appears. Select this option if the current field information is found in different positions for each loop. In the Dependent by Current Loop box also enter the loop number for which you are defining fields. You need to define the field information position for each loop.
  14. In the Field Source list, select the relevant option for where the field information is found in the import file.
    • Fixed Position: Select this option if the information is found in a fixed position on one row. Highlight the field information in the Import File window.
    • Fixed Item: Select this option if the information is found between two specific delimiters on one row. Highlight the field info in the Import File window by double-clicking between the delimiters.
    • Repeated on Each Row: Select this option if the information is found in the same position on all rows. Highlight the first occurrence of the field information in the file Import File window. Typically, the account and, or the amount would be repeated on each row.
    • Dependent by Current Loop: Select this option if the file will be read several times and the field information is in different positions for each loop. Enter the number of the loop in the text box and highlight the field information for each loop, one loop at a time.
    • Ask Question at Runtime: Select this option if the user should give the information at the time of import and enter the message to be displayed in the dialog box at runtime.
    • File Name: Select this option if the information is found in the file name and highlight the part of the name that contains the information.
    • File Extension: Select this option if the information is found in the file extension and highlight the part of the name that contains the information.
    • Static Value: Select this option if the information always is the same and enter the static information in the Static Value text box.
    • Get Local Currency Code from the Company Register: Select this option if the currency information is not included in the import file, but should be collected from the company structure.
  15. Click Next to define the next field. Repeat steps 13-14.
  16. While defining the Amount field, the Multiply by and Divide by value options are activated. Select the Multiply by option and select the With Account Factor option to multiply the imported amount with the factor specified in the lookup table for accounts, or select the With a Static Value option and enter the factor to be used in the text box. Select the Divide with the supplied Value option and enter the value to divide the amount by.
  17. Click the Create and Connect to a Lookup Table button to open the Define Lookup Tables window, where you can define new lookup tables.
  18. Click the Connect to an Existing Lookup Table button to open the Select lookup table dialog box. Select the lookup table you want to use to convert the imported data into the structure of IBM® Cognos Controller and click OK.
  19. Click Disconnect the Lookup Table from the Field to disconnect a lookup table previously connected to the field you are currently defining.
  20. Click the Create an Operation button if you want to open the Define Operations for the Field window to set up conversion rules and formulas for specific fields. For more information, please see the Available Operations.
  21. Click the Check button to validate the defined fields. Any missing definitions are listed in a log report window.
  22. Click Save. A log report appears if there are any missing or uncompleted definitions.