Thursday, April 18, 2013

Import Flat File Definitions

How to Import Flat File Source and Target Definitions

Import Flat File Source Definition:
1. Connect and Open the folder where you want to import flat file definition. If it not exist in the folder.
2. Select Tools --> Source Analyser
3. Click on Source Menu --> Import from File
4. It will ask you File Path
          Note: Change Files of types: All Files (*.*) to see all types of files.

4. Browse the File path and select the File.
5. Click 'OK'.
6. Flat File Import Wizard - Step 1 of 3 will appears.

  • Choose the Flat File Type is either Delimited or Fixed Width.
  • Enter a name for this source: EMPLOYEE
  • Start Import at Row: 2. If you specify to start at row 2, the Flat File Wizard skips 1 row before reading.
  • Select 'Import field names from first line', If you want to import the port name from the 1st row of the flat file.
  • Click 'Next'
 7. Flat File Import Wizard - Step 2 of 3 will appears.
  • Select the Delimited: Comma (in my case). You can choose Tab, Semicolon, Comma, Space, Other (Character used to separate columns of data).
  • 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.
  • Click 'Next'.
Note: Treat Consecutive Delimiters as One, Treat Multiple Delimiters as AND, Escape Character are optional.

8. Flat File Import Wizard - Step 3 of 3 will appears.
  • 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.
  1. For numeric columns, Precision is the number of significant digits. The Flat File Wizard ignores the width for numeric columns in delimited files.
  2. 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.
  3. 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.
Note: Only characters 0 to 9 are considered numeric. Columns that contain multibyte character set numbers, such as Japanese, are considered text.
  • Click 'Finish'
 9. Flat File is Imported.

Import Flat File Target Definition:
1. Select Tools --> Target Designer
2. Select Target from Menu --> 'Import from File' and follow step from 4 to 8.

Note: You can drag source definition into target designer but not target definition into Source analyzer.

3 comments:

  1. hello all,
    if i am getting data like
    1,abc,def,pqr,300
    2,add,jgl,200
    in row 1 abc,def belongs to single column . here (,) is some time also included in value and it is also working as separator.
    target is
    column1 column2 column3 column4
    1 abc,def pqr 300

    ReplyDelete
  2. Hi Saurabh,

    Incase your input file is comma separator and your getting comma as value then you have to use field separator as comma and single/doubt quotes from text qualifer (you can see snapshot at step 7)

    your input should be like:
    1,'abc,def,pqr',300.

    Then you dont have any problem.

    Thanks,
    Gowtham

    ReplyDelete