Editing Flat File Definitions - Informatica

After you import a flat file source or target definition, you may need to add business names and configure file properties. Additionally, if the file definition changes, you might want to manually edit the definition.

You can edit source or target flat file definitions using the following definition tabs:

  • Table tab. Edit properties such as table name, business name, and flat file properties.
  • Columns tab. Edit column information such as column names, datatypes, precision, and formats.
  • Properties tab. View the default numeric and datetime format properties in the Source Analyzer and the Target Designer. You can edit these properties for each
    source and target instance in a mapping in the Mapping Designer.
  • Metadata Extensions tab. Extend the metadata stored in the repository by associating information with repository objects, such as flat file definitions.

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

When you update a source or target definition, the Designer propagates the changes to any mapping using that source or target. Some changes to source and target definitions can invalidate mappings. If the changes invalidate the mapping, you must validate the mapping. You can validate mappings from the Query Results or View Dependencies window or from the Repository Navigator. You can also validate multiple objects without opening them in the workspace. If you cannot validate the mapping from these locations, you must open and edit the mapping.

When you create sessions using file source, target, or lookups, you can override some properties you define in the Designer. For example, when you create a session with a file source, you can specify a source file location different from the location you use when you import the file source definition.

Editing Table Options

You can edit the following options on the Table tab of a flat file source or target definition:

  • Business name. Add a more descriptive name to the source or target definition.
  • Description. Add a comment or link to business documentation. These display in Repository Manager for the source or target definition. Adding comments or business documentation links to a source or target is an easy way to document its purpose. You can add or modify comments to any existing source or target.
  • You can enter up to (2,000 bytes)/K characters in the description, where K is the maximum number of bytes a character contains in the selected repository code page For example, if the repository code page is a Japanese code page where K=2, each description and comment field can contain up to 1,000 characters.

  • Keywords. Track flat file targets with key words. As development and maintenance work continues, the number of targets increases. While all of these targets may appear in the same folder, they may all serve different purposes. Keywords can help you find related targets. Keywords can include developer names, mappings, or the associated schema.
  • Use keywords to perform searches in the Repository Manager.

  • Database type. Define the source or target type. Choose Flat File for flat file sources
    and targets.
  • Flat file information. When the database type is flat file, define the flat file properties by clicking the Advanced button.

To add options to a flat file source or target definition:

  1. To add options to a source definition, in the Source Analyzer, double-click the title bar of the source definition.
  2. To add options to a target definition, in the Target Designer, double-click the title bar of the target definition. The Edit Tables dialog box appears.

  3. Click the Rename button to edit the source or target name and the business name.
  4. Choose Flat File in the Database Type field.
  5. Click the Advanced button to edit the flat file properties.
  6. A different dialog box appears for fixed-width and delimited files.
  7. To add a description, type a description in the Description field.
  8. To add keywords for target definitions, click Edit Keywords.
  9. The Edit Keywords dialog box appears. Use the buttons to create and move keywords.

Editing Columns

You can edit the following information in the Columns tab of a flat file source or target definition:

  • Column Name. The names of columns in the flat file source or target.
  • File name columns. You can add a file name column to a flat file source or target. For sources, use the CurrentlyProcessedFileName column to return the names of the source files from which rows of data were read. Use this column if you configure the session to read data from a file list. For targets, use the FileName column to dynamically name flat file targets.
  • Datatype. The datatype of the column. For flat files, you can choose bigint, datetime, double, int, nstring, number, or string.
  • Precision, Scale, and Format. When importing file definitions, you often need to consider the precision, scale, field width, and format of values in each column. You can edit the field width and format by clicking in the Format column. Enter the precision, scale, and format.
  • Not Null. Choose whether you want to allow null data in the source or target.
  • Key Type. Choose NOT A KEY for flat file source and target definitions.
  • Shift Key. You can define the shift state for shift-sensitive fixed-width flat file sources that do not contain shift keys. This attribute appears when you select User Defined Shift State in the Edit Flat File Information dialog box for fixed-width files.
  • Choose Shift-In if the column contains single-byte characters. Choose Shift-Out if the column contains multibyte characters.

  • Business Name. Optionally, you can add business names to each source or target field.
  • Note:If the file columns for a source or lookup definition change significantly, you may need to reimport the file.

To edit the columns of flat file source and target definitions:

  1. To edit a source definition, in the Source Analyzer, double-click the title bar of the flat file source definition. To edit a flat file source definition, in the Target Designer, double-click the title bar of the flat file target definition.
  2. Click the Columns tab.
  3. Configure the options of the source or target definition as described above.
  4. If you want to add columns, select a column and click Add.
  5. Enter the name, datatype, and other characteristics of the column.
  6. Repeat these steps for each column you want to add to the source or target definition.
  7. If you want to move a column, use the Up and Down buttons, or drag it within the scrolling list.

Updating Fixed-Width File Properties

After you import a fixed-width file, you can update the file properties. Double-click the title bar of the source or target definition. Edit the table and column information.

To edit file properties, click the Advanced button on the Table tab. The Edit Flat File Information - Fixed Width Files dialog box appears. The Edit Flat File Information - Fixed Width Files dialog box contains more options for file sources than for file targets. For example, it contains information that the Integration Service needs to read the file, such as the number of initial rows to skip, or the number of bytes between rows.

The following table describes the fixed-width file properties that you can configure for source, target, and lookup definitions:

Updating Fixed Width File PropertiesUpdating Fixed Width File Properties

Null Character Handling

You can specify single-byte or multibyte null characters for fixed-width file sources. When reading a fixed-width source file, the Integration Service uses these characters to determine if a column is null. When writing to a fixed width target file, the Integration Service uses these characters to represent null values.

The following table describes how the Integration Service uses the Null Character and Repeat Null Character properties to determine if a column is null:

null character handelingnull character handeling

Updating Delimited File Properties

After you import a delimited file, you can update the file properties. Double-click the title bar of the source or target definition. Edit the table and column information.

To edit file properties, click the Advanced button on the Table tab. The Edit Flat File Information - Delimited Files dialog box appears. The Edit Flat File Information - Delimited Files dialog box contains more options for file sources than for file targets. For example, it contains information that the Integration Service needs to read the file, such as the number of initial rows to skip or the escape character.

The following table describes the delimited file properties that you can configure:

Updating Delimited File PropertiesUpdating Delimited File PropertiesUpdating Delimited File Properties

Rules and Guidelines for Delimited File Settings

Delimited files are character-oriented and line sequential. Use the following rules and guidelines when you configure delimited files:

  • The column and row delimiter character, quote character, and escape character must all be different for a source definition. These properties must also be contained in the source or target file code page.
  • The escape character and delimiters must be valid in the code page of the source or target file.
  • Use the following rules and guidelines when you configure delimited file sources:
  • In a quoted string, use the escape character to escape the quote character. If the escape character does not immediately precede a quote character, the Integration Service reads the escape character as an ordinary character.
  • Use an escape character to escape the column delimiter. However, in a quoted string, you do not need to use an escape character to escape the delimiter since the quotes serve this purpose. If the escape character does not immediately precede a delimiter character, the Integration Service reads the escape character as an ordinary character.
  • When two consecutive quote characters appear within a quoted string, the Integration Service reads them as one quote character. For example, the Integration Service reads the following quoted string as I’m going tomorrow:
  • 2353,‘I’’m going tomorrow’MD
  • The Integration Service reads a string as a quoted string only if the quote character you select is the first character of the field.
  • If the field length exceeds the column size defined in the Source Qualifier transformation, the Integration Service truncates the field.
  • If the row of data exceeds the larger of the line sequential buffer length or the total row size defined in the Source Qualifier transformation, the Integration Service drops the row and writes it to the session log file. To determine the row size defined in the Source Qualifier transformation, add the column precision and the delimiters, and then multiply the total by the maximum bytes per character.

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

Informatica Topics