Importing data from a flat file

You can analyze data that is in flat files by importing the metadata about the file into the metadata repository with InfoSphere® Metadata Asset Manager. A QETXT.INI file that specifies the format of the data in the flat files must exist before you import the metadata. You can create or update this file with the InfoSphere Information Analyzer Identify Flat File wizard.

Before you begin

  • An ODBC connection must be defined on the engine tier with the DataDirect Text driver.
  • The ODBC connection on the engine tier must point to the directory in which the flat files are stored.

About this task

You can use the Identify Flat File wizard to specify the format of the data that is in your flat file, which allows you to import your flat file data into your projects. When you use the Identify Flat File wizard, you specify either the fixed-widths of the data in your flat file, or you specify the delimiter that your data is separated by, such as commas, spaces, and colons. You can then preview the way the wizard will parse your data. After displaying a preview of how the data in your text file is parsed, and how it will look when it is uploaded into a table in your data source, the wizard automatically saves these settings in the QETXT.INI file on your system. Once the QETXT.INI file is configured by the wizard, you can import the data by using InfoSphere Metadata Asset Manager.
Note: You can also create or update the QETXT.INI file with a text editor.

Procedure

  1. In the InfoSphere Information Analyzer client, select HOME > Metadata Management > Import Metadata.
  2. In the Import Metadata workspace, in the Data Sources window, select the data source that you want to upload the flat file to. The data source must contain at least one schema.
  3. From the task list on the right side of the workspace, click Identify Flat File. After you click Identify Flat File, a wizard is displayed.
  4. In the Identify Flat File wizard, in the Steps to Complete task list on the left, follow the tasks.
  5. From the list displayed in the Select File to Import pane, locate the file that you want to import, or click Add if you want to add a new flat file.
    For the flat files to be displayed in the list, the ODBC connection on the engine tier must point to the directory in which the flat files are stored.
  6. Select the file that you want to import, and click Next.
  7. Select one of the following field separators on the Specify File Layout screen:
    • Delimited
      Select the character that is used to separate the data in your flat file. You can choose from the following options:
      • , (Comma)
      • Tab
      • | (Pipe)
      • Space
      • Other
    • Fixed Width
      Select this option if each column's data has the same width. Each column individually might have a different width. You can create a list of your column widths, separated by commas.
  8. Optional: Select the First line is column names check box if your column names are in the first line of the text file.
  9. Optional: Click Update Preview when you are done specifying the delimiters used to separate your data to preview how the columns will be parsed. The preview displays the first few rows of data based on the settings you specified in the Field Separation area. Make sure the preview looks correct, and make appropriate changes until your data preview looks accurate.
  10. Click Next.
  11. Click Update Preview in the Edit Column Details workspace to verify that your setting selections are showing up correctly. The preview lists the first few rows of data, divided into columns as specified by the options on the page. You can resize columns in this view. You can change the column width values, and view the changes in the preview view.
  12. Click Next.
  13. In the Name field in the Save File Definition workspace, specify the table name, or logical file name, that you want to assign to the flat file
  14. Click Finish to configure additional flat files. Click Finish and Close to save the flat file configuration and exit the Identify Flat File wizard..

What to do next

The format of the flat file that you want to import is now saved in the QETXT.INI file for the data source that you want to import your flat file to. Import the metadata for the flat file by using InfoSphere Metadata Asset Manager.