Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.
Moderators: chulett , rschirm , roy
edward_m
Charter Member
Posts: 257 Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA
Post
by edward_m » Mon Apr 28, 2008 9:29 am
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
Participant
Posts: 117 Joined: Sun Mar 21, 2004 4:40 pm
Post
by nivas » Mon Apr 28, 2008 11:23 am
You can use sequential stage and create the file as .csv. This will create a EXCEL spread sheet.
ArndW
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Mon Apr 28, 2008 11:26 am
This should work with ODBC - What does the SQL command look like if you use auto-generated commands?
edward_m
Charter Member
Posts: 257 Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA
Post
by edward_m » Mon Apr 28, 2008 12:18 pm
ArndW,
ArndW wrote: 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
Charter Member
Posts: 257 Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA
Post
by edward_m » Mon Apr 28, 2008 12:19 pm
nivas wrote: 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
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Mon Apr 28, 2008 4:18 pm
Does the Sheet1 in question have column headings COL1 and COL2 ?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
nivas
Participant
Posts: 117 Joined: Sun Mar 21, 2004 4:40 pm
Post
by nivas » Tue Apr 29, 2008 6:43 am
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
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Tue Apr 29, 2008 7:00 am
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
Charter Member
Posts: 257 Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA
Post
by edward_m » Tue Apr 29, 2008 7:39 am
ray.wurlod wrote: Does the Sheet1 in question have column headings COL1 and COL2 ? ...
Yes, sheet1 has column headings COL1,COL2
ArndW
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Tue Apr 29, 2008 8:04 am
Can you read from it using COL1 and COL2 in a test job?
edward_m
Charter Member
Posts: 257 Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA
Post
by edward_m » Tue Apr 29, 2008 9:42 am
ArndW wrote: 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
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Tue Apr 29, 2008 10:10 am
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
Charter Member
Posts: 257 Joined: Fri Jun 24, 2005 9:34 am
Location: Philadelphia,PA
Post
by edward_m » Tue Apr 29, 2008 10:48 am
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
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Tue Apr 29, 2008 10:55 am
Does "UPDATE" work?