Error reading Excel worksheet

myukassign · Post by **myukassign** » 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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Aug 13, 2010 6:20 am

Been quite a long time since I used Excel ODBC. So guessing....

Did you try putting the name in double quotes - like "Shares$" ?

chulett · Post by **chulett** » Fri Aug 13, 2010 6:22 am

So is this actually a Server job? Yours are usually PX ones and we are in the PX forum after all, hence the question.

myukassign · Post by **myukassign** » Fri Aug 13, 2010 6:23 am

I used that.. not working still

ray.wurlod · Post by **ray.wurlod** » Fri Aug 13, 2010 6:23 am

The ODBC stage has a property for quote character on the Stage properties. Click "Get SQL Info" to have this populated with the correct value from the database. You could also apply double quotes around the table name in the Table Name property.

myukassign · Post by **myukassign** » Fri Aug 13, 2010 9:03 pm

Ray...Thanks a lot for the suggestions but you know the mistake was very funny. Let me elaborate.

A. The driver I chose was "Microsoft Excel Driver(.xls), I changed that to another driver called "driver do Microsoft Excel (.xls) to create system dsn. Ya I knw this is not funny but see the next isse.

B. Initially I gave the worksheets name like 'A', 'B', 'C', 'D'. Though I mentioned in my original query that the worksheet name is 'share' company et but that wasn't the case. Now I changed the worksheet names to a long name like Company-A, Company-B That surprisingly solved the issue. Some how a single digit table name(worksheet name) the SQL is not able to take.

The issue is solved.

Thanks for the support.

Additional information for anyone looking for a similar problem in future.

1. Import all the table defnitions using Manager. When you import make sure that you select the option "Include system tables"

2. Make sure that you do a view data from manager itself to see all your dsn and configurations are right.

3. In the designer in the odbc stage give this DSN name as source DSN / server. Make sure you click SQL info

4. You load the colmn defnition from the table defnion you loaded in the first step

5. You can either go for generated query option / userdefined query. I did a userdefined query and it will look like this.

SELECT * FROM `MyShareABC$`;

6. Do a view data / run. You should find everything working fine.

I did it in a server job though I have a px server here