writing into excel

sonia jacob · Post by **sonia jacob** » Thu Aug 12, 2004 2:41 pm

I am using ODBC stage and trying to write to an excel file. The DSN is mapped to excel and the worksheet chosen as the table. But when trying to write to the excel

Event #:504
Timestamp:8/12/2004 3:58:15 PM
Event type:Warning
Message:
Accountxls..ODBC_4.Lnk_Account: DSD.BCIOpenR call to SQLExecDirect failed.

Statement was:SELECT HEADER, VERSION, ACCT_NUMBER accountloader

SQLSTATE=S1004, DBMS.CODE=0
[DataStage][SQL Client]An unsupported SQL data type was encountered

all the three columns are varchar.

what i do not understand is
1. why is the DS director giving me a select statement when the SQL stmt. is INSERT INTO accountloader(HEADER, VERSION, ACCT_NUMBER) VALUES (?,?,?)

2. "An unsupported SQL data type was encountered". Is this because the datatype i mentioned was Varchar or is it because the data was not compatible with the type.

thanks
Sonia

sonia jacob · Post by **sonia jacob** » Thu Aug 12, 2004 3:18 pm

If excel is not installed in my DS server, would it pose a problem in writing to an excel?

ray.wurlod · Post by **ray.wurlod** » Thu Aug 12, 2004 10:03 pm

Excel is probably reporting the column data type as NVarChar. Can't remember the workaround for this; do a search for NVarChar on the forum.

sonia jacob · Post by **sonia jacob** » Fri Aug 13, 2004 8:11 am

Finally I was able to write to excel. :D

But to drop table/worksheet before load or clear the table/worksheet before load does not seems to be working. It throws errors like

DSD.BCIOpenW call to OCONV failed. Statement: DROP TABLE accountloader

Deleting data in a linked table is not supported by this ISAM

I am creating the excel worksheet for each of my load, and it does not give any "object already existing" errors. So I guess Iam all set.

THANKS A LOT

neena · Post by **neena** » Fri Aug 13, 2004 8:21 am

Hi Sonia,
You can also use a Exec sh or Exec dos commands and give the pamareter as "rm file.xls" this will run before the your job runs and deletes that file using OS.
HTH
Thanks
Neena

sonia jacob · Post by **sonia jacob** » Fri Aug 13, 2004 8:37 am

Hi Neena,

My understanding of using Excel as a source and/or target was that the DSN points to the WorkBook (.xls file) as it does to a database and each sheet with the workbook is considered as a table.

In short

Database = Workbook (File.xls)
Table = Worksheet (sheet1)

Now If i remove the .xls file then I would get a error while the DSN tries to connect to the .xls file.

Do correct me if I am wrong

Thanks
Sonia

neena · Post by **neena** » Fri Aug 13, 2004 11:43 am

Sorry sonia,
I thought something else..
The scenario i gave you doesn't work for you..
Hope you got your solution already...
Thanks
Neena