Home » ETL - Informatica » Informatica Tutorial – Part 9

To import an excel source definition, follow these steps:

  • Step I: Microsoft Excel ODBC driver should be installed in the system.
  • Step II: Create a Microsoft Excel ODBC data source for each source in the 32 Bit ODBC Administrator.
  • Step III: Prepare MS excel spreadsheet by defining the ranges and formatting columns of the numeric fields.
  • Step IV: Import the source definition in the Designer.

Preparing Microsoft Excel data for import:

  • Define Ranges
  • Format column for numeric data

a. To define the range:

  • Open the Microsoft Excel and highlight the columns and rows of data to be imported.
  • Choose Insert → Name → Define in the excel file.
  • Enter a name for the selected range and click OK.
  • Choose File → Save.

b. Formatting Columns of Numeric Data:

  • Open the Microsoft Excel and select the columns that consist of numeric data.
  • Choose Format → Cells.
  • In the Number tab, select Number.
  • Specify the decimal place and click OK.
  • Click File → save.

Importing Excel source definition:

  • In the Informatica Designer connect to the respective repository and open folder for the source definition.
  • Open the source analyzer and select Source from Import from Database.
  • Import table opens up and select the Microsoft data source which is the Microsoft excel worksheet name in the ODBC Data source name.
  • Click Browse and open up the ODBC administrator.
  • In the user or system DSN tabs, double click the Microsoft excel driver.
  • Click select workbook and browse for the Microsoft excel file, which is considered as the relational database.
  • Click OK and connect.
  • It is not required to enter the data base user name or the password. The ranges defined in the Microsoft excel appears as the username.
  • There is no Owner name as we are not using the database.
  • Select the table we wanted to import and click Ok.
  • The newly created source definition would be there in the navigator under the data base name. Choose Repository Save.

Note: Informatica and its related products are Trademarks of Informatica Corporation. For more details, please visit their official website www.Informatica.com

Leave a Reply

Your email address will not be published. Required fields are marked *