Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Reference:

https://community.informatica.com/message/62128

Windows ODBC Workflow:

Windows > Control Panel > Administrative Tools > Data Sources (ODBC) > click "System DSN" menu > click "Add" > choose "Microsoft Excel Driver (*.xlsx) >

Data Source Name: "aaaaaaaaa"

Version: Excel 12.0

click "Select Workbook" button > choose xlsx file

Informatica PowerCenter Designer Workflow:

Sources > Import from Database >

ODBC Data Source: "aaaaaaaaa"

click "Connect" button > the button's name changes to "Re-Connect"

Error:

.. it doesn't seem to find the data in the worksheet ... I have 3 columns with 10 rows of data

"Nothing has been selected to import" error message

share|improve this question
    
Is that really the DSN? The reason I ask is that I wonder if it as simple as one too many a's (though I'm sure you would have already checked that). –  swasheck May 30 '12 at 18:01
    
Yah, I'm just emulating a database by using Excel, and making it my data source name. I tried reducing it to 1 "a", and same message. I tried both xls format and xlsx (open XML) format. When Informatica PowerCenter Designer connects to the ODBC source (the file), it locks the file. If I try opening it in Windows, it says it's locked for editing. But it can't find the tables and columns in "Sheet1". Seems to be a compatibility issue with Informatica and this ODBC file type. –  MacGyver May 30 '12 at 18:23
    
Try opening the ODBC connection programatically through something else (e.g. Activestate python and the odbc module, or VBA and DAO). –  ConcernedOfTunbridgeWells May 30 '12 at 18:48
add comment

1 Answer

up vote 1 down vote accepted

To use an Excel file as a source you need to define a named range containing the cells with data (that's Step 1 of the instruction you referenced in the question).

All the named ranges found in the file will be shown in the Select tables pane. After you select a table (i.e. a range) and click OK, a new source will be created.


You can also consider converting the Excel spreadsheet to the CSV format and using simple flat files as a source. If you're in a Windows environment and have have Excel installed, there's a VBS script that can help you automate this process.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.