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:
Quantity on location
The flat file could look like:
Warehouse inventory dump
U12A09 24188 Lubricant
U12A10 87912 Ball bearing
U12A11 54399 Fuel injection pump
U12A12 A65432 Gasket
U13A01 99553 Spark plug
U13A02 63298 Brake drum
You define a connection client of this type by
selecting FIXED FILE in the connection type listbox of the edit connections
- 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
- Delete after read: the input file is deleted, and will be no longer
- 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
- 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.
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
- %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.
Input Filename: In001inventory_20030101.txt
Sequencenumber = 1
Table = inventory
year = 2003
month = 01
day = 01
Input Filename: In001inventory_20030101.dat
file is rejected
Table = PartMaster
Datestamp = 1Jan2003
Output filename = OutPartMaster1Jan2003.txt
Input filename: InA254.txt
YearDayCode = A254
Table = ANY
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.