Import/Export Formats
Table of Contents
- 1 Introduction
- 2 Import/Export Format Ident (IEF)
- 3 Fields for Import/Export Formats (IEFDEF)
- 4 Allocation of Import Fields to Formats (IEFFEL)
- 5 Examples
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 IMPORT 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: IEF) and is stored in three database tables, each with an overview application and a single record application:
- IEF
- means the header of an import/export format. Along with the format name, it sets common properties.
- IEFDEF
- 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.
- IEFFEL
- 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 (IEF)
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 '#TXT'.
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 'SPIDEF' 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 (IEF)
The object type and the format ID are mandatory entry fields in the selection area of the overview 'IEF', 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 (IEFFEL) with keys of the selected format definition
- Fields for IDL formats
- Calling the import/export format (IEFDEF) 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 'IEJOB'). Therefore, this format can only be specified in the default format '#DB'.
- 'TXT' 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 '#TXT' 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 ('KTOSAL', 'ICKTOSAL', 'ANLBEW'). 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 (TAB) 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 UMS) 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 'TXT' 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 '#TXT'.
- 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 'TXT' 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 '#TXT'.
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:
VAR | Bezeichnung |
---|---|
%KTK | Konzern/Teilkonzern |
%GES | Gesellschaft |
%UBR | Geschäftsbereich |
%ABR | Periode im Format yyyyMM |
%FAC | Datenart |
VAR | Description |
---|---|
%KTK | Group/sub-group |
%GES | Company |
%UBR | Business unit |
%ABR | Period in Format yyyyMM |
%FAC | Fact |
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)
- KTOSAL
- ==> C:\IDL\BATCH\KTOSAL.TXT (add import path and suffix, as no specification)
- %GES\%ABR\KTOSAL_%FAC
- ==> C:\IDL\BATCH\001\200712\KTOSAL_I4.TXT (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:
Format-ID: | KTK | GES | UBR | ABR | FAC |
---|---|---|---|---|---|
ANLBEW | X | X | X | X | X |
ANLOBJ | X | X | X | X | X |
BEL | X | X | X | X | X |
BUCH | X | X | X | X | X |
CNTSAL | X | X | X | X | X |
GESGES | X | X | X | X | X |
GESUBR | X | X | X | X | X |
ICANLBEW | X | X | X | X | X |
ICANLOBJ | X | X | X | X | X |
ICBEW | X | X | X | X | |
ICKONV | X | X | X | X | X |
ICKTOSAL | X | X | X | X | X |
KAPBEW | X | X | X | X | X |
KONBEL | X | X | X | ||
KONBUCH | X | X | X | ||
KST | X | ||||
KTK | |||||
KTKGES | X | X | X | ||
KTO | X | ||||
KTOSAL | X | X | X | X | X |
KTOUAW | X | X | X | X | |
RUEBEW | X | X | X | X | X |
SPIBEW | X | X | X | X | X |
3 Fields for Import/Export Formats (IEFDEF)
3.1 Brief Description
The fields for Import/Export Formats (IEFDEF) 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 IEF 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 IMPORT application, specification "#KEY" 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 '#TXT' 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 (IEFFEL)
4.1 Brief Description
The "Match Import fields to Formats" application (IEFFEL) 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 IEF 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 IEFDEF application and partly in the IEFFEL 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 IEFDEF
- Description, Column header
- Texts of the field in question
- Start Position or Column No.
- Position of the field in the import file (for TXT 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 TXT formats with fixed field length)
- E
- IEF field input type according to table IEFDEF (see above)
- PN
- IEF parameter activation according to table IEFDEF (see above)
- International
- Initialization flag for optional attributes according to table IEFDEF (see above)
- IE Data Type
- Import/export data type according to table IEFDEF (see above)
- F
- Field format additional specification according to table IEFDEF (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), '#KEY' 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 IEFDEF (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 IEFDEF 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 IEFDEF 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:
Formatteil | Beschreibung |
---|---|
yyyy | Jahr, immer vierstellig |
MM | Monat, immer zweistellig |
M | Monat, ein- oder zweistellig |
dd | Tag, immer zweistellig |
d | Tag, ein- oder zweistellig |
HH | Stunde 00 bis 23, immer zweistellig |
H | Stunde 0 bis 23, ein- oder zweistellig |
hh | Stunde 01 bis 12, immer zweistellig |
h | Stunde 1 bis 12, ein- oder zweistellig |
mm | Minute, immer zweistellig |
a | AM/PM-Marker, ein- oder zweistellig |
Format Part | Description |
---|---|
yyyy | Year, always in four digits |
MM | Month, always in two digits |
M | Month, single or double-digit |
dd | Day, always in double digits |
d | Day, single or double-digit |
HH | Hour 00 to 23, always in double digits |
H | Hour 0 to 23, single or double-digit |
hh | Hour 01 to 12, always in double digits |
h | Hour 1 to 12, single or double-digit |
mm | Minute, always in two digits |
a | AM/PM marker, single or double-digit |
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 IEF:
Spalte | Wert |
---|---|
Import/Export Objekttyp | KTOSAL |
Import/Export Format-ID | SUMSAL-117 |
Bezeichnung | Summensaldenliste von Ges. 117 |
Kurztext | SumSal 117 |
Formattyp | TXT |
Separator | ; |
Default-Dateiname | D:\IDL\import\von_117\SUMSAL.csv |
Fixe Anzahl Kommentarzeilen | 1 (für Tabellenüberschrift) |
Column | Value |
---|---|
Import/Export Object Type | KTOSAL |
Import/Export Format ID | SUMSAL-117 |
Description | Total balances list for segment 117 |
Short Text | Sum 117 |
Format Type | TXT |
Separator | ; |
Default File Name | D:\IDL\import\von_117\SUMSAL.csv |
Fixed Number of Comment Lines | 1 (for table heading) |
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 IEFFEL:
Feld ID | Spaltennr. | Default-Wert |
---|---|---|
KON / K003-K010-KTOM | 1 | |
KON / K003-WERT-LW | 2 | |
KON / K003-GES | #KEY | |
KON / K003-K011-FAC | #KEY | |
KON / K003-ABR-MON-JAHR | #KEY |
Field ID | Column No. | Default Value |
---|---|---|
KON / K003-K010-KTOM | 1 | |
KON / K003-WERT-LW | 2 | |
KON / K003-GES | #KEY | |
KON / K003-K011-FAC | #KEY | |
KON / K003-ABR-MON-JAHR | #KEY |
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 IMPORT 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.