How to Use Format as Flat File to Output a Complex Multi-Record Type File

Introduction

Typically, flat files outputted by BPA Platform contain records from the same data source all using the same structure. This article provides guidance for creating a multi-record-format flat file 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. output.

Required Tools

Database Query tool — it doesn't matter if it's OLEDB or ODBC
Format as Flat File 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.
An Output tool such as Save File 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.

The Data

This article uses the example of order headers and order lines (multiple lines to a single header).

We are combining the following data:

Order Header:

order_number|Order_date|CustomerID

ACME001|01 Oct 2019 13:00|CUST001
ACME002|02 Oct 2019 14:00|CUST002
ACME003|10 Oct 2019 12:00|CUST001

Order Lines:

Order_Number|line_id|Article_sku|QTY

ACME001|001|SKU001|1
ACME001|002|SKU005|5
ACME002|001|SKU010|10
ACME003|001|SKU050|1
ACME003|002|SKU003|2
ACME003|003|SKU100|1

Structuring the Database Query

The recordset required by the Format as Flat File tool must contain records from both sources, formatted into the same structure. To do this, you use the CAST and UNION functions to join tables and columns that use different data types. Using the above data example, our SQL statement would be as follows (using the free-type mode):

► Linetype is a new column that we have created to help us identify which records are order headers (OH) and which are order lines (OL). This is particularly important if you are wanting to split the output from the Format as Flat File according to a value in the record.

► Use CAST to change the data type of a column where the columns being merged are of different data types. Note that we haven't used CAST on the order_number column. This is because the data format of this column is exactly the same in both tables.

► We've used RTRIM here to remove the trailing spaces we have in our Article_sku column to ensure it fits into the new data type of nvarchar(20).

►

Use NULLs to ensure each record in the recordset contains the correct number of columns according to the largest table you are querying.

►

Ordering your recordset data is very important to ensure the desired output is achieved, especially when splitting the output.

Using the above data, our recordset data would be as follows:

OH|ACME001|01 Oct 2019 13:00|CUST001|NULL
OL|ACME001|001|SKU001|1
OL|ACME001|002|SKU005|5
OH|ACME002|02 Oct 2019 14:00|CUST002|NULL
OL|ACME002|001|SKU010|10
OH|ACME003|10 Oct 2019 12:00|CUST001|NULL
OL|ACME003|001|SKU050|1
OL|ACME003|002|SKU003|2
OL|ACME003|003|SKU100|1

Formatting the Outputted Document

Using the Format as Flat File tool, we can choose to output all records to a single document or output multiple documents based on a key value.

Creating a Single Document

To create a single document containing all records in the recordset, use One Document in the Main tab of the Format as Flat File tool. The file contents would be as shown above.

Choose a Delimiter as the Separator instead of fixed width. If column names are required as the first row, enable Include header row in File. All other options can be left in their default state.

To speed up creating the output file, enable Use Source Column as Column Name then from the Output Columns tab, click Generate from Input. You can then adjust the column names as required rather than having to remember what was specified in the SQL statement.

Creating Multiple Documents

To create multiple documents, you need to know the key column, that is, the one that links the data together. For this scenario, this is the order number column now known as col1.

From the Main tab of the Format as Flat File tool, choose New Document when column changes and select the key column in the provided drop-down. Because we ordered our data according to the order number (col1) then Linetype, the output would be:

Document 1:

OH|ACME001|01 Oct 2019 13:00|CUST001|NULL
OL|ACME001|001|SKU001|1
OL|ACME001|002|SKU005|5

Document 2:

OH|ACME002|02 Oct 2019 14:00|CUST002|NULL
OL|ACME002|001|SKU010|10

Document 3:

OH|ACME003|10 Oct 2019 12:00|CUST001|NULL
OL|ACME003|001|SKU050|1
OL|ACME003|002|SKU003|2
OL|ACME003|003|SKU100|1

Without the ordering, we run the risk of outputting multiple documents for a single order number, or having order lines in the wrong order document.

Choose a Delimiter as the Separator instead of fixed width. If column names are required as the first row, enable Include header row in File. All other options can be left in their default state.

Outputting the Multi-Record Document

Any tool capable of consuming from the Format as Flat File tool can output the multi-record document — refer to the product help (Format as Flat File Technical Summary). It is worth noting that a physical copy of the file does not exist until consumed by an Output tool, therefore should you need to upload it to a web server for example, have a Save File step before the File Management Use the File Management tool to carry out a number of automated actions on single or multiple files — move, copy, rename, and delete — either locally or across the network. Uploads to and downloads from an FTP server are also supported. task step.