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 File to import to PowerCenter Designer 1

Excel Source in Informatica

Before we start the Informatica Excel Source demo, First connect to the repository service with your Admin credentials and then Navigate to Source Analyzer.

Please refer Source Analyzer article to understand the Source Analyzer. From the below Informatica screenshot, you can observe that we are in the Source Analyzer.

PowerCenter Source Analyzer 0

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

Import Excel File into PowerCenter SOurce Analyzer 0

Once you choose the Import from Database.., the Import tables window will open. We don’t have any ODBC connection to select from the drop-down list. To import Excel source in Informatica, please add a new one, and click on the …button.

Choose ODBC Data Source Connection 2

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

Add ODBC Connection 2

Once you click the Add button, Create a New Data Source window will open. Here we can select the required driver from the available list. As we said before, we need to import Excel files into Informatica source. So, we are choosing the Driver to Microsoft Excel for this example.

Choose Driver do Microsoft Excel 3

You can also select the Microsoft Excel Driver.

Select Microsoft Excel Driver xls 0

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

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

Next, Click the Select Workbook.. button to select the required Excel file.

Select the Workbook 4

Once you click the Select workbook.. button, the following window opens to choose the necessary workbooks from our local file system. Once you are done selecting, Click the OK button to close the window

Excel Source in Informatica 5

Please select the ODBC connection from the drop-down list we created now.

Select the ODBC Data Source from drop down 6

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

TIP: If you find it 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

Excel Source in Informatica 7

From the below image, under the Source, see our newly created Excel source in Informatica, and the table definition (Column Names and data types) inside our workspace.

Excel Source in Informatica 8

Preview Data inside the Source

Although we created our Excel source in Informatica, before we proceed further, we should preview our data. To preview the source data, Right-click on the table definition and select the Preview data.. option from the context menu.

Right-click and choose Preview data option 9

Once you select the Preview data.. option, the Preview data window will open. 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 connect, data inside the table (here Multi_cast table) will display

Preview data window opens to view 10

Edit existing Excel Source in Informatica

Although we selected the Multi_Cast.xls file as the 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 situations, we can edit the table by right-clicking on the table definition and selecting the Edit.. option from the context menu.

Choose Edit Option 11

Selecting the Edit.. option opens a new window called Edit tables. To alter or change the table, we must navigate to Columns Tab. For example,

  • If you want to add new columns, then Please select the first button (before the scissors symbol) and add Column Name, Data Type, Precision (if required), and Scale (if needed). If it is the primary key, change the Key type to primary and checkmark the Not Null option (if your column doesn’t allow null values).
  • If you want to delete columns, select the unwanted ones and click the scissors button. Once you complete editing, Click OK to close the Edit Tables window.
Excel Source in Informatica 12

Comments are closed.