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
-
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– swasheck2012年05月30日 18:01:59 +00:00Commented May 30, 2012 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.JustBeingHelpful– JustBeingHelpful2012年05月30日 18:23:57 +00:00Commented May 30, 2012 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– ConcernedOfTunbridgeWells2012年05月30日 18:48:52 +00:00Commented May 30, 2012 at 18:48
1 Answer 1
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.