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
