Formatting Flat File Columns - Informatica

When you import or edit flat file definitions, you need to define the following column options:

  • Precision. Precision is defined differently for different datatypes.
  • Scale. Scale is the maximum number of digits after the decimal point for numeric values.
  • Field width. Field width is the number of bytes the Integration Service reads from or
    writes to a file. Field width applies to fixed-width file definitions only. Field width must be greater than or equal to the precision.
  • The following table describes precision and field width for flat file definitions:

    precision and field width for flat file definitions

  • Format. Format defines the appearance of numeric and datetime values.
  • For datetime values, you can choose to output only the date or time parts of the data. For example, you can configure the file definition for datetime data with the following format:

    03/2002

    For numeric values, you can choose thousands and decimal separators. For example, you can configure the file definition for numeric data with the following format:

    1.000.000,95

    Note:If the source file structure changes significantly, you may need to reimport the
    flat file source definition.

You can define the appearance of number and datetime columns at the following locations:

- Source or target field. You can define the format for individual columns on the Columns tab for the source in the Source Analyzer, or for the target in the Target Designer.

- Source or target instance in a mapping. You can define the default datetime and numeric formats for a source or target instance in a mapping in the Mapping Designer. The Integration Service uses the default formats you define when you do not define a format for an individual column.

Formatting Numeric Columns

When you edit flat file definitions, you can define formats for numeric values with the Number datatype. Bigint, double, and integer datatypes have a default precision, scale, and format. You can change the precision for a column that is an integer or double datatype.

Use the Format column on the Columns tab of a flat file source or target definition to define the format for numeric values.

You can define the following formatting options for numeric values in the Column Format Settings dialog box:

  • Numeric data
  • Field width

When you define formatting options in the Column Format Settings dialog box, the Designer shows the options you define in the Format column on the Columns tab.

Numeric Data

You can define decimal and thousands separators in the numeric data area of the Column Format Settings dialog box. For the decimal separator, you can choose a comma or a period. The default is the period. For the thousands separator, you can choose no separator, a comma, or a period. The default is no separator.

To specify numeric separators, click Override Separators and choose a separator from the Decimal Separator and Thousands Separator fields. You can override one or both separators. When you override the separators, you must choose different options for each.

For example, the source data contains a numeric field with the following data:

9.999.999,00
5.000.000,00

Choose the period as the thousands separator, and choose the comma as the decimal separator in the flat file source definition.

For example, you want to output the data above to a file target with the following format:
9,999,999.00

Choose the comma as the thousands separator, and choose the period as the decimal separator in the flat file target definition.

Field Width

You can change the field width in the Column Format Settings dialog box by padding the width or by defining a fixed-width value in bytes. By default, the field width equals the precision.

To output numeric data to a fixed-width flat file, you must configure the field width for the target field to accommodate the total length of the target field. If the data for a target field is too long for the field width, the Integration Service rejects the row and writes a message to the session log. When you configure the field width for flat file target definitions, you must accommodate characters the Integration Service writes to the target file, such as decimals and negative signs.

To adjust the field width, select Adjust Width and enter the number of bytes in the Padding field. When you adjust the field width, the Integration Service defines the field width as the field precision plus the padding you enter. For example, when the precision of a field is 10 and you enter 5 in the Padding field, the Integration Service reads 15 bytes from a file source, and it writes 15 bytes to a file target.

To fix the field width, select Fixed Width and enter the number of bytes in the Field Width field. The Designer lets you enter a field width greater than or equal to the precision. When you enter 20 in the Field Width field, the Integration Service reads 20 bytes from a file source, and it writes 20 bytes to a file target.

For example, you have a target field with a precision of four, scale of zero. You want to add two blank spaces to the target field to make the target file easier to view. Select Adjust Width and enter 2 in the Padding field. Or, select Fixed Width and enter 6 in the Field Width field.

When padding numeric data in flat file targets, the Integration Service adds blank spaces to the left side of the target column.

Note:You might adjust the field width for a target field if you think another user might change the precision of the field. When the precision changes, the field width adjusts accordingly.

Format Column

When you override the numeric separators or define the field width, the Designer shows the options you define in the Format column on the Columns tab. For example, you have a numeric column with a precision of five. Click the Format column and define the options in the Column Format Settings dialog box.

The following figure shows the format options the Designer displays in the Format column:

Designer displays in the Format column

The Designer displays N,NNN(F 6.5).NN in the Format column for the PRICE column. “N,NNN” displays the thousands separator you specified. “F” indicates a fixed field width. This displays “A” when you adjust the field width. “6.5” is the field width and precision in bytes. The first number is the field width, the second is the precision. “.NN” displays the decimal separator you specified.

Note:If you do not define decimal and thousands separators for a particular source or target field, the Integration Service uses the separators you specify in the source or target instance in the Mapping Designer.

Formatting Datetime Columns

When you edit flat file definitions, you can define formats for datetime values. Use the Format column on the Columns tab of a flat file source or target definition to define the format for datetime values.

You can define the following formatting options for datetime values in the Column Format Settings dialog box:

  • Format string
  • Field width

When you define formatting options in the Column Format Settings dialog box, the Designer shows the options you define in the Format column on the Columns tab.

Format String

You can enter any datetime format in the Format String field in the Column Format Settings dialog box. For example, you can specify the datetime format as MM/YYYY. Or, you can specify the time only, for example, HH24:MI.

To specify the datetime format, choose Format String and enter the format in the Format String field. You can choose a format from the list, or you can enter a format using the keyboard. The default format is MM/DD/YYYY HH24:MI:SS, which has a field width of 19.

For example, the source data contains a datetime field with the following data:
11/28/2002
10/15/2003

Enter the following format in the flat file source definition: MM/DD/YYYY.

For example, you want to output the data above to a file target with the following format:
28-11-2002
15-10-2003

Enter the following format in the flat file target definition: DD-MM-YYYY.

You can also enter any single-byte or multibyte string literal in the Format String field. To enter a string literal, enclose it in double quotes (“”). When you enter string literals in the format string, the Integration Service writes the strings to the file target when it runs the session. You might want to add string literals to describe the different date parts.

For example, you enter the following text in the Format String field:
“Month”MM/”Day”DD/”Year”YYYY

When you run the session and the Integration Service outputs the date October 21, 2002,
it writes the following to the target file:
Month10/Day21/Year2002

Field Width

You can define the field width after you define a format string. You can change the field width by padding the width or by defining a fixed-width value in bytes. By default, the field width equals the precision.

To adjust the field width after you enter a format string, select Adjust Width and enter the number of bytes in the Padding field. When you adjust the field width, the Integration Service defines the field width as the number of bytes required for the datetime format plus the padding you enter. For example, when the datetime format is MM/ YYYY and you enter 5 in the Padding field for a flat file source, the Integration Service reads 12 bytes from the
file. When the datetime format is MM/YYYY and you enter 5 in the Padding field for a flat file target, the Integration Service writes 12 bytes to the file.

When you use Adjust Width, the Integration Service adjusts the field width based on the format string. You can change the format string without manually adjusting the field width.

To fix the field width after you enter a format string, select Fixed Width and enter the number of bytes in the Field Width field. You must specify a fixed-width value greater than or equal to the number of bytes required for the datetime format or the Integration Service truncates the data. For example, when the datetime format is MM/DD/YYYY HH24:MI:SS.NS, specify a fixed-width value greater than or equal to 29. When you enter 21 in the Field Width field, the Integration Service reads 21 bytes from a file source, and it writes 21 bytes to a file target.

For example, you have a target field with a datetime format of MM/DD/YYYY, which requires 10 bytes. You want to add two blank spaces to the target field to make the target file easier to view. Select Adjust Width and enter 2 in the Padding field. Or, select Fixed Width and enter 12 in the Field Width field.

Note:When padding datetime data in flat file targets, the Integration Service adds blank spaces to the right side of the target column.

Format Column

When you choose a date time format or define the field width, the Designer shows the options you define in the Format column on the Columns tab. For example, you define the options in the Column Format Settings dialog box.

The following figure shows the options the Designer displays in the Format column:

format coloumn

The Designer displays A 30 MM/DD/YYYY HH24:MI:SS.US in the Format column for the DATE port. The “A” indicates the field width is adjusted. The “30” is the field width in bytes: 26 bytes for precision to the microsecond, plus four bytes for padding.

Note:If you do not define a datetime format for a source or target field, the Integration Service uses the datetime format you specify in the source or target instance in the mapping.

Defining Default Datetime and Numeric Formats

When you use a flat file source or target definition in a mapping, you can define the default formats for datetime and number columns in the file. The Integration Service uses the default formats you define when you do not define the format for an individual column.

If you do not define the default formats, the Integration Service uses the following formats:

Defining Default Datetime and Numeric Formats


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Informatica Topics