Multiple fixed width record files

To create new source data formats for a multiple fixed width record file:

  1. In Transfer Desk, click Format Designer on the toolbar.
  2. From the File menu, select New Format. The New Format dialog is displayed.
  3. Specify this information:
    Classification
    Select Multiple Records Fixed Width.
    Name
    Specify a unique name for the new data format.
    Description
    Specify a description for the new data format.
  4. Click OK.
  5. Specify or amend the following details:
    Description
    The description that was entered when the data format was created.
    Initial lines to ignore
    The number of lines at the start of the external file that are to be ignored. The default is 0.
    Record Separator
    The code that is used at the end of each record in the external file in order to separate the records. Select Windows New Line, UNIX New Line or Other. Windows New Line is the default separator and is displayed in the script as record-sep="\r\n", that is, carriage return and new line.

    To specify a different separator, select Other. This enables the Specify field for you to specify the separator.

    In the Fields section, in the Start, Length and Field columns, click in a row marked with a '*' asterisk to enter the start position (numeric), length (numeric) and name of the field. The name of the field must conform to XML syntax, for example, it cannot start with a numeric character. The field name must also be unique within the data format.

    Use these buttons to import field names or to insert or delete fields:

    Read File
    To import existing field names from an example file, click Read File and browse to the location of your file. The field names are extracted and inserted into the rows, replacing any field names you have already defined.
    All files being imported must be in UTF-8 format.
    Insert
    Click Insert to add an additional new row above the currently selected row.
    Delete
    Click Delete to remove the currently selected row.
    Records
    In the Records section:
    • To add a record, click Add. This displays an additional tab for you to enter the record details.
    • To delete a record, select an existing record tab, and click Delete.

    To complete the record details, specify this information:

    Prefix
    The string that identifies the start of each record.
    Record Name
    The name that identifies each record.
    Field
    The start position, length, and field names that define the fixed width data in the external file. Click in a row marked with an '*' asterisk to enter new field names. These must conform to XML syntax, for example, they cannot start with numeric characters. The field names must also be unique within the field set.

    Check the check boxes in the Ignore column for any rows that you do not want to be used, for example, you may want to ignore the spaces between each field name.

    Use these buttons to import field names or to insert or delete fields:
    Read File
    To import existing field names from an example file, click Read File and browse to the location of your file. The field names are extracted and inserted into the rows, replacing any field names you have already defined.
    Note: All files being imported must be in UTF-8 format.
    Insert
    Click Insert to add an additional new row above the currently selected row.
    Delete
    Click Delete to remove the currently selected row.

    To sort the order of the field names, select the field you want to move and click Up or Down as appropriate.

  6. Click Save a Format to save the data format to the database.
  7. Validate and test the data format.
  8. To view or edit the data format source code, change the view to text view. Select ViewChangeText View.
    Note: Restrictions exist if the external data source consists of files that contain more than one header line. For example, if you want to import external data sources into SunSystems as purchase orders, you create an import profile using component Purchase Order, method CreateOrAmend, and create a data format and the respective transformation. A purchase order will be generated if the runtime parameters are set correctly and there is no system exception during the import. Only the first header line is taken as the Header and the Line data will be all the lines, including those belonging to the other header lines in the source data. This restriction is evident in the components Purchase Order, Sales Order, Movement Order, Goods Receipt Notes and so on. This restriction will be removed in future releases; however, if you need to bypass this now, please contact your regional support group.