Table of Contents

Search

  1. Preface
  2. Using the Designer
  3. Working with Sources
  4. Working with Flat Files
  5. Working with Targets
  6. Mappings
  7. Mapplets
  8. Mapping Parameters and Variables
  9. Working with User-Defined Functions
  10. Using the Debugger
  11. Viewing Data Lineage
  12. Comparing Objects
  13. Managing Business Components
  14. Creating Cubes and Dimensions
  15. Using the Mapping Wizards
  16. POWERCENTERHELP
  17. Datatype Reference
  18. Configure the Web Browser

Importing Delimited Flat Files

Importing Delimited Flat Files

Delimited flat files are always character-oriented and line sequential. The column precision is always measured in characters for string columns and in significant digits for numeric columns. Each row ends with a newline character. You can import a delimited file that does not contain binary data or multibyte character data greater than two bytes per character.
Complete the following steps to import a delimited file for either a source or target definition:
  1. To import a source definition, open the Source Analyzer, and click
    Sources
    Import from File
    . To import a target definition, open the Target Designer and click
    Targets
    Import from File
    .
    The
    Open Flat File
    dialog box appears.
  2. Select the file you want to use.
  3. Select a code page.
    When you import a flat file source definition, select a code page that matches the code page of the data in the file.
  4. Click
    OK
    .
    The contents of the file appear in the preview window at the bottom of the Flat File Wizard.
  5. Edit the following settings:
    Delimited Flat File Wizard, Step 1 of 3
    Description
    Flat File Type
    File type. Select Delimited for a delimited file.
    Enter a Name for This Source
    Name of the source. This is the name of the source definition in the repository. You can use the file name or any other logical name.
    Start Import At Row
    Indicates the row number at which the Flat File Wizard starts reading when Flat File Wizard imports the file. For example, if you specify to start at row 2, the Flat File Wizard skips 1 row before reading.
    Import Field Names From First Line
    If selected, the Designer uses data in the first row for column names. Select this option if column names appear in the first row. Invalid field names are preceded by “FIELD_”.
  6. Click
    Next
    .
    Any incorrectly parsed fields display in red text in the file preview window at the bottom of this screen.
  7. Enter the following settings:
    Delimited Flat File Wizard, Step 2 of 3
    Description
    Delimiters
    Character used to separate columns of data. Use the Other field to enter a different delimiter. Delimiters must be printable characters and must be different from the escape character and the quote character if selected. You cannot select unprintable multibyte characters as delimiters.
    Treat Consecutive Delimiters as One
    If selected, the Flat File Wizard reads one or more consecutive column delimiters as one. Otherwise, the Flat File Wizard reads two consecutive delimiters as a null value.
    Treat Multiple Delimiters as AND
    If selected, the Flat File Wizard reads a specified set of delimiters as one.
    Escape Character
    Character immediately preceding a column delimiter character embedded in an unquoted string, or immediately preceding the quote character in a quoted string. When you specify an escape character, the Integration Service reads the delimiter character as a regular character. This is called
    escaping
    the delimiter or quote character.
    Remove Escape Character From Data
    This option is selected by default. Clear this option to include the escape character in the output string.
    Use Default Text Length
    If selected, the Flat File Wizard uses the entered default text length for all string datatypes.
    Text Qualifier
    Quote character that defines the boundaries of text strings. Choose No Quote, Single Quote, or Double Quotes. If you select a quote character, the Flat File Wizard ignores delimiters within pairs of quotes.
  8. Click
    Next
    .
  9. Enter column information for each column in the file.
    To switch between columns, select a new column in the Source Definition pane or Target Definition pane, or click the column heading in the file preview.
    Delimited Flat File Wizard, Step 3 of 3
    Description
    Name
    Port name that you want to appear for each column. If you select Import Field Names from First Line, the wizard reads the column names provided in the file instead.
    Datatype
    Column datatype. Select Text, Numeric, or Datetime, and then enter the appropriate Length/Precision, Scale, and Width.
    For numeric columns, Precision is the number of significant digits. The Flat File Wizard ignores the width for numeric columns in delimited files.
    For Text columns, Precision is the maximum number of characters contained in the source field or the target field. The Flat File Wizard ignores the precision when reading text columns in or writing text columns to delimited files.
    By default, the Flat File Wizard enters the same value for both Precision and Width. You can change the value of the precision or width, but the Flat File Wizard only lets you define the precision to be greater than or equal to the width.
    Only characters 0 to 9 are considered numeric. Columns that contain multibyte character set numbers, such as Japanese, are considered text.
  10. Click
    Finish
    .
    If the file size exceeds 256 KB or contains more than 16 KB of data for each line, verify that the Flat File Wizard imports the file with the correct field precision. If not, adjust the field precision in the Flat File Wizard or in the imported definition.


Updated June 25, 2018