Excel Source in Informatica

In this article, we will show you, How to import data from Excel workbooks and use them as an Excel source in Informatica Source Analyzer. Before we start importing data from the excel file, let us see the data inside the excel sheet.

The following screenshot will show you the data inside the excel file. Our task is to import this excel workbook and use this data as the Excel Source in Informatica. 

Excel 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 Database.. option as shown below  

Once you select the Import from Database.. option, Import tables window will be opened as shown below. Currently, we don’t have any ODBC connection to select from the drop-down list. In order to add a new one, please click on the … button as shown below

Once you click on the … button, ODBC Data Source Administrator Window will be opened to add a new ODBC connection. Click on the Add button to do so. 

Once you click on the Add button, Create New Data Source window will be opened. Here we have an option to select the required driver from the available list. As we said before, we need to import excel files so we are selecting the Driver to Microsoft Excel for this example.

You can also select the Microsoft Excel Driver as shown below  

This will open the new pop up window called ODBC Microsoft Excel Setup. Here we have to fill the required details:

• Data Source Name: Please specify a unique name for this Excel Source in Informatica.
• Description: Please specify the valid description of the connection we are going to establish.
• Version: Please specify the Excel file version.

Next, Click on the Select workbook.. button to select the required excel file

Once you click on the Select workbook.. button, the following window will be opened to select the required workbooks from our local file system. Once you are done selecting, Click the OK button to close the window

From the drop-down list, Please select the ODBC connection that we created now.

Next, we have to select the required tables from the Excel workbooks. From the below screenshot you can observe that Multi_Cast book has only one table and we are selecting the same. Here you can select more than one table also.

TIP: If you find difficult to track your required table(s) then, you can type the table name under the Search for tables named and click on the search button

From the below screenshot you can observe that under the Source subfolder you can see our newly created Excel source in Informatica and the table definition (Column Names and appropriate data types) inside our workspace.

Preview Data inside Our Excel Source

Although we created our Excel 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. Here you have to provide the credentials (if there are any) required to connect with your local file system and click the connect button. Once you successfully connected, data inside the table (here Multi_cast table) will be displayed as shown below

Edit existing Excel Source in Informatica

Although we selected the Multi_Cast.xls 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.

Selecting the Edit.. option open a new window called Edit tables as shown 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. Once you are done editing, Click OK to close the Edit tables window

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