Informatica:Use a Microsoft Excel worksheet as a source

Step 1. Create and configure the Excel worksheet.

In Microsoft Excel do the following:

1. Select the required rows to be read into PowerCenter.
2. Select Insert > Name > Define (Formulas > Name Manager in Excel 2007).
3. Enter a name for the range.
4. Click OK.
5. Save the worksheet.

Step 2. Create the ODBC DSN

On the Windows machine (hosting the PowerCenter client and server):

1. Go to Start > Settings > Administrative Tools > Data Sources (ODBC)
2. Click System DSN
3. Click Add…
4. Select Microsoft Excel Driver (*.xls) from the list.
5. Click Finish.
6. Configure the DSN.

Step 3. Create the source definition for the mapping

In PowerCenter Designer do the following:

1. In the Source Analyzer select Sources > Import From Database.
2. Select the ODBC data source name created in Step 3. above.
3. Leave Username, Password and Owner name blank.
4. Click Connect
5. Expand the worksheet name and select the range created in Step 1 above.

The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

Step 4. To format columns in Microsoft Excel:

In Microsoft Excel, you can assign data types to columns of data. The Microsoft Excel data types are ODBC data types. PowerCenter supports ODBC data types and converts them to transformation data types. If you do not assign data types in Microsoft Excel, the Designer imports each column as VARCHAR. If you want to perform numeric or aggregate calculations in a mapping, assign numeric data types in Microsoft Excel before importing the spreadsheet.

1. Open the Microsoft Excel file.
2. Select the columns of data that consist of numeric data.
3. Click Format > Cells.
4. In the Number tab, select Number.
5. Specify the number of decimal places.
6. Click OK.
7. Click File > Save.

Step 5. Create the relational connection for the session

In PowerCenter Workflow Manager do the following:

1. Go to Connections > Relational
2. Click New… >
3. Select ODBC from the list.
4. Click OK.
5. 5. Configure the connection:

Enter a name for the connection.

Username=pmnulluser

Password=pmnullpasswd

Connect string=<ODBC name as defined in step 2>

1. Use this connection for the source in the session.

Errors Analysis:

1. RR_4036:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Database driver error…

Solution: Check the Informatica server machine: make sure in System DSN tab of ODBC, the DSN name match with the respective connection string of workflow ODBC connection configuration.

1. RR_4036 Error connecting to database [Microsoft][ODBC Microsoft Access Driver]'(unknown)’ is not a valid path.

Solution: File location

• Local file
  the path to the local file is correct.
• Remote file
  if the Access file is remote to the machine that the PowerCenter Server is running do NOT browse to the location of the source file. Enter the UNC name of source file (i.e.: \\[ hostname ]\[ directory ]\[ filename ]) in box of database name when create DSN.