Ouput to Excel on Multiple Sheets
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Ouput to Excel on Multiple Sheets
Hello,
I have a DS job in which i use Sequential file stage to produce Excel as Output.Here i use Tab as the text delimiter.
Now i have a requirement where depending upon a value say Instance name(column in source) in the Source file i have to write the data on new sheets.i.e i have sorted data Instance wise and i want the Ouput of all instance in a single Excel file but 1 sheet should contain the data only about 1 instance only.
Is it possible to insert new sheets in excel from DS Jobs while it is executing?I cannot use ODBC Stage because i dont have the rights to create a DSN.
Any help regarding this would be great.
I have a DS job in which i use Sequential file stage to produce Excel as Output.Here i use Tab as the text delimiter.
Now i have a requirement where depending upon a value say Instance name(column in source) in the Source file i have to write the data on new sheets.i.e i have sorted data Instance wise and i want the Ouput of all instance in a single Excel file but 1 sheet should contain the data only about 1 instance only.
Is it possible to insert new sheets in excel from DS Jobs while it is executing?I cannot use ODBC Stage because i dont have the rights to create a DSN.
Any help regarding this would be great.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Unless you can use ODBC you're screwed. It's the only way you can access worksheets in an Excel workbook. And even then they're system tables. Get someone to create the DSN for you.
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.
I work in UNIX and darn glad of it, so I ain't no Windows Guroo with fancy pants MS certifications but isn't there a way to have a master Excel spreadsheet with pre-defined worksheets that point to other *.csvs?. In other words, the Master .xls has n number of tabs which are merely references to the actual .csvs files? DS could write the .csvs and the Master would be refreshed upon opening. Just an idle thought unhampered by any actual knowledge or experience in the matter.
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Thanks for the help Ray,but the problem is even if i get someone to create a DSN it doesnt Appear in the DropDown of the ODBC Stage,Hence the Problem persists..I was thinking about sending some keystrokes to excel (like shift+F11 or alt+I+W) to insert new sheets when required,but could not achieve it...I was hoping somebody has a workaround to this..ray.wurlod wrote:Unless you can use ODBC you're screwed. It's the only way you can access worksheets in an Excel workbook. And even then they're system tables. Get someone to create the DSN for you. ...
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Craig remembers correctly. Importing a table definition establishes the DSN in a file called uvodbc.config in the project directory on the DataStage server machine. The DSN drop down list is populated from this file.
If you don't want to import the table definitions - in which case you are not following DataStage best practice - then you could manually edit the uvodbc.config file.
If you don't want to import the table definitions - in which case you are not following DataStage best practice - then you could manually edit the uvodbc.config file.
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: 30
- Joined: Mon May 29, 2006 11:19 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
I tried typing in the DSN Name but it doesnt work because i guess the DS Server engine and the DSN needs to be on the same machine.My Current Environment is as follows ,i connect to a remote server on which i can create a DSN and i use DSDesigner on that machine to connect to a DS Server which is located on some other server,hence the DSN is not working i guess.Factoring in this constraint is there any solution to this ?chulett wrote:Just type it in - you are not forced to use the drop-down. From what I recall, it won't show up on that list until you import metadata using it.
I really appreciate the time and thought given to this query by all the members.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
No.
The DataStage job - or metadata importer - is the client, the ODBC driver is the protocol, and the database server is the server.
Therefore the ODBC driver (and driver manager) must be on the same machine as the DataStage server.
The DataStage job - or metadata importer - is the client, the ODBC driver is the protocol, and the database server is the server.
Therefore the ODBC driver (and driver manager) must be on the same machine as the DataStage server.
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: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
chulett wrote:Of course. Get the DSN created on the Server machine and give it another shot.
but craig the issue is that we do not have access at all to the server machine.None of the team members have access to the server.So i guess in that case the requirement will not be achievable with this constraint.
Thanks Everyone for the thought put in to resolve this requirement.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0