Hi to all, my requirement is this:
I have an excel file (.xls) with multiple sheets (three sheets for precision: sheet1, sheet2 and sheet3) and I must read data from sheet1, sheet2 and sheet3 and write them in a new sheet, say, sheet4 of same excel file.
How to realize this in datastage 8.1 server edition?
Is it possible?
thanks in advance.
Read from write into excel file.
Moderators: chulett, rschirm, roy
Well... I have no actual experience with this as I've never worked on a Windows edition of DataStage but you should have Excel ODBC drivers you can leverage for this. At least for the reading part. No clue how well they write back to Excel or if writing back to the same file you are reading will be an issue.
Basically, the workbook / file itself is the database and each worksheet is considered a table. You'll need to create a DSN to access it and make sure it is not marked 'Read Only' which seems to be the default. If you do an exact search here for 'Excel' you'll find examples of conversations on this topic, mostly about how to read them directly, very little on writing to them as anything other than a csv.
You sure this isn't a more appropriate task for an Excel macro?
Basically, the workbook / file itself is the database and each worksheet is considered a table. You'll need to create a DSN to access it and make sure it is not marked 'Read Only' which seems to be the default. If you do an exact search here for 'Excel' you'll find examples of conversations on this topic, mostly about how to read them directly, very little on writing to them as anything other than a csv.
You sure this isn't a more appropriate task for an Excel macro?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Does the write sheet exist and have column headings in place? Then you can import its "table definition" just like any other sheet. And write to it, provided that the executing process has write permission to the workbook. Beware that each worksheet is a system table to the ODBC driver for Excel.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Don't forget that system table names must end with "$". So, if your worksheet is to be called MySheet, then the table name used in clients (whether DataStage, MS Access or whatever) needs to be MySheet$.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.