Follow-On Topics

Related Topics

# About the Main Tab

About the Format as Flat File Tool

The Format as Flat File tool takes a King Task Centre recordset and converts it into 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.

At the time of writing, only King Task Centre recordsets can be formatted as flat files.

You use the Main tab to specify what data is extracted from the recordsets, and how the flat file is structured.

TIP:

When configuring the flat file structure, remember it is to fit the consuming application or step, rather than matching where it came from. For example, if an address field from the exposed recordset takes up 100 characters, but the application consuming the flat file only has a 50 character field, your configuration here should be for 50 characters only,.

## Adding a New Task Step (cont.)

You use the File tab to determine the format of the flat file, and the Output Columns tab to determine the structure.

Using the File Tab

Supported Flat File Outputs At the time of writing, the following flat file formats are supported:
|  | Delimited — Where a nominated character determines the start and end of a data column. Comma-delimited files (`.CSV`) are the most common.
|  | Fixed width — Where each data column has a specific character length and position. For example, column 1 is characters 1 to 2, column 1 is 3 to 10, and so on.
With the above two formats, all outputted data is structured the same way. The Format as Flat File tool supports an additional output format, where you can choose how to output certain types of data:
|  | Supports multiple record types — As above, but using a record type to determine how the outputted row is structured. For example, if column 1 contains "ABC" then output as this, if it contains "DEF" then output as that.
Note that with this type of flat file, you can output a file that contains a mixture of delimited and fixed width rows. Taking the previous example, you can have "ABC" records outputted as delimited and "DEF" as fixed width but contained in the same file. From the One Document panel, specify when a new flat file 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 from the drop-down list.
|  | New document for each row — Generate a new document each time a new row is created.
From the Output Generation panel, you specify the file structure: | Click the image to view full size.

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
What's a Tag Column / Record Type?

Tag is the name given to the data the Format as Flat File step acts upon. In the configuration, the 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.

1. From the Tag Column drop-down, choose the recordset column that contains the tag record type.
2. All other options are not used for this type of flat file and are greyed out.
3. Click on Output Columns.

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

1. From the Separators panel, 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.

1. 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.

1. Choose the relevant End of Row Marker from the drop-down list. You can also add custom markers by typing directly into the drop-down.
2. From the drop-down list, select the relevant Text Qualifier to indicate 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.

3. Enable 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 King Task Centre tool such as Print Document.
4. Enable 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.
5. Click on Output Columns.

Using the Output Columns Tab
If you've chosen to Support Multiple Record Types in File, the Output Columns tab displays as:

Click 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.
| Click the image to view full size.

7. Click 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. Supplied formats include:

Date/Time Format | Example
--- | ---
d/M/yyyy hh:mm:ss | 20/9/2018 09:30:00 (12 hour clock, without AM/PM notations)
M/d/yyyy hh:mm:ss | 9/20/2018 09:30:00 (12-hour clock, without AM/PM notations)
d/M/yyyy h:mm:ss tt | 20/9/2018 9:30:00 AM
M/d/yyyy h:mm:ss tt | 9/20/2018 9:30:00 PM
d/M/yyyy HH:mm:ss | 20/9/2018 09:30:00 (24-hour clock)
M/d/yyyy HH:mm:ss | 9/20/2018 21:30:00 (24-hour clock)
d/M/yyyy | 20/9/2018
M/d/yyyy | 9/20/2018
hh:mm:ss | 09:30:00 (12-hour clock, without AM/PM notations)
h:mm:ss tt | 9:30:00 AM
dddd, MMMM dd, yyyy | Thursday, September 20, 2018
dddd, MMMM dd, yyyy h:mm:ss tt | Thursday, September 20, 2018 9:30:00 AM
dddd, MMMM dd, yyyy hh:mm:ss | Thursday, September 20, 2018 09:30:00 (12-hour clock, without AM/PM notations)
yyyy'-'MM'-'dd'T'HH':'mm':'ss | 2018-09-20T21:30:00 (24-hour clock) "T" is a delimiter between the date and time sections, and is a required character for this format.
yyyy'-'MM'-'dd HH':'mm':'ss'Z' | 2018-09-20 21:30:00+0100 (24-hour clock) "Z" represents the time-zone offset from GMT. In the example above, it is 21:30 in the GMT+1 time-zone.

1. Repeat the above until all record types and their relevant columns have been added.
2. Use the arrow buttons in the middle to change the position of the columns as required.
3. Use the Remove button for any mistakes.
4. Click the Options tab (continue to the Options tab's help).

If you did not select Support Multiple Record Types in File, the Output Columns tab displays as:

Click 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. Click 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: | Click the image to view full size.

|  | 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. Supplied formats include:

Date/Time Format | Example
--- | ---
d/M/yyyy hh:mm:ss | 20/9/2018 09:30:00 (12 hour clock, without AM/PM notations)
M/d/yyyy hh:mm:ss | 9/20/2018 09:30:00 (12-hour clock, without AM/PM notations)
d/M/yyyy h:mm:ss tt | 20/9/2018 9:30:00 AM
M/d/yyyy h:mm:ss tt | 9/20/2018 9:30:00 PM
d/M/yyyy HH:mm:ss | 20/9/2018 09:30:00 (24-hour clock)
M/d/yyyy HH:mm:ss | 9/20/2018 21:30:00 (24-hour clock)
d/M/yyyy | 20/9/2018
M/d/yyyy | 9/20/2018
hh:mm:ss | 09:30:00 (12-hour clock, without AM/PM notations)
h:mm:ss tt | 9:30:00 AM
dddd, MMMM dd, yyyy | Thursday, September 20, 2018
dddd, MMMM dd, yyyy h:mm:ss tt | Thursday, September 20, 2018 9:30:00 AM
dddd, MMMM dd, yyyy hh:mm:ss | Thursday, September 20, 2018 09:30:00 (12-hour clock, without AM/PM notations)
yyyy'-'MM'-'dd'T'HH':'mm':'ss | 2018-09-20T21:30:00 (24-hour clock) "T" is a delimiter between the date and time sections, and is a required character for this format.
yyyy'-'MM'-'dd HH':'mm':'ss'Z' | 2018-09-20 21:30:00+0100 (24-hour clock) "Z" represents the time-zone offset from GMT. In the example above, it is 21:30 in the GMT+1 time-zone.

9. Repeat the above until all record types and their relevant columns have been added.
10. Use the arrow buttons in the middle to change the position of the columns as required.
11. Use the Remove button for any mistakes.
12. Click the Options tab (continue to the Options tab's help).

Copyright © 2018 Quadrant Software B.V. All rights reserved.