Skip to main content

Data & Intelligence

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.


Data Intelligence - The Future of Big Data
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.



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.

Thoughts on “Informatica:Use a Microsoft Excel worksheet as a source”

  1. Pingback: Informática Para Seres Humanos.

  2. Hi,

    is it possible to extract the data from UNIX box.. Source file is excel..

    i hope UNIX will not support excel….


  3. 1.There is a single folder.15 excels will be put here.
    2.Source is excel sheet (15 excels).
    3.All excel sheets will have same structure and will be put in the same folder.
    4.We have defined table in a single excel sheet and we are able
    to import the source using DSN.
    5.However we want that it should pick other remaining 14 excels automatically so
    that we dont have to import each excel in the mapping.

    Please suggest us how it can import from all these 15 excels in one shot as we do in indirect file loading for csv files.


  4. srinibas: we used this method to process one excel source file. If you have several excel files, maybe it is easier to convert them to csv files and then use indirect file loading. Others seems need to create another ODBC for other excel file.

  5. Very helpful…
    But what happened when we want to create an ODBC connection that points to an Access DB.
    I have as source table a table from access schema after creating a user/system DNS that points to the path that mdb file is. So, for creating a relational connection I enter the below credentials:
    Connection String: testAccess (the name of User/System DNS).

    Thank you in advance.

  6. Hi ,
    Could you please explain in detail , how to import excel if it is in network path . There is an option called Network in when we give Select workbook and i gave my network path and imported file also . But while running it throwing error “check your network connection or contact the server administrator” .Could you please help to find a solution

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.