Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(9 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Create an Excel data source and import the file into PowerCenter Designer
Solution

The Excel file is treated as a table when it is being read through the ODBC Data Source Name (DSN). The ODBC DSN does not recognize the data in the Excel file by default. A name has to be defined using the Define Name functionality in the Excel file so that the name would be treated as a table name using the ODBC drivers.

To import an Excel file in the PowerCenter Designer, do the following:

  1. Open the Excel file and select the contents in the Excel file that needs be read or imported.

 

  1. Right-click the selected cells and click the Define Name menu that appears. A new dialog box would appear. 

 

  1. In the new Dialog box (New Name) that appears, specify a name (table name) and then click the Ok button and save the changes. This name will be the table name while you import the file later.

 

 

  1. Now create a 32-bit ODBC DSN (Microsoft Excel Driver) pointing to the file that was saved in step 3. Consider that the DSN name created is TEST_EXCEL_DSN.
  2. Open the PowerCenter Designer and connect to the repository. Open a folder and click on the source analyzer section. Click Source > Import from Database option in the menu bar. In the dialog box that appears, select the ODBC Data Source that was created in step 4 and click the connect button.

 

 

  1. Select the table that needs to be imported and then click the OK button. You can see the table being imported in the Designer.

 

 

More Information
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Architect; Developer
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:8/27/2012 10:57 PMID:140004
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)