Flat File Source in Informatica

In this article, we will show you, How to import data from flat files of both Delimited type, Fixed Width type and use them as Flat File source in Informatica Source Analyzer. Before we start importing data from the flat file, let us see the data inside the flat file.

The following screenshot will show you the same and our task is to import this comma delimited text file as the Flat File Source in Informatica. 

Flat File Source in Informatica

Before we start doing anything, First connect to Informatica repository service with your Admin credentials and then Navigate to Source Analyzer. Please refer Informatica Source Analyzer article to understand the Source Analyzer. From the below screenshot you can observe that we are in the Source Analyzer.

NOTE: Source Menu in the Menu bar will be available only when you are in the Source Analyzer section otherwise, it will be hidden

Once you are in Source Analyzer, Please navigate to Source menu and select the Import from File.. option as shown below  

Once you select the Import from File.. option, a new window called Open Flat Filewill be opened as shown below. Please select the required flat file from your local file system and click the Open button. For this example, we are selecting the GeoImport.txt file.

Once you click the Open button, a new pop up window called Flat File Import Wizard will be opened as shown below. This wizard can help us to specify the text format easily. First, Under Choose the file type that best describes your datasection we have to choose either a Delimited or Fixed Width option. Our text is separated by comma delimiter as we shown earlier so we are selecting a Delimited option. 

If you flat file contains column names as first line of data like our example file then, you have to change the Start Import at Row value from 1 to 2 otherwise keep it as 1. If you forgot to change then, while exporting this data to the database then Type casting errors will throw.

Import filed names from the first line: This option allows us to import the column names from the first line of a flat file. If your flat file contains column names as the first line of data like our example file then, by selecting this option will import column names and this will automatically change the Start Import at Row value from 1 to 2

Within the Step 2:

• Under the Delimiter section, please select the delimiter used in the text file. Our text is separated by comma delimiter as we shown earlier so we are selecting Comma.
• Under the Text Qualifier section, we are selecting No quotes because our flat file doesn’t have any quotes. Please change as per your requirements.

Here, we can edit the Column Name, Data type, Length or Precision, Scale and Width. In general, we don’t do that but if you want you can do it.

From the below screenshot you can observe that, under the Source sub folder you can see our newly created Flat File source in Informatica and the table definition (Column Names and appropriate data types) inside our work space.

Preview Data inside Our Flat File

Although we created our Flat File source, before we proceed further we should preview our data. In order to preview the source data, Right click on the table definition and select the Preview data.. option from the context menu.

Once you select the Preview data.. option, Preview data window will be opened. Please select the GeoImport.txt file as a Flat File name from your local file system and click the Open button.

Once you successfully selected your file, data inside the flat file (here GeoImport.txt) will be displayed as shown below

Edit existing Flat File Source in Informatica

Although we selected the GeoImport.txt file as source definition, there are many situations where we may not require all the columns present in that table or we may have to add extra columns. To resolve these type of situations, we can edit the table by Right click on the table definition and selecting the Edit.. option from the context menu.

Once you select the Edit.. option, a new window called Edit tables will be opened as show below. In order to alter or change the existing table, we have to navigate to Columns Tab.

For example, if you want to add new columns then, Please select the first button (before scissors symbol) and add Column Name, Data Type, Precision (if required), Scale (if required), If it is primary key then change the Key type to primary and check mark the Not Null option (if your column doesn’t allow null values)

If you want to delete columns then select the unwanted columns and click the scissors button as shown below. Here we are removing a few columns for the demonstration purpose. Once you done editing, Click OK to close the Edit tableswindow

From the below screenshot you can observe that, we successfully deleted the unwanted columns from our flat file source definition.

NOTE: We are removing the columns only from the Informatica Source Analyzer. This will not reflect the underlying flat file.