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.

Prerequisite

Refer to documentation Implementation

Screen management

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.

This field indicates the name of the import/export template's factory owner.

If the factory owner is SAGE, you can't modify anything on the screen.

Template (field EXT)

This code identifies the import/export template.
Description (field INTIT)

[object Object]

Active (field ENAFLG)

Select this check box to activate the current record.

Disabled records keep their content and setup but cannot be used by recalling their code:

  • On other records such as documents and settings
  • On mass processes

The authorizations for a given function can prohibit the creation of an active record. In this case, the check box is disabled by default. It can only be modified by an authorized user or through a signature workflow.
Factory owner (field AFACTORYOW)

Tab Header

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

General

Object (field OBJ)

This field indicates the object code to be imported or exported. This field is optional for exports. The identifying block displays the name of the main table to export.
Function (field FONCTION)

This field initializes the context and checks the access rights. It is useful if the same object is used by several functions. Indeed, users must have the appropriate rights to access the function to be able to use the template.

This field is mandatory.
Module (field MODULE)

[object Object]

Activity code (field CODACT)

An activity code is used to:

  • Make an element optional in the dictionary if the value associated with the activity code is null
  • Identify the specific/custom elements if they are marked with a code starting with X, Y, or Z
  • Size a maximum number of lines when the activity code marks elements from a grid

If the activity code is disabled:

  • The marked element will not be useable
  • The associated code will not be generated nor activated
Access code (field ACS)

This access code makes it possible to prohibit access to the current record for some users.

As a matter of fact, if the field is completed, only the users having this access code with read rights (respectively write rights) can view (respectively modify) the concerned record.

This access code makes it possible to prohibit access to the current record for some users.

If the field is populated only users that have this access code with read access rights (and write access rights respectively) can view (or with write access rights, modify) the record.

The execution right associated with a user code is processed in a particular way in the case of the import/export templates: If a user has not been granted the execution right, he cannot use the template to import or export data.
Standard script (field TRTIMP)

This field defines the standard process that includes the labels of the actions called in import/export processes.

These processes are used to carry out initializations, additional controls, and if required, updates. The structure of such a program is in the technical appendix. The standard processes usually named IMPXXX, XXX being the import code, are supplied for a certain number of imports.

For additional information on these actions, refer to the corresponding appended documentation.
Specific script (field SPEIMP)

This field defines the specific/custom process that is called before the standard process, and which is used to carry out the same actions by de-activating if necessary what is carried out by the standard process.

The possible actions are initializations, additional controls, and updates if required.

For additional information on these actions, refer to the corresponding appended documentation.

Structure

File type (field TYPFIL)

This field defines the structure used to manage the data in the file to be imported or exported. For further information, refer to the corresponding paragraph.
Field separator (field SEPFLD)

Specify the character used as the separator between two fields.

To enter a non-printable character, enter a backslash followed by three numbers representing the ASCII code of the character with decimal bases.
Record separator (field SEPREC)

Specify the separator used between two records (data groups).

To enter a non-printable character, enter a backslash followed by three numbers representing the ASCII code of the character with decimal bases.

The commonly used separators are:

  • The line feed character (\010), which corresponds to the end of the line in Unix text files
  • The combination of the two carriage return and line feed characters (\013\010), which corresponds to the end of the line in Windows text files
Field delimiter (field FLDLIM)

The field delimiter is added in the first and last position for alphanumeric fields. For numeric and date fields, no delimiter is required.

It is usually one of the following characters:

  • Simple quote: '
  • Double-quote: "
File format (field CODDBA)

Enter the format of the characters used in the file:

  • ASCII is the standard format where a character is equivalent to a byte in the file. This type of format is used to deal with traditional occidental characters, with different possible character sets, defined in the corresponding field.
  • UTF-8 corresponds to a UNICODE format where the number of characters varies (from 1 to 4, 1 corresponding to the non-accentuated Latin character set). This format makes it possible to deal with all types of characters, for instance, Chinese characters.
  • UCS-2 corresponds to Microsoft's standard format where characters are systematically stored on two bytes.

Export

Export (field EXPORT)

If this field is checked, it will be possible to use this template in the export of data.

Export sequence no. (field CHRNUM)

This non-editable field stores the value of the sequence number when the last export took place. When performing chronological exports, this is useful to process only what has been modified since the last export.

Transcoding

Character set (field OPTCHA)

When the ASCII character set is used, you can apply various standardized formats:

  • ISO 8859 code, which is also the Adonix internal set when using the ASCII format
  • IBM PC code
  • ASCII 7-bit code without accents, accentuated letters are converted into the corresponding lowercase letters
Decimal separator (field SEPDEC)

This field defines the decimal separator used for numbers. If this field is empty, the system considers that the decimal separator is a full stop.
Date format (field OPTDAT)

This field defines how the date type fields are coded.

You can only specify the order and the number of characters in the year. For the import, any separation characters between fields are filtered. Dates in the DD-MM-YY or DD/MM/YYYY formats are correctly decoded.

The decoding sub-program takes the engine's adxdcs variable into account. This variable is set via the DCS parameter that is found in the general parameters to define how a year is decoded over two characters. DCS represents the pivot year that defines the century change.

For example, if DCS is equal to 1940, any year made up of two numbers:

  • Less than or equal to 40 is considered to be part of the 21st century
  • Greater than 40 is considered to be part of the 20th century

It is then possible to express the years between 1940 and 2039 with two numbers.
field LIBDAT

Title associated with the previous code.
Local menu format (field OPTMNL)

The fields of local menu type are stored as a number representing their rank in the table.

According to the value of this field, the template will export or expect to find an import.

  • 0 : 0: The choice is a number that indicates the rank of the menu in the table. 1 for the first choice, 2 for the second, etc. This also corresponds to the internal format under which the local menu is stored in the database.
  • 1 : 1: The choice is entered using the code on one character associated with each choice of local menu. This code is not visible in local menu management. It can be defined in the development functions in message management where there is the possibility to enter this internal code. This internal code is only used for that purpose, unlike for earlier character versions, where it was used as an entry accelerator.
  • n : (n>1): The first n characters of the label displayed in the entry. When using this option, the search algorithm searches for the first character, then the second, and so on, until a single corresponding title exists. For example, if a search is carried out to find CHQ in a local menu where the titles are Cash, Transfer, Check, Draft, and Bank/credit card, the algorithm finds Check because it is the only title whose first two letters correspond.

The local menu titles are only the labels used in display, and the value stored in the database being the rank in the table. It is possible to change the title of the local menus at the time of an import for the search algorithm to operate correctly. Changing local menu titles can only be done in single-user mode. This is not designed for regular or automatic transfers.
field LIBMNL

Title associated with the previous code.

Import

Import (field IMPORT)

If this field is checked, it is possible to use this template to import data.

Update allowed (field OPTUPD)

Use this field to modify an already existing record during import.
Temp storage space (field AOWSTA)

When this box is checked, the data import feeds the import/export storage space with the wrong data. The fact that the storage space is fed does not prevent the creation of an error file.
Special import (field OPTSPE)

This field indicates that the data integration into the database is made using specific/custom actions defined in the process whose name is given in the Import process field. This specific/custom process includes a restricted number of entry points and therefore requires the writing of a process including all the controls that should be carried out.

Its use resides in the fact that it is possible to group controls to optimize the import program. The structure of the specific/custom imports is described in the appendix. You can find the following actions:

  • A $RAZCRE label that is directly called by the import
  • A $SAIMSK label that is directly called by the import for each record read and replaces the standard SAIMSK call (allocation and control of the mask fields starting from class [F])
Authorized creation (field OPTCRE)

When this check box is selected, the import/export template can be used to create new records in the database.

Workflow (field ENAWRK)

If this box is unchecked, the workflow events related to basic operations such as creation or modification in object management mode are no longer called. If imports are started and generate mass updates, this avoids the release of several events of this type. This can impair the performance of the import and cause the mass sending of messages.

However, this does not mean that the workflow events related to the import's release are disabled.

Grid Identifiers

No. (field NUMFLG)

Current line of the grid.
Level (field FLGLEV)

This field defines the group's overlapping level. Level 1 is the main level. An N+1 level defines a sub-level of the preceding N level.
Indicator (field FLGREC)

This field identifies the group by a code containing a maximum of five characters. This code will be mentioned in the field grid of the following tab, and in the file itself, as a group header.
Table (field FLGFIL)

This indicator grid defines the structure of the record groups. Refer to the corresponding paragraph.
Key (field FLGKEY)

This field defines the key of the linked table used to access the detail of the group records from values of the upper-level tables used in the link expression.
Link (field FLGLNK)

This field defines the link expression. It is a series of values separated by a semicolon that gives the key values linking the detail table to the header record.
Length (field RECLEN)

For a file with a fixed length, you need to indicate the number of characters for each record.

Tab Fields

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.

Grid Fields

No. (field NUMLIG)

Current line of the grid.
Indicator (field TYP)

This field is only entered if the group indicator grid in the previous tab is not empty. It is used to attach the information to be exported or imported to a group of data.
Table (field FIL)

This field defines the database table where the data to be imported or exported is defined. Note that:

  • This field is mandatory even if it has no particular use. If a calculated expression is defined in the export template, it can reference fields coming from several tables.
  • The table in question is not necessarily the main table associated with the group. But it can be a table linked to the main table in this group or one of the previous groups in the superior level. If no link is found, a warning message is displayed: Automatic link not managed. This means that for the import or export process entered in the first section, you do not need to carry out this link manually. For example, by declaring the table in the IMP_OUVRE action and the read of the table in the IMP_LIENS action.
Field (field FLD)

This field indicates the name of the field of the table to be imported or exported. Different syntaxes are possible to define the information to be extracted or integrated:

  • The slash means that a group indicator is written upon export or searched for during import. In a template used for import, when several groups exist, this separator is mandatory for each group. The group separator is supposed to be a normal field enclosed by field separators and delimiters if the Delimited template type is used.
  • The most simple syntax is FIELD(index) when a field coming from the table declared in the previous column is imported or exported. A selection window displays the possible fields.
  • If a constant string of characters enclosed between double quotes is entered here, the field will be written as it is in the file to be exported and ignored in the import.
  • If nothing is entered, there will be an empty field in the exported file enclosed by field delimiters if the template is of the Delimited type. In import mode, it means that the corresponding field must be ignored.
  • The *N syntax, where N is a number between 1 and 99, is also possible. You can use it to assign in import or to read in export the GIMP(N) variable. GIMP is a global variable of character string type with a maximum length equal to 100. You must assign it in a specific/custom process associated with the import or export.
  • The last possibility, only used in export, is to define any calculated expression preceded by =. This expression can call constants, functions, variables, operators, and fields coming from online tables. Its syntax is verified upon entry. The control of the context, such as the fact that the variables exist, cannot be verified.
Description (field COM)

Add a comment to make the configuration easier to understand.
Range (field SEL)

Three choices are possible in this field:

  • Not entered means that no start-end range will be entered for this field when launching the export.
  • Entered means that a start-end range will be entered for this field when launching the export.
  • Not transferred means that a start-end range can be entered for this field when launching the export, but that the field will not be transferred during an import or an export.
Position (field LOC)

This column is only useful in the case of a fixed-length format. In this case, the position gives the gap concerning the start of the block or the record. The position is in numbers of bytes, 1 being the start of the section or record. The positions must be compatible with the size of the record.
Length (field LNG)

This field determines the length of the field for a sequential file.

Format (field FMT)

This column is only entered if the format is fixed length. For numeric amounts, the format entered is defined in the nnn or nnn.mmm format, remembering that these numbers can be:

  • Prefixed by < or >. Left or right framing being completed with zeros, the right alignment must be used by default.
  • Prefixed or suffixed by the + character. This sign is mandatory before or after the number.
  • Prefixed by the * character. The decimal separator must not appear. The grid below displays examples of the formatting for a given amount. In the examples below, spaces are replaced by #.

Format

Amount

Result of the formatting

7.2

123.456

####123.45

7.2

-123.456

### -123.45

>7.2

123.456

0000123.45

>7.2

-123.456

-000123.45

<7.2

123.456

123.45####

<7.2

-123.456

- 123.45###

6.2+

123.456

###123.45+

>6 .2+

123.456

000123.45+

<6.2+

-123.456

123.45-###

For an alphanumeric format, the only formatting directives that are possible are < or > (left or right alignment, remembering that the character strings are completed with spaces).
  • Pattern (field PATTERN)

This field is used when imports/exports are carried out under the XML format.

When an XML file is created, more information is required. For example, to create an XSD file describing the structure of the XML file and then control its validity with syntax check tools integrated into the different ETL software.

This field defines whether a template has to be associated with the description entered in the XSD description.

If this field is entered, the XSD field will contain a specification of the following type:
pattern value="value_entered_in_the_pattern_field"

You can find in online tutorials (like this one) syntaxes to use for patterns.
  • Tag (field BAL)

This field is used when imports/exports are carried out under the XML format.

When an XML file is created, more information is required. For example, to create an XSD file describing the structure of the XML file and then control its validity with syntax check tools integrated into the different ETL software.

This field defines the code of the tag describing the field exported in the template as it appears in the XML file.
  • Mandatory (field OBL)

This field is used when imports/exports are carried out under the XML format.

When an XML file is created, more information is required. For example, to create an XSD file describing the structure of the XML file and then control its validity with syntax check tools integrated into the different ETL software.

This field defines whether the field is mandatory or not. If the value of this field is set to Yes, the XSL file will contain a specification of minOccurs="1" type.

This number, if it exists, refers to a transcoding table used to transcode the field that is read and make it comply with the expected format.

File generation

field TYPEXP

Select the type of export that you want to do. It can be:

  • Client
  • Server
Data file (field FILEXT)

This field defines the path of the default data file proposed during the launch of the import or export. This data file is used in automatic mode when launching an import or export chain. This file path can be relative. In this case, the database directory is supposed to be the database directory for the software's installation.

The path can include the # character. In this case, there will be a sequential number management:

  • In import mode: All the files where the template corresponds to the path will be searched, with # representing five numbers. The files are integrated in increasing order of the numbers.
  • In export mode: A file is created integrating the formatted value of the [C]EXPORT sequence number counter on five numbers. This is related to checking the Sequence number management box during the launch of the export function.

For example, if the export sequence number is equal to 156, /u/tmp/fil# makes it possible to generate the /u/tmp/fil156 file.
Final directory (field REPFIN)

This field forces the final directory to which the file will be transferred after having been imported. If no value is defined, the directory mentioned in the import/export general parameters is used.
Select a Field

Block number 1

Table (field FICHIER)

Define the table for which the fields to be inserted must be selected.

Grid

No. (field NUMLIG)

Current line of the grid.
Field (field CODZONE)

Define the field name for the table as it will be expressed in the software. A field with the name FIELDNAME defined in an ABV abbreviation table can be accessed using the [F:ABV]FIELDNAME syntax.

For custom/specific fields, the field name must start with X_, Y_, or Z_.

In the database, each zone corresponds to one or more fields, according to whether or not the zone is sized. The corresponding fields are called FIELDNAME_0, FIELDNAME_1, FIELDNAME_2.

To enter and display the corresponding field on a screen, it is given the same name in the screen dictionary. The screen and the table will be used simultaneously in object management.
Description (field INTITCOURT)

Title associated with the previous code.
Selection (field SELECT)

If the field is set to Yes, it is inserted into the main table. By default:

  • Fields that are not in the main table are proposed to be set to Yes
  • Fields that are in the main table are proposed to be set to No

Use this field to insert, from the current line in the grid, a group of fields coming from a template's table.

Indicator position re-calculation

This function is only present for templates with fixed-length file types. It recalculates the position of each of the fields in the current data group (sharing the same line indicator). The recalculation is carried out from position 1 in the first field of the group and adds the length of each field to obtain the following field's position.

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.

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)

Indicate the folder from which the record is going be copied. The possible syntaxes are described in the dedicated appendix.
All folders (field TOUDOS)

This option is used to copy the record to all the folders defined in the dictionary (ADOSSIER table from the current solution).
To folder (field DOSDES)

Indicate the folder in which the record is going be copied. The possible syntaxes are described in the dedicated appendix.

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.

Grid Range

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)

Grid Criteria

Tables (field FICHIER)

The tables used in the import/export template are listed here to enable the filtering of the exported data.
Criterion (field FLGEXP)

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