Configuration manager

Flat file - fixed length fields

A flat file with fixed length fields is an ASCII file in which database records are written. Each database record consists of one line terminated by carriage return (CR) and linefeed (LF), and where the fields always have the same number of characters, independent of their contents.

For example: consider a database table holding warehouse inventory and having the following information:

Field	Start position	Length	Description
Location	1	10	Warehouse location
Product	11	8	Product identification
Description	19	30	Product description
On-hand quantity	49	12	Quantity on location

The flat file could look like:

Warehouse inventory dump
20030101124500
U12A09    24188   Lubricant                     00000018.450
U12A10    87912   Ball bearing                  00000201.000
U12A11    54399   Fuel injection pump           00000001.000
U12A12    A65432  Gasket                        00000003.000
U13A01    99553   Spark plug                    00000124.000
U13A02    63298   Brake drum                    00000003.000

123456789012345678901234567890123456789012345678901234567890

You define a connection client of this type by selecting FIXED FILE in the connection type listbox of the edit connections screen.

• Directory: the full path to the directory where the connection client scans for input files, or the directory where the output files are written in.
• Manual or Scan directory: when manual is selected, the input file is always selected manually at the connection client. When 'Scan directory' is selected the input file is automatically read and interpreted when the file is written in the directory specified. This option does not disable the manual mode.
• After interpreting the input file, it must be marked as been executed, to avoid a repetitive execution. Three options are available:
  - Delete after read: the input file is deleted, and will be no longer available
  - Set archive: the archive attribute of the file is set. Only files that do not have the archive attribute set are read by the connection client.
  - Reset archive: the archive attribute of the file is switched off. Only files that have the archive attribute set are read by the connection client.
• Filename format and Format variables: definition of the format of the filename. For input files, only files with a name matching the format will be read. If the filename format is left blank, any file written in the input directory is executable. For output files, this is the format how the connection client should name the file. If left blank, the output file can only be generated manually, since the connection client does not have a way to generate the filename. See below for the syntax in these fields.
• Start reading at line: Specify on what line the first record in the file is. This avoids header lines in the file being treated as records.

Format strings and variables

With a format string you can specify how the filenames should be interpreted when the file is of input type, or how the output filename should look like. The format string can contain both literal text and substitution symbols. If you want the connection client to recognize an input file, then its filename should match exactly the format string. If not, the file is ignored.

Substitution symbols are placeholders for variable strings in the filename. By defining a substitution symbol you can extract parts of the filename and put it in a variable for further use, or insert the contents of a variable in the filename to be generated. The format string can contain the following substitution symbols:

For input files

• %#s : specifies a fixed length string, where # should be replaced by the number of characters in the string. For example: when you specify %5s the connection client will consider the 5 next characters as one string and put this string in the variable provided.
• %sdelimiter : all characters starting at the % sign till the delimiter character are considered to be one string and written in the variable provided. Example: %s_ will read all characters till the underscore and put these as one string in the variable (without the underscore).
• %n : starting at the % sign, all characters till the first alpha-numeric characters will be treated as a number. Example: '078.txt' will return 78 as numeric value when %n is specified.
• %#t : specifies a fixed length table name. The usage is the same as for %#s, but the string is considered to be a table name used in the 'source tables and mapping' section. You must provide a dummy variable, however you cannot use the variable in further configuration. A %t symbol can only be used once in one format string.
• %tdelimiter : as %sdelimiter but specifies a table name.

For output files

• %s : the contents of the variable is written at the exact place of the %s. The variable is written when defined in the 'destination field name' (See source tables and mapping).
• %t : the table name defined in 'destination table name' (See source tables and mapping) is written at the exact place of the %t.

As many substitution symbols you define, as many format variables you need to define. The substition symbols are replaced by the variables content in the exact sequence as both symbols and variables are defined. The name of the variable specified for a %s or %n symbol can be used as a field name when you do the field mapping job. A %t symbol is always placed in an internal variable. The contents of %t is used as table name. However, you still need to provide a dummy variable for %t.

If you do not specify a table name, you must use 'ANY' as table name in your 'source tables and mappings' section.

Some examples:

Defining fields

Fields are defined and used in exactly the same way as outlined in source tables and mapping. However, as soon as you use a fixed file connection your screen will be slightly different.

In source table name you define the name extracted from the filename and defined as %t or %#t in the format string. In case of destination table it would be the string written in the filename where %t is defined.

In case you defined a variable %s or %n in the format string with corresponding variable names, you may use the variable name here by entering it in the 'Field position' input field.

When writing files, you will get as many files as records sent to the fixed file client. If you want to group records, you can only do this by defining a scheduled task (batch) at the source client, and enable the obsolete check option in the 'tables and mappings' section.