The Format as Flat FileClosed The Format as Flat File tool takes a BPA Platform recordset and converts it to a flat file to be used by another task step or external program. The flat file output can be delimited or fixed width. You can also choose whether the flat file contains data for single or multiple record types. tool does not require any global configuration; it can be immediately added to a task.

Where Should I Use a Format as Flat File Step?

Typically, you would add a Format as Flat File step after a step that exposes BPA Platform recordsets, for example, the Convert XML to RecordsetClosed The Convert XML to Recordset tool is used to convert XML data into a flat-format BPA Platform recordset, allowing the data to be used by recordset-only consuming tools. This could then be used to power alerts via fax, email, or SMS, create reports, power a workflow, perform FTP transfer, and so on. tool.

You would then follow the step with a tool that can consume and use the document exposed by the Format as Flat File step. For example, the Save FileClosed The Save File tool saves output from another task step capable of producing document, as one or more files on the local drive, on-premise file server, external server, or cloud based server. or Print DocumentClosed The Print Document tool allows you to produce hard-copies of documents created by other task steps. All standard printer properties are available to produce run-time-specific output. tool.

Example

The following example shows a process flow using a Format as Flat File step:

Task Flow for Format as Flat File

A Database Query (ODBC)Closed You typically use the Database Query (ODBC) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. step is used to extract sales data from the database. The outputted recordset is formatted as a CSV file, which is then opened in Microsoft Excel for further manipulation (not part of this task).

For more information about creating tasks, see How to Create a Basic Task.

For a detailed list of those tools the Format as Flat File tool can interact with, go to Working with Other Tools.

Adding a New Task Step

To add a new Format as Flat File step to an existing task:

  1. Open the relevant task (if creating a new task, see How to Create a Basic Task).
  2. Either:
► Click and drag the Format as Flat File icon from the Task Browser to the task Design area.

–OR–

► From the task's Design tab, right-click on empty space and select New > Format > Format as Flat File.
  1. In the General tab, Name your step and select the input data source.

    The General tab is used to Name and describe (Description) this task step.

    Tip: If this task instance makes use of two or more Format as Flat File steps, ensure the Name used is unique for each individual step.

    At the time of writing, the Format as Flat File tool can only consume recordsets from other steps. The data from this recordset make up the contents of the flat fileClosed A flat file (also referred to as a "flat file database") stores unstrcutured data in plain text format. It does not contain: • Any internal hierarchy • Any links to other files • Any word processing information or formatting For BPA Platform, a flat file contains a single "table" of data, with one record per line. You specify the data type of each column and use delimiters to separate each column in a record..

    ►Use a Recordset — Enable this parameter if recordset data from a previous task step is required to form the document
    ■Input Recordset — Contains all available recordsets from steps previously created in the task

    For more information, go to the main General tab topic.

  2. In the Main tab, inform the task step how to read the input data source and specify the required output.

    Format as Flat File - Main Tab

    From the Document Generation Options pane, you specify when a new document is generated:

    ►One Document — Generate a new document each time the Format as Flat File task step is run.
    ►New Document when column changes — Generate a new document each time the value in a specified column changes. Select the relevant column.
    ►New document for each row — Generate a new document for every record in the input recordset.

    This type of flat file supports a different row structure for different data values.

    To have a flat file which supports a different row structure when a specific data value (record type) is detected, select Support Multiple Record Types in File.

    "Tag" is the name given to the data the Format as Flat File step acts upon. Tag ColumnClosed "Tag" is the name given to the data the Format as Flat File step acts upon. "Tag Column" is the input recordset column that the step monitors for the data. Tag record type, or value, is the recordset column contents the Format as Flat File step is monitoring for. is the input recordset column that the step monitors for the data. Tag record type, or value, is the recordset column contents the Format as Flat File step is monitoring for.

    All other options are not used for this type of flat file and are greyed out.

    Use Suppress end of row marker on last row if using this file in a program that does not require the last row of the file to be blank, for example, an external program such as Notepad, or even a BPA Platform tool such as Print Document.

    Use Use Source Column as Column Name to have the output column names the same name as the source column. This saves time with re-keying field values.

    Go to the Output Columns tab.

    Use Add Record Type to specify the tag (record type) that starts a new row, where:

    ►Name — The name of the record type.
    ►Tag — The record type value.
    ►Delimiter — The column separator, if required. Custom values are allowed.
    ►End of Row Marker — The marker for the end of row, if required. Custom values are allowed.
    ►Text Qualifier — The symbols for the start and end of a column, if required. Custom values are allowed.

    Use Add Column to add the columns for the extracted data, where:

    ►Name — The name of the column. This value is used in the header row.
    ►Source Column — The column of the recordset.
    ►Use Text Qualifier — If required, you can choose to have a different text qualifier for this column to the rest of the row, even if no qualifier is used.
    ►From and To — If a Fixed Width Delimiter has been specified for this row, you must specify its start (From) and end (To) position in the row instead of the column length. For example, From position 25 To position 30, then the next column would start From position 31.
    ►Alignment — If a Fixed Width Delimiter has been specified for this row, choose whether the column data is aligned to the left (default) or to the right.
    ►Padding Character — By default, fixed width columns are padded with spaces if the data does not fill the whole width. You can choose a different symbol to pad out the column (Space, Hyphens, or Zeros). Custom values are allowed.
    ►Date/Time Format — If the Source Column contains date / time data, choose the output format for this column. You can either select a supplied format, or enter a custom date format — custom formats must adhere to the .NET implementation of date / time format strings, see https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings.

    To use this format of flat file, you configure the exact specifications of each column.

    To have a flat file where you specify the exact format of each column, do not select Support Multiple Record Types in File

    From the Separators panel, you choose how each column in the flat file is distinguished from its neighbours:

    ►Include header row in File inserts the header details into the top of the file.

    Choose whether the columns in the flat file are:

    ►Delimited — Columns are separated with either a Comma, Tab, Semicolon, a Space, or a custom value of your choice (Other).
    ►Fixed width — Columns have a set number of characters (specified in the Output Columns tab). If the data in the column is less than the specified width, the column is padded with spaces up to the limit.

    Choose the relevant End of Row Marker. You can also add custom markers by typing directly into the drop-down.

    Text Qualifier indicates the start and end of a column. This is particularly important where the column data could also contain the chosen Delimiter as a legitimate character, for example, where lines of an address are separated by a comma, but the address is a single column in a CSV file. Again, custom qualifiers can be used here.

    Use Suppress end of row marker on last row if using this file in a program that does not require the last row of the file to be blank, for example, an external program such as Notepad, or even a BPA Platform tool such as Print Document.

    Use Use Source Column as Column Name to have the output column names the same name as the source column. This saves time with re-keying field values.

    Go to the Output Columns tab.

    Use Generate from Input to create a flat file that uses the same column names, in the same order.

    Note: You still need to specify any text qualifiers, alignments, and column widths if using this option as this information is not available from the consumed recordset.

    Use Add Column to add the columns for the extracted data if you're not using the Generate from Input button.

    From the Column Properties pane, specify the structure of each column in your output, where:

    ►Name — The name of the column. This value is used in the header row if enabled in the File tab. Skip this parameter if generating from input.
    ►If Use Source Column as Column Name is enabled in the File tab, skip this field as it is automatically filled from the Source Column value.
    ►Source Column — The column of the recordset. Skip this parameter if generating from input.
    ►Use Text Qualifier — If required, you can choose to have a different text qualifier for this column to the rest of the row, even if no qualifier is used.
    ►From and To — If Fixed Width has been specified for this flat file, you must specify its start (From) and end (To) position in the row instead of the column length. For example, From position 25 To position 30, then the next column would start From position 31. If configuring a Delimited file, skip this parameter.
    ►Alignment — If Fixed Width has been specified for this flat file, choose whether the column data is aligned to the left (default) or to the right. If configuring a Delimited file, skip this parameter.
    ►Padding Character — By default, fixed width columns are padded with spaces if the data does not fill the whole width. You can choose a different symbol to pad out the column (Space, Hyphens, or Zeros). Custom values are allowed. If configuring a Delimited file, skip this parameter.
    ►Date/Time Format — If the Source Column contains date / time data, choose the output format for this column. You can either select a supplied format, or enter a custom date format — custom formats must adhere to the .NET implementation of date / time format strings, see https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings.

    For more information, go to the main Main tab topic.

  3. Go to the Options tab and select the relevant error handling options for this task step.

    The Options tab allows you to define how errors in this step are handled at task runtime.

    If an error occurs, you can decide whether the step should Continue processing, or terminate the step immediately (Abort Task).

  4. Click  to save the task step.