Import/Export templates

This function defines the file formats used by the import and export functions for an object to integrate or extract data from the software database.

A file that can be used by the import module and generated by the export module is based on a file with one of the following structures:

• Flat file with fixed-length fields
• Flat file with separators between the fields (and records)
• XML file

The import/export module uses the notion of objects to update the database. An object is defined by a group of tables and screens, as well as the standard actions associated with the fields and the update. In addition, there is the guarantee that all the controls and actions necessary during the database update are carried out because a single description for the object is used to both generate the code relative to an online update and an update by import.

When an object only updates a single table, the import template describes the list of fields from the table to be integrated, remembering that a block of data from the file to be imported or exported contains the data for a record; When several tables are updated by an object (for example the header and the lines), several blocks of data will be found for an instance of the object to be integrated (for example a block defining the header and N blocks, one for each line).

It is also possible to import a table without associating the notion of an object with it. The empty object field is left for this purpose and the table and the fields concerned are entered in the grid in the second section. The import is then made without any control apart from those deduced from the formats associated with the data types of the fields in the table.

Prerequisites

Refer to the Implementation documentation.

Screen management

Header

An import/export template is identified by an alphanumeric code. Other than a title, two sections are used to define the technical characteristics of the template.

Header tab

This section defines the following general characteristics of the template:

• The data that must be exported
• The general structure of the file such as its format, coding, and data group definition
• The additional parameters

Fields tab

The different fields to be imported are defined in this grid. They are organized in groups identified by the Code column in which one of the codes defined in the indicator grid of the first section is found. The field can remain blank if no table has been defined.

This section contains the grid defining the detailed structure of the groups existing in the first section. Note that:

• It is not mandatory to define fields in all the groups. Some groups are simply technical groups that define links. For example, if the fields from the order header and the pay-by customer record are to be exported in a single group of data, you will need to define two groups (the first defining the orders, and the second associated with the customer with the appropriate link). However, only the lines associated with the second group are entered in the field section. These lines can include both the information extracted from the customer and the order header.
• It is mandatory to define for each group the position where the group separator is located in the case of a template that can be used for imports and with multiple groups.
• The blocks must be ordered sequentially. When a block exists for a lower level, it must follow the block to which it is linked.

Reports

By default, the following reports are associated with this function :

• PRTSCR : Screen print

This can be changed using a different setup.

Specific buttons

• Validation

  Click this button to validate your import/export template.

• Copy

  This button is used to copy the record definition from or to another folder.

  Block number 1

  Field OBJET

  Field CLES

  Block number 2

  From folder (field DOSORG)

  Use this field to define the folder from which the record will be copied. The possible syntaxes are described in the dedicated appendix.

  All folders (field TOUDOS)

  Use this option to copy the record to all the folders defined in the dictionary (ADOSSIER table of the current solution).

  To folder (field DOSDES)

  Use this field to define the folder to which the record will be copied. The possible syntaxes are described in the dedicated appendix.

• Export

  This button gives access to a screen in which it is possible to define the default values for the criteria to filter the exported data.

  • When the export is launched, the criteria are displayed and can be modified.
  • When a chain of exports is launched, the criteria are automatically applied without entry for each of the templates in which they have been defined.

  Range grid

  Field (field BNOM)

  This field defines the name of the field for which a range can be entered.

  First value (field BDEB)

  Enter start and end ranges for each field defined as an entered or not displayed criterion in the field grid. If an empty field is documented, ranges are considered to be unavailable. These ranges will be suggested by default during export.

  Final value (field BFIN)

  Criteria grid

  Tables (field FICHIER)

  The tables used in the import/export template are listed here to enable the filtering of the exported data.

  Criteria (field FLGEXP)

  This field defines a logical condition using the table fields. Only the lines meeting this condition will be exported.

Menu bar

Options / Export schema of a template

This function, which can be accessed when the export format is XML, is used to create an XSD file that describes the file structure created by the template. This file is created in the sub-directory following the directory where the folders are, in the application server:

• X3_PUB/FOLDER/GEN/ALL/WEBS (FOLDER being the name of the current folder)
• The file name is WWIMPTEMPLATE.xsd (TEMPLATE being the name of the import-export template)

This XSD file is used to define the data format to carry out a preliminary syntax validity check using ETL tools. The syntax obtained includes (besides standard headers) lines such as:

• Lines describing groups and lines. The following example is an ADI template and the CODE and NUMTAB fields with the ADI_CODE and ADI_NUMTAB tags):

<xs:complexType name="ADI">

<xs:sequence>

<xs:element name="ADI_NUMTAB" type="ADI_NUMTAB" minOccurs="1" maxOccurs="1"/><xs:element name="ADI_CODE" type="ADI_CODE" minOccurs="0" maxOccurs="1"/>...

</xs:sequence>

</xs:complexType>

• Lines describing the data types:

<xs:simpleType name="ADI_NUMTAB">

<xs:restriction base="xs:int">

<xs:minExclusive value="-32768"/><xs:maxExclusive value="32767"/>

</xs:restriction>

</xs:simpleType><xs:simpleType name="ADI_CODE">

<xs:restriction base="xs:string"/>

<xs:maxLength value="5"/><xs:pattern value="{[A-Z]}*"/>

</xs:restriction>

</xs:simpleType>

Here are examples of numerical fields, and then alphabetical fields. A few comments on the way this syntax is managed:

• Each field name (here ADI_CODE and ADI_NUMTAB) corresponds to the content of the tag column of the template.
• The pattern heading comes from the pattern column in the template (here, the suggested syntax corresponds to any number of alphabetical characters in uppercase).
• The headings minOccurs and maxOccurs are entered, taking into account the mandatory column of the template.
• The data types, that can be xs:string, xs:int, xs:decimal, xs:date, xs:base64Binary, xs:boolean, are originated from the corresponding X3 data, as well as the maxLength, maxExclusive, minExclusive, maxInclusive, minInclusive headings depending on the case.

Documentation / Paragraphs

This menu item allows access to the documentation management on the first paragraph of the documentation (if there is one) associated with the current record.

Documentation / Links

This menu item allows access to link management. It is used to define the links between the current record and other records (for example, the links between functions and parameters). These links are specific to the documentation and are used to load the generation of documentation structures.

Documentation / Generation

This menu item launches a documentation generation. You can also launch it from the Generation button at the bottom of the screen.

You can launch three types of generation one by one or simultaneously

• The generation of the documentation structure from the dictionary (ADOCUMENT, ADOCBLB, and ADOCCLB tables)
• The generation of the documentation from the previous tables
• The generation of the field documentation

The range suggested by default takes into account the current record, but you can modify it at launch time.

Error messages

In addition to the generic error messages, the following messages can appear during the entry:

Import not possible for this object

The object has not been defined for importing. The Import checkbox is not checked in the Miscellaneous tab.

Code already exists in line nnn

The same code associated with different groups has already been entered.

ZZZ non-existent field (XXXXXX, YYYYY, ... )

This message is displayed when a link has been expressed in the group grid using a ZZZ field that is not referenced in any of the tables (XXXXX, YYYYY, ...) defined in the previous lines.

G separator (/) not referenced in the fields. Prohibited in an import template

In the field grid, for the G group, there is no line indicating the place where the group identifier is found (/ syntax).

Link not possible. Prohibited in an import template

An attempt has been made, in the lines describing the fields, to insert a field coming from a table that has not been linked to the main table in the group.

Incorrect length nnn<>mmm

The length defined by the numeric format (mmm) is different from the length of the field defined in the previous column (nnn).

Tables used

Refer to the Implementation documentation.

Implementation advice

Basic principles

Consider the following principles when setting up an import template:

• The import allows creation and modification actions.
• To determine the action to be carried out on the imported record, the system carries out an existence test of the object to be processed. This test is carried out by loading the primary key for the object with the information in the imported record.
• As a result, if the main key is not set up, all the records will switch to creation mode. The records will only work if the key for the object to be created can be assigned automatically. For example, with a sequence number counter.
• An import corresponds to an entry simulation for all the screens linked to the imported object.
• It processes all the fields on the screen by carrying out the same checks as an interactive entry.
• It does not take into account the fields that cannot be entered on the screen (except in specific and referenced cases). As a result, the fields set up in a template corresponding to a field that cannot be entered are not imported.
• The order in which the fields are set up within the record does not matter. The import loads all the fields of the file and then imports them in the order of the fields in the screens.

Standard templates supplied

If data exports are always possible regardless of the object, this is not always the case for the import. The automatic mechanisms for decoding data flows and the call to the conditions linked to the object greatly automate the import. However, this is not sufficient for an automatic import of complex objects. Thus, not all objects can be imported.

In the reference folder, an editable import template is supplied for each object for which the import is possible. But this import can also be linked to specific features. This is defined in the online help associated with the import templates for which specific cases exist. This help is accessible by selecting Alt + F1 when the template is loaded.

The list of corresponding help, sorted by module, can be found here.

Indicators grid

Choosing the structure of the files to be imported or exported depends on the extraction or integration possibilities found in the external software.

In all cases, you must organize data in logical groups of lines, which can be of different types (such as header, detail, sub-detail) or a single type. You can define the organization of these groups in the grid of identifiers located in the first tab of the template.

Each group is associated with one of the tables in the database. The first being the main table of the object. The others are defined by the links from the previous tables. When using the template to carry out an object export, you can define the links with any table in the database in which a theoretic link exists to extract the linked data. On the other hand, for an import template, you can only use the tables updated by the object. It is not possible to simultaneously import the order and the customer, for instance. The order object was not designed for this.

This grid can be blank if a data structure to be imported or exported is based on the use of only the main table. In this case, the Code column for the next page will remain blank. You do not need to create several data groups if several linked tables have to be exported simultaneously. If fields extracted from different tables are displayed in a single data group, the export process will attempt to resolve the links between tables using the link structure described in the dictionary. This implies that there is only one link possible from the main table in the group to the described table. If not, the first link encountered will be used, even if it is not the correct one.

There is a specific case where it is necessary to create at least one group: if the template is defined with a fixed length. In this case, you need to define the record length somewhere from the table of groups). If the group indicator is not mandatory in the list of fields, defining this group with an empty code is sufficient: only one group can then be defined and the Code column will no longer be entered in the next tab.

The identifier grid is only accessible if the object is of a single type. If group indicators are defined, each is associated with a level, a table, and the link conditions that make it possible to create links between them.

The main table is set to level 1 for import or export. This table is not entered in the grid but is deduced from the object associated with the template.

The level of any table linked to a previous table is equal to the level of the previous table if a one-to-one link exists between these two tables, and corresponds to this level plus one if several records are linked to a record in the previous table. The link is characterized by the key for the destination table to be read and by the key segment expression whose value defines the linked lines.

If you define groups like in the following example:

Level	Group
1	A
2	B
2	C
3	D

Then you will obtain the following overlapping information:

Group A record 1
	Group B record 1.1
	Group B record 1.2
	...
	Group B record 1.N
	Group C record 1.1
		Group D record 1.1.1
		Group D record 1.1.2
		...
		Group D record 1.1.M
	Group C record 1.2
		Group D record 1.2.1
		...
	Group C record 1.Q
		Group D record 1.Q.1
		...
		Group D records 1.Q.R
Group A record 2
	Group B record 2.1
	...

Group example

To explain this setup, the following example takes an export template including the companies and sites:

• The main COMPANY table is level 1.
• In this example, information linked to the accounting currency (ACCCUR field) is required in the exported file. In this case, it is not necessary to define a new group linked to the company table. You simply need to define the fields of the TABCUR table in the group. The export engine will then run through the dictionary to search for the link between the COMPANY and TABCUR tables. Since there are several links, it is the first link that will be used. In this case, it is the one required.
• If the purpose had been to export information related to the currency of the company stock capital (RGCCUR field), it would have been necessary to create a second block of data. This second block would have also been of level 1 based on the TABCUR table. It would display RGCCUR in the link column. This is also what you would have needed to do if the link was not explicit. For example, the CREUSR field, which corresponds to the code of the user who created the record, uses a generic type (A) which does not allow the link to be made automatically. As a result, if information concerning the user who created the record had to be displayed, it would have been necessary to define the link by a group.
• Then, if the list of sites linked with the company needs to be displayed. A level 2 group is then created using the FACILITY table, with a link based on the FCY index taking the value of the CPY field of the main table.
• Eventually, if the setup values directly related to each company need to be extracted, a second level 2 group of data will be created using the ADOVAL table. This second group would have a link based on the ADW0 index, taking the value of the CPY field of the main table.

The grid below summarizes the Identifiers as they would be entered:

Level	Code	Table	Key	Link	Comment
1	CPY	COMPANY	CPY0		Main group record
1	CUR	TABCUR	TCU0	[CPY]RGCCUR	1 linked record
2	FCY	FACILITY	FCY1	[CPY]CPY	N linked records
2	ADP	ADOVAL	ADW0	[CPY]CPY	M linked records

File formats

File formats are determined by the type. It can be one of the following:

Field separators (ASCII 1 format)

This is a field with variable length where all the fields are separated by a separator (the separator field being SC).

Field 1 record 1	SC	Field 2 record 1	SC	...	Field N record 1	SC
Field 1 record 2	SC	Field 2 record 2	SC	...	Field N record 2	SC

Record separator (ASCII 2 format)

This is a file with variable length where all the fields are separated by a separator (the field separator). When the record is complete, another separator (the SL line separator) replaces the field separator.

Field 1 record 1	SC	Field 2 record 1	SC	...	Field N record 1	SL
Field 1 record 2	SC	Field 2 record 2	SC	...	Field N record 2	SL

Delimited format

This is a file with variable lengths of the same type as the record separator file (two distinct separators). But, in addition, the fields of character string type are contained by a field delimiter (called DC). In the following example, the second field is of alphanumeric type.

Field 1 record 1	SC	DC	Field 2 record 1	DC	SC	...	Field N record 1	SL
Field 1 record 2	SC	DC	Field 2 record 2	DC	SC	...	Field N record 2	SL

Fixed length format

This is a file where the fields are defined with a fixed length without a field separator. You must give the total length of the record in a setup. There can be a line separator. In this case, do not include its length in the record length.

In the same way, you can define the length of each group when blocks of data are set up in the indicators grid.

Fld 1 rec 1	< ---------Field 2 record 1------------ >	...	< --Field N record 1-- >	SL
Fld 1 rec 2	< ---------Field 2 record 2------------ >	...	< --Field N record 2-- >	SL

XML format

It is a format where data is defined in XML tags.

Upon export, the file contains information related both to the model and the extraction. You can also use the Options/Export schema of a template menu to export an XSD file describing the structure of the file created by the template.

Upon import, data is less important. It is possible to limit this data in the file to be imported.

Flat A format

This format is the variable Record separator format or the delimited format (if the Field delimiter field is entered).

If in the import/export template several levels are defined, only one line is generated.

Level 1 flag

Field 1 record 1

SC

DC

Field 2 record 1

DC

SC

...

Level 2 flag

Field 1 record 2

SC

DC

Field 2 record 2

SL

During import, the use of this format type implies a grouping of all detail lines of a specific level under the same header if all the fields in the header are strictly identical.

Header format

This format is the same as the Flat A format with an additional header line corresponding to the titles of the template fields.

For example, this format is used in Germany for GDPDU files.

Technical appendices

For further information see the following technical appendices:

• Import/export templates: Technical appendix
• Appendix: GIMP variables