Excel File

[edward_m]

I was trying to insert data into excel file using ODBC stage, somehow i'm getting the following error..
SQLSTATE=S1000, DBMS.CODE=-3035
[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] Operation must use an updateable query.

My SQL is INSERT INTO "Sheet1$"(COL1, COL2) VALUES (?,?);

Does anybody inserted data into excel file through ODBC,please advise.

[nivas]

You can use sequential stage and create the file as .csv. This will create a EXCEL spread sheet.

[ArndW]

This should work with ODBC - What does the SQL command look like if you use auto-generated commands?

[edward_m]

ArndW,

This should work with ODBC - What does the SQL command look like if you use auto-generated commands? ...

I have used update action as insert rows without clearing and generated SQL is
INSERT INTO `Sheet1$`(COL1, COL2) VALUES (?,?);

Thanks

[edward_m]

You can use sequential stage and create the file as .csv. This will create a EXCEL spread sheet.

fyi.. xls format is not same as .csv

[ray.wurlod]

Does the Sheet1 in question have column headings COL1 and COL2 ?

[nivas]

Using Sequential File stage if you create a file with extention .csv and select the check box First Line is column names, delimiter as "," there by it will create a spread sheet with first row as column names and the rest rows with data.

[ArndW]

Nivas - a .csv file is not the same as a .xls file; the OP is using ODBC to populate the Excel sheet directly.

[edward_m]

Does the Sheet1 in question have column headings COL1 and COL2 ? ...

Yes, sheet1 has column headings COL1,COL2

[ArndW]

Can you read from it using COL1 and COL2 in a test job?

[edward_m]

Can you read from it using COL1 and COL2 in a test job? ...

Yes..i am able to read data this and inserted into sequential file without errors

Thanks for your help

[ArndW]

So if you can read from the Excel ODBC then the connection and sheet format is correct. Is your write SQL automatically generated or user-defined?

[edward_m]

I have tried both generated SQL and user defined SQL but i got the same error.
SQLSTATE=S1000, DBMS.CODE=-3035
[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] Operation must use an updateable query.

Thanks..

[ArndW]

Does "UPDATE" work?

[ds_developer]

You might want to look into the following article at Microsoft:

http://support.microsoft.com/kb/175168

John