Informatica Tutorial | ETL Tool

Page 1 2 3 4 5 6 7 8 9 10

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.

Step III. Preparing Microsoft Excel data for import

a. To define the range:

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

b. Formatting Columns of Numeric Data:

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

Step IV. Importing Excel source definition:

1. In the Informatica Designer connect to the respective repository and open folder for the source definition.
2. Open the source analyzer and select Source from Import from Database.
3. Import table opens up and select the Microsoft data source which is the Microsoft excel worksheet name in the ODBC Data source name.
4. Click Browse and open up the ODBC administrator.
5. In the user or system DSN tabs, double click the Microsoft excel driver.
6. Click select workbook and browse for the Microsoft excel file, which is considered as the relational database.
7. Click OK and connect.
8. 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.
9. There is no Owner name as we are not using the database.
10. Select the table we wanted to import and click Ok.
11. The newly created source definition would be there in the navigator under the data base name. Choose Repository Save.

Page 1 2 3 4 5 6 7 8 9 10