Error reading Excel worksheet
Posted: Fri Aug 13, 2010 5:42 am
Hi,
I have an excel workbook with many worksheets in it. The e.g. names of the worksheets are Share, Company etc
I have to read from the Share worksheet and load into the DB2 target. The steps I did is as follows.
1) Created a system DSN named as RprtSysDsn to the source excel sheet
3) Using datastage manager successfully imported the table defnitions to Datastage.
4.) Used an odbc stage to and passed "DatasoureName" as RprtSysDsn. Userid & pwd left blank
5). In the dropdownbox of available tables to add, I could see all my worksheets there e.g. Share$
When I selected view data I am getting the following error.
DSBrowser..ODBC_0.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT SHARE_NAME, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE, NUMBER_OF_TRADES, TRADED_QTY, TRADED_VALUE FROM Share$
SQLSTATE=37000, DBMS.CODE=-3506
[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] Syntax error in FROM clause.
Please tell me whats the problem here. I feel the problem is the $ at the end of the worksheet name but thats the format datastage is showing me when I clicked the drop down box of available tables to add.
Please suggest how to resolve the problem here. What am I doing wrong here.
I have an excel workbook with many worksheets in it. The e.g. names of the worksheets are Share, Company etc
I have to read from the Share worksheet and load into the DB2 target. The steps I did is as follows.
1) Created a system DSN named as RprtSysDsn to the source excel sheet
3) Using datastage manager successfully imported the table defnitions to Datastage.
4.) Used an odbc stage to and passed "DatasoureName" as RprtSysDsn. Userid & pwd left blank
5). In the dropdownbox of available tables to add, I could see all my worksheets there e.g. Share$
When I selected view data I am getting the following error.
DSBrowser..ODBC_0.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT SHARE_NAME, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE, NUMBER_OF_TRADES, TRADED_QTY, TRADED_VALUE FROM Share$
SQLSTATE=37000, DBMS.CODE=-3506
[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] Syntax error in FROM clause.
Please tell me whats the problem here. I feel the problem is the $ at the end of the worksheet name but thats the format datastage is showing me when I clicked the drop down box of available tables to add.
Please suggest how to resolve the problem here. What am I doing wrong here.