Import/Export Formats

1 Introduction

Exports from IDL.KONSIS as well as imports to IDL.KONSIS serve as interfaces to other information systems as well as for the transmission of data between two IDL.KONSIS installations. These functions are available for a large part of the IDL.KONSIS data (see Actions/Follow-up Applications of the IMPT application).

Both external and internal interfaces must be defined to enable correct data transfer. This definition is used in IDL.KONSIS as an import/export format (in short: IEFID) and is stored in three database tables, each with an overview application and a single record application:

IEFID

means the header of an import/export format. Along with the format name, it sets common properties.

IEFLD

provides an overview of which fields per object type can or must be contained within a format definition, and sets some format-independent properties of the fields.

ALLCFLD

defines which of these fields are contained in a format and which format-specific properties they have.

Both when exporting data from IDL.KONSIS and when importing data to IDL.KONSIS, the respective import/export format can be specified.

2 Import/Export Format Ident (IEFID)

2.1 Key

The key of an import/export format consists of two parts:

• Object type is the data set to which this format applies. The object type selection list is specified by IDL and includes the data sets for which an import function is currently available (see above).
• The format ID distinguishes between different formats for each type of object. There are formats defined by IDL, recognizable by the leading '#', but also customized formats can be defined by a separate format ID. The default format provided by IDL is '#DSCTXT'.

The key of an object type is usually the same as the menu ID of the associated care application. There are exceptions in the area of master data, as the new applications (...DEF) are usually Manage data of different object types. For example, the application 'TRNDEVDFN' includes the maintenance of the object types Transaction development (SPI), transaction development area (SBE), mirror columns (SSP), posting key groups (BSG) and posting key (BSL). The object types are then named according to the previous individual applications.

The formats starting with '#' in the format ID are maintained exclusively by IDL and delivered with the metadata upon version change. They may not be changed by the customer.

2.2 Overview (IEFID)

The object type and the format ID are mandatory entry fields in the Selection area of the overview 'IEFID', whereby ambiguous entries (partial key+'%') are also permitted. For example, both fields are pre-populated with '%' (all data) when the application is first called. In addition, the language is an optional input field in the Selection area, which can be used to control the language of the descriptions displayed in the table.

The table shows the format IDs selected accordingly with their properties. The information about the last change only refers to changes made to these records themselves, but not to changes made to the child format specifications.

The toolbar and the context menu provide the usual options for inserting, editing and delete an Import/Export Format ID and for display, editing and delete a Help text. The following follow-up applications are also available in the context menu:

Allocation of IDL fields to formats

Call up the overview for assigning IDL fields to formats (ALLCFLD) with keys of the selected format definition

Fields for IDL formats

Calling the import/export format (IEFLD) fields overview with keys of the selected format definition

2.3 Single-Record Application (IEFE)

In the single-record application 'IEFE', the following properties of a format can be set:

Description and short text

Enable language-dependent expressions of the format

Format

distinguishes between the following basic types of import/export formats:

• 'TABLE' indicates that the interface is in the form of a database table. These tables are specified by IDL in the IDL.KONSIS database (see application 'IEJBCN'). Therefore, this format can only be specified in the default format '#DB'.
• 'DSCTXT' describes formats that are available in the form of line-oriented files (one data set per line). These can be both fixed-length formats (as in the standard '#DSCTXT' format) and formats with specific separators between fields (CSV formats).
• 'XML' means formats in XML format. These are currently only supported for a few data types ('ACBAL', 'ICACBAL', 'FATRN'). The default format '#XML' is the default XML format used internally by IDL. Other XML formats, such as the format for the CODA interface (#XMLCODA format ID), must specify the transformation rules between these formats (xslt definition) in the associated Help text.

Separator

specifies the separator between the fields for CSV formats. To date, comma (,), semicolon (;), space (SPACE) and tab character (TBL) are allowed.

Masking character

specifies another special character with which fields are enclosed and thus interpreted as a field, even if they contain the separator character (e.g. in descriptions). Only the quotation mark (") is currently supported.

Invert Value

This field is not relevant at the moment.

Code page

Indicates a different character set for the input file.

Transfer group

Specifies an implementation group (application TRFGRP) that must be applied by default in connection with an export/import format.

Imp/Exp Table Object ID

Specifies the name of the IDL.KONSIS database table into which the data is written during import

Default file name

The default filename defines the startup data of the filename for both import and export in the respective dialogs, but can also be overridden there. For export, the file name can also be assigned variable components (see).

Import API Relevance

This specification is only relevant for IDL internally and may not be used for customized formats.

XML prefix

This field is only required for the XML formats defined by IDL.

Comment string

For a file in 'DSCTXT' format, you can specify which string at the beginning of a line identifies this line as a comment line to be ignored during import, e.g. '**' for the standard format '#DSCTXT'.

Fixed number of comment lines

This number specifies how many rows at the beginning of the file (e.g. table header) should be treated as comment lines for a file in 'DSCTXT' format and thus ignored during import.

Export Table Header / Export Comment Lines

These switches are only evaluated during export and control whether only the pure data rows are to be exported or also the table heading or the other rows (empty lines, totals rows, control values etc.).

Import sequence number

This information is particularly relevant for the export function of IDL.XLSLINK if the Excel file contains export references of different tables. This number is then used to decide the order in which the data will be imported in order to avoid errors due to missing references in the meantime. This information is only maintained with the standard format ID '#DSCTXT'.

The Invert and Code Page fields are not yet activated.

2.4 Placeholder in Default File Name

The following placeholders are currently available to make the path and/or file name more flexible:

For the following examples, "C:\IDL\BATCH\" is assumed for the import path in the options dialog:

C:\XYZ\Import_Salden.ABC

==> C:\XYZ\Import_Salden.ABC (no change given as drive and suffix and no placeholders)

ACBAL

==> C:\IDL\BATCH\ACBAL.DSCTXT (add import path and suffix, as no specification)

%CODT\%CLSDTE\KTOSAL_%FCT

==> C:\IDL\BATCH\001\200712\KTOSAL_I4.DSCTXT (if company = '001', period = '12.2007' and fact = 'I4')

The use of variables depends on the available parameters from the applications. The following table shows which variables are currently allowed for which format the import menu provides:

3 Fields for Import/Export Formats (IEFLD)

3.1 Brief Description

The fields for Import/Export Formats (IEFLD) application displays the fields that can be used for flexible import/export formats. This data is provided and maintained by IDL.

3.2 Selection Options

Selection is possible using the following fields

Object type

The possible data sets are specified here for selection.

Field ID

The first field describes whether it is a technical field from IDL.KONSIS ('KON') or a general field ('IAR'). Input options in the second field are valid key, partial key+% or %. The field names used here are also defined on the IDL side.

Translation object type

The selection shows the object types for which an implementation between external and internal key can be defined within an implementation group. Selecting by object type provides an overview of the places in which implementation groups can be used for import or export.

Language

Enables field names to be displayed in different languages.

3.3 Table Columns

The table displays the following columns:

Import/Export Object Type

See Selection options

Project ID, Field ID

See Selection options

Description, Column header, Short text

Texts of the field in question

IE Data Type

Means the technical data type of the field, e.g. CHAR (string), DATE (date) or DECIMAL (number with decimal separator)

F

The field format supplement specifies whether the letters of a field are automatically converted to uppercase.

E

The IEFID field input type indicates whether a field is a must (M) or optional (K).

PN

The parameter activation specifies whether the field can be set using a parameter specification (Selection area of the IMPT application, specification "#KYS" in a machine control) and which parameter is used to assign it.

International

The initialization indicator for optional attributes '*' indicates that the existing content of a field can be deleted by the '*' in the import record, while the empty value indicates that the previous content of the field is retained. Fields without the optional attribute initialization flag are set blank or deleted by specifying blank.

GUI Col ID

Internal IDL reference to identify the field to be issued in the IDL.KONSIS application

Type, Relaying

Type of implementation object (see Selection options), here supplemented by the implementation restriction, as they can be defined in the implementation groups

XML Path

Internal IDL statement: XML path must be entered if the default format '#DSCTXT' also has a format '#XML' defined.

Property

Internal IDL statement: Property must be entered if this format is used by the Import API. The format describes the field Help.

4 Allocation of Import Fields to Formats (ALLCFLD)

4.1 Brief Description

The "Match Import fields to Formats" application (ALLCFLD) displays all field mappings to a selected import/export format with their properties. The properties depend on the format type.

4.2 Selection Options

Selection is possible using the following fields

Format

The possible object types and format IDs as defined in the IEFID application are specified here for selection.

Language

Enables field names to be displayed in different languages.

4.3 Table Columns

The table displays the following columns, which are set partly in the IEFLD application and partly in the ALLCFLD application itself:

Field ID

Referenced field name

GUI Col ID

Internal IDL reference identifying the field to be issued in the IDL.KONSIS application according to Table IEFLD

Description, Column header

Texts of the field in question

Start Position or Column No.

Position of the field in the import file (for DSCTXT formats only): For fixed field lengths, this is the position at which the field starts (start position); for csv formats, it is used to determine the order of fields and is equal to the number of preceding separator minus 1 (column no.).

Length

Field length in the input file (only for DSCTXT formats with fixed field length)

E

IEFID field input type according to table IEFLD (see above)

PN

IEFID parameter activation according to table IEFLD (see above)

International

Initialization flag for optional attributes according to table IEFLD (see above)

IE Data Type

Import/export data type according to table IEFLD (see above)

F

Field format additional specification according to table IEFLD (see above)

Format String

Is used to interpret and convert dates and times into specific formats (e.g. "dd.MM.yyyy").

Default

Default value: Here you can specify a default value (e.g. text constant) if the source file does not provide a value. For the adoption of the defined parameters (see column PN), '#KYS' must be entered here. The priority is the value in the input file. Only if there is no value in the input file, the value entered here is adopted.

Type, Relaying

Implementation object type and conversion object constraint according to table IEFLD (see above)

4.4 Single-Record Application (IEFREN)

The single-record application included in the overview enables the properties displayed in the table to be maintained, unless they are already set by the IEFLD application. These are

• Starting position or column number
• Field length
• Factor (not currently used)
• Format string
• Default value

The field ID must be entered as the key. Only the fields specified in the IEFLD table for each type of object are available as fields, where field keys starting with "I" are reserved exclusively for the format '#DB' and therefore cannot be used for individual formats.

The format string is used to convert dates from the input file into the internal format. The individual format parts (see table) can be used with/without separators. Example: dd.MM.yyyy = default format for valid-off date. By specifying a format string from multiple zeros (e.g. "00000") can be specified that the associated numeric values (e.g. account numbers) are extended to the specified length (in example 5 digits) by leading zeros. This is useful, among other things, if alphanumeric keys defined in IDL.KONSIS are purely numerical in foreign systems. The format string can be used to specify the following strings:

5 Examples

5.1 Account Balances as a Simple List of Balances in Excel

A company returns its account balances in an Excel spreadsheet that contains only the Account No. and Amount columns. This Excel table can be saved in CSV format and used for import.

For this purpose, a format with the following attributes must be defined in IEFID:

Format ID, text descriptions and default file names are shown here with sample names and can be selected as desired. The following fields are assigned to this format in ALLCFLD:

This means that the import file contains only the account number and amount details in local currency as stated above. Further information on the resulting data sets in the database is as follows:

• The keys for company, fact, and period are specified as parameters at import (e.g. in the Selection area of the IMPT application). If necessary, a business unit can also be entered in the same way.
• The chart of accounts is determined by the company or the facts.
• The debit/credit indicator results from the account's balance sheet/income statement indicator.