Import data file structures

On the Import Data File Structures page, you have the ability to define the specific structures of your import file, which allows you to import crucial information such as account details, company information, flow data, and dimension data from external files into Intercompany Management.

To access the Import Data File Structures page, click Import of Data > Import Data File Structures.

On the left-side panel is a list of all the available import file structures. The left-side panel contains the Code and Name of the Import structure, along with the Company code to which the structure is linked. 

 Note:  If the Global option is selected, all users will be able to use the selected import structure. Otherwise, only users with access rights on the linked company will be able to use it.

The right-side panel displays the detailed parameters of a selected import file structure.

Create an import data file structure

To create new import structure, you do the following:

  1. Define the file properties that can be used to identify the import file structure on the top right of the page.

  2. Specify how you want the data to be handled during import in the Options tab.

  3. Match the position of data in the application to that in the data in the Columns tab.

  4. Optionally, enable any import rules that apply in the Import rules tab.

  5. Optionally, select the default mapping tables to use with the import file structure in the Mapping tables tab.

Define the file properties

The file properties section describes how the import module handles the data in the import file:

  1. Click .

  2. Enter a Code and Description of the import file in their respective fields.

  3. If you want the import file structure to be available for use for other companies, select . Otherwise, the file structure will be available only for the company.

  4. Type of Data - here, you specify the nature of the intercompany data to import: either closing amounts or transaction amounts.

     Note:  This data input type is defined by the administrator in the admin mode and cannot be changed by the user.

  5. Type of Amount - here, indicate whether the imported figures are Net Amounts (all amounts are in one column) or Debit/Credit amounts (Debit amounts are in different columns from Credit amounts).

  6. Scale of Amounts - specify the scale of value used in the import file (e.g. x1=units, x1000=thousands, etc).

  7. Number Format - specify the format of the amounts to import.

  8. Signed Amounts - if the amounts in the import file are signed (+ for the Debtor amounts, – for the Credit amounts), select this option. This option can only be selected in combination with Net Amounts.

  9. Negative Charges - only use this option to upload the charge amounts with negative signs (inverses the sign convention). This option can only be selected in combination with Net Amounts.

  10. Round amounts - select to use rounding to round up the figures during import.

  11. Default Date -  selecting this means that the period date will be used as the document date (to be used when no document date is available in the file to import).

  12. File Type -  specify the file format of the import file. You can import XLS (Excel) or CSV (Comma Separated Value) files.

    • The Hub type allows you to create a specific handling for data put into the HUB by some automation process, like using some mapping table or import rules.

    • The Odata type enables you to create a process that will get data from a remote web application over the internet, making use of the Odata protocol.

       Note:   Depending on the selected file type, you additional information like separators (for CSV), package id (for Hub), etc. , may be required.

  13. Proceed to the Options tab

Options tab

The options tab allows you to specify how the system processes the import data with the existing data during the import process.

  1. Select one of these options:

    • Replace all existing data: deletes ALL existing data of the imported company for the current period and replaces deleted data with the one contained in the file.

    • Replace imported accounts: deletes the data of only the imported accounts of the imported company for the current period and replaces it with the data contained in the file.

    • Add to existing: adds the imported data to the existing ones.

  2. Proceed to the Columns tab.

Columns tab

The Columns tab is where you indicate the column in which you find relevant data.

  1. Simply match the column to the field name (e.g. Column 2 for Company ID, Column 3 for Account Number, etc.).

  2. You now have these options:

    • If you want to enable import rules for this file structure, proceed to the Import rules tab. 

    • If you want to select default mapping tables for the data import (Companies, Partners, Account numbers. Negative Account), go to the Mapping tables.

    • If you want to save the the import file structure, click to save the import file structure. If successfully saved, the new import file structure is listed on the left-side panel.

Import rules tab

An import rule allows you to transform the imported information. Typical transformations include:

  • Removing rows that are not useful for the transfer of data

  • Changing the content of some fields based on some information

  • Adding content based on some other information

 Note:   For security reasons, only the table used for the import is available.

When you select , the (script) editor area will be enabled, allowing you to write /edit (with the right permission) the transformation script using SQL language. The import /export process will then also know that there is a script to apply on the imported /exported data and will do so.

If the rule is not enabled, even when there is one in the editor, the import /export process will not use it.

  1. To activate the data transformations, select .

  2. Proceed to Mapping tables tab select the mapping tables to use for the import.

Here is an example of some sample queries displayed by default:

/*

-- Example queries

select [LineNumber], [tmp_CompanyCode],[tmp_PartnerCompanyCode],[tmp_Account],[tmp_TransactionCurrencyCode],[tmp_DocumentReference],[tmp_DocumentDate]

,[tmp_TransactionNetAmount],[tmp_TransactionDebitAmount],[tmp_TransactionCreditAmount],[tmp_AccountingNetAmount],[tmp_AccountingDebitAmount],[tmp_AccountingCreditAmount]

,[tmp_MoreInfo1],[tmp_MoreInfo2],[tmp_MoreInfo3],[tmp_MoreInfo4],[tmp_MoreInfo5],[tmp_MoreInfo6],[tmp_MoreInfo7],[tmp_MoreInfo8]

,[tmp_MoreInfo9],[tmp_MoreInfo10],[tmp_MoreInfo11],[tmp_MoreInfo12],[tmp_MoreInfo13],[tmp_MoreInfo14],[tmp_MoreInfo15]

from $import$

where [tmp_CompanyCode] = '1'

update $import$ set [tmp_CompanyCode] = '1' and [tmp_AccountingNetAmount] = [tmp_AccountingNetAmount] / 2 where [tmp_Account] = '200000'

insert into $import$ ( [tmp_CompanyCode],[tmp_PartnerCompanyCode],[tmp_Account],[tmp_AccountingNetAmount]) values ('1','2','200000','123.56')

delete from $import$ where [tmp_Account] = 'tmp200000'

*/

 

Mapping tables tab

The Mapping tables tab allows you to select the default mapping tables to be used by each type of data.

  1. From the drop-down field of Companies, Partners, Account numbers, Negative Account, select an applicable mapping table.

  2. After you have successfully defined the different sections of the import files structure, click to save the import file structure. If successfully saved, the new import file structure is listed on the left-side panel.

     Note:  If there are any issues with the setup, such errors and where they occur are displayed. Rectify the errors and try to save again.