Write to Variable Format File

Write to Variable Format File writes records of varying layout to a file.

Variable format files have these characteristics:

  • Records in the file may have different fields, and different numbers of fields.
  • Each record must contain a tag (usually a number) identifying the type of record.
  • Hierarchical relationships are supported.

Example of a Variable Format File

This example shows a variable format file containing information about checking account activity for two customers, Joe Smith and Anne Johnson. In this example, the file is a delimited file that uses a comma as the field delimiter.

001   Joe,Smith,M,100 Main St,555-234-1290
100   CHK12904567,12/2/2007,6/1/2012,CHK
200   1000567,1/5/2012,Fashion Shoes,323.12
001   Anne,Johnson,F,1202 Lake St,555-222-4932
100   CHK238193875,1/21/2001,4/12/2012,CHK
200   1000232,3/5/2012,Blue Goose Grocery,132.11
200   1000232,3/8/2012,Trailway Bikes,540.00

The first field in each record contains the tag which identifies the type of record and therefore the record's format:

  • 001: Customer record
  • 100: Account record
  • 200: Account transaction record

For delimited files it is common for the tag value (001, 100, 200) to be in a fixed number of bytes at the start of the record as shown in the above example.

Each record has its own format:

  • 001: FirstName,LastName,Gender,Address,PhoneNumber
  • 100: AccountID,DateOpened,ExpirationDate,TypeOfAccount
  • 200: TransactionID,DateOfTransaction,Vendor,Amount

Record format 100 (account record) is a child of the previous 001 record, and record format 200 (account transaction record) is a child of the previous record 100 (account record). So in the example file, Joe Smith's account CHK12904567 had a transaction on 1/5/2012 in the amount of 323.12 at Fashion Shoes. Likewise, Anne Johnson's account CHK238193875 had two transactions, one on 3/5/2012 at Blue Goose Grocery and one on 3/8/2012 at Trailway Bikes.

File Properties Tab

Option Name Description
Server name Indicates whether the file you select as input is located on the computer running Spectrum Enterprise Designer or on the Spectrum Technology Platform server. If you select a file on the local computer, the server name will be My Computer. If you select a file on the server the server name will be Spectrum Technology Platform.

File name

Specifies the path to the file. Click the ellipses button (...) to locate the file you want.

Note: If the Spectrum Technology Platform server is running on Linux, remember that file names and paths on these platforms are case sensitive.

Root tag name

The tag to use for records that are a parent of other record types. For example if you have three record types 001, 100, and 200, and record types 100 and 200 are children of record type 001, then 001 is the root tag.

Use fixed-width tags

Specifies whether to allocate a fixed amount of space at the beginning of each record in which to place the record tag. This example shows a file with the tags 001, 100, and 200 in a fixed-width field:

001   Joe,Smith,M,100 Main St,555-234-1290
100   CHK12904567,12/2/2007,6/1/2012,CHK
200   1000567,1/5/2012,Mike's Shoes,323.12

Tag width

If you check the Use fixed-width tags box, this option specifies the number of spaces to allocate for tags at the beginning of each record. For example, if you specify 7, then the first seven positions in each record will be reserved for the tag. The value you specify must be greater than or equal to the size in characters of the longest tag name. For information about tag names, see Tag Names in Variable Format Files.

The value in the Tag width field is automatically increased if you add fields on the Fields tab that have a name that is longer than the value specified.

The maximum tag width is 1024.

Remove numeric tag prefix

Removes the "NumericTag_" portion of the field name before writing the tag to the file. The "NumericTag_" prefix is added to tag names by the Read from Variable Format File stage for any tag names that start with a number. This is because the tag name is used as the name of a list dataflow field which contains the data in the record, and dataflow field names cannot begin with a number. For example, a tag 100 would by changed to list field named "NumericTag_100". If you enable this option, this field would be written to the output file as a record with a tag of "100" instead of "NumbericTag_100".

Character encoding The text file's encoding. Select one of these:
CP1252
This encoding is also known as the Windows-1252 or simply Windows character set. It is a super set of ISO-8859-1 and uses the 128-159 code range to display additional characters not included in the ISO-8859-1 character set.
UTF-8
Supports all Unicode characters and is backwards-compatible with ASCII. For more information about UTF, see unicode.org/faq/utf_bom.html.
UTF-16
Supports all Unicode characters but is not backwards-compatible with ASCII. For more information about UTF, see unicode.org/faq/utf_bom.html.
US-ASCII
A character encoding based on the order of the English alphabet.
UTF-16BE
UTF-16 encoding with big endian byte serialization (most significant byte first).
UTF-16LE
UTF-16 encoding with little endian byte serialization (least significant byte first).
ISO-8859-1
An ASCII character encoding typically used for Western European languages. Also known as Latin-1.
ISO-8859-3
An ASCII character encoding typically used for Southern European languages. Also known as Latin-3.
ISO-8859-9
An ASCII character encoding typically used for Turkish language. Also known as Latin-5.
CP850
An ASCII code page used to write Western European languages.
CP500
An EBCDIC code page used to write Western European languages.
Shift_JIS
A character encoding for the Japanese language.
MS932
A Microsoft's extension of Shift_JIS to include NEC special characters, NEC selection of IBM extensions, and IBM extensions.
CP1047
An EBCDIC code page with the full Latin-1 character set.
Field separator Specifies the character used to separate fields in a delimited file. For example, this record uses a pipe (|) as a field separator:
7200 13TH ST|MIAMI|FL|33144

These characters available to define as field separators are:

  • Space
  • Tab
  • Comma
  • Period
  • Semicolon
  • Pipe

If the file uses a different character as a field separator, click the ellipses button to select another character as a delimiter.

Tag separator Specifies the character placed after the tag field to demarcate the identifying field for each record in a delimited file. A tag separator must be a single character.

By default, these characters are available to be selected as tag separators:

  • Space
  • Tab
  • Comma
  • Period
  • Semicolon
  • Pipe

If the file uses a different character as a tag separator, click the ellipses button to add and select a custom tag separator.

Note: By default, the Record separator character is the same as the selected Field separator character. To enable this field and select a different character, uncheck the Same as Field separator checkbox.
Same as Field separator Indicates if the tag separator is the same as the field separator. Uncheck this to select a different character as the tag separator.
Note: By default, this checkbox is checked and the Tag separator field is disabled.
Text qualifier

The character used to surround text values in a delimited file.

For example, this record uses double quotes (") as a text qualifier.

"7200 13TH ST"|"MIAMI"|"FL"|"33144"

The characters available to define as text qualifiers are:

  • Single quote (')
  • Double quote (")

If the file uses a different text qualifier, click the ellipses button to select another character as a text qualifier.

Record separator

Specifies the character used to separate records in line a sequential or delimited file. This field is not available if you check the Use default EOL check box.

The record separator settings available are:

Linux (U+000A)
A line feed character separates the records. This is the standard record separator for Linux systems.
Macintosh (U+000D)
A carriage return character separates the records. This is the standard record separator for Macintosh systems.
Windows (U+000D U+000A)
A carriage return followed by a line feed separates the records. This is the standard record separator for Windows systems.

If your file uses a different record separator, click the ellipses button to select another character as a record separator.

Use default EOL

Specifies that the file's record separator is the default end of line (EOL) character used on the operating system on which the Spectrum Technology Platform server is running.

Do not select this option if the file uses an EOL character that is different from the default EOL character used on the server's operating system. For example, if the file uses a Windows EOL but the server is running on Linux, do not check this option. Instead, select the Windows option in the Record separator field.

Fields Tab

The Fields tab controls which fields from the dataflow are included in the output file.

Option Name

Description

Add

Click to add a field to the output.

For information about constructing dataflow fields for use with Write to Variable Format File, see Writing Flat Data to a Variable Format File.

Modify

Click to modify the name of the tag. This button is only enabled when a tag is selected. If the Use fixed-width tags option is enabled on the File Properties tab, the tag width is automatically adjusted if you enter a longer tag name.

Note: Using this button to modify the root tag name has the same effect as modifying the value of the Root tag name field on the File Properties tab.

Remove

Removes the selected field from the output. If you remove a list field all child fields are also removed. If you remove a child field, just the selected child field is removed from the list field.

xx

Remove All

Removes all the fields from the output.

Move Up/Move Down

Reorders the selected field.

Runtime Tab

Option Name

Description

File name

This displays the file defined on the File Properties tab.

Generate multiple files Select this option to write records to different files instead of writing all records to one file. The file to which each record is written is specified in the record itself. Each record must contain a field that specifies either a file name or the full file path of the file to which you want the record written. For example, if you want to send the stock prices of different companies (of various groups) to all the clients separately, this feature writes the stock prices of different companies into separate files that may be sent to each of the clients, if you so wish. If you enable the Generate multiple file option you must specify an output file on either the Spectrum Technology Platform server or on an FTP server. If you want to write data to a file on an FTP server you must define a connection to the file server using Spectrum Management Console.
Note: The records in the column you select in the File path field must be in sorted order. Use this feature when record contains either a file name or the full file path of the file.
File path field Selects the field that contains the path (either a file name or the full file path) of the file to which you want to write the record. Note that only the simple type elements mapped directly to a root tag will be listed in the File path field. This field is only enabled if you select the Generate multiple files.

Write Mode

Specifies whether to add the dataflow's output to the end of the file or to delete the existing data in the file before writing the output:

Overwrite
Replaces the existing data in the output file each time the dataflow runs.
Append
Adds the dataflow's output to the end of the file without erasing the file's existing data.