Ouput to Excel on Multiple Sheets

parag.s.27 · Post by **parag.s.27** » Fri Apr 13, 2007 12:06 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 13, 2007 2:07 am

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.

kduke · Post by **kduke** » Fri Apr 13, 2007 6:41 am

ray.wurlod wrote:you're screwed

I would not want to go against that kind of statement from Ray.

trobinson · Post by **trobinson** » Fri Apr 13, 2007 7:27 am

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.

chulett · Post by **chulett** » Fri Apr 13, 2007 7:38 am

trobinson wrote:Just an idle thought unhampered by any actual knowledge or experience in the matter.

Need more of that.

parag.s.27 · Post by **parag.s.27** » Sun Apr 15, 2007 10:03 pm

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 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..

chulett · Post by **chulett** » Sun Apr 15, 2007 10:11 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sun Apr 15, 2007 11:43 pm

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.

khaja.arshad · Post by **khaja.arshad** » Mon Apr 16, 2007 5:57 am

This query is really a good one
Did any one got some work around ..if so plz let us know

Thanks
Hameed

ray.wurlod · Post by **ray.wurlod** » Mon Apr 16, 2007 2:37 pm

You don't need a workaround - we have given the solution

parag.s.27 · Post by **parag.s.27** » Tue Apr 17, 2007 12:26 am

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 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 ?
I really appreciate the time and thought given to this query by all the members.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 17, 2007 1:42 am

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.

chulett · Post by **chulett** » Tue Apr 17, 2007 6:54 am

parag.s.27 wrote: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.

Of course. Get the DSN created on the Server machine and give it another shot.

parag.s.27 · Post by **parag.s.27** » Sun Apr 22, 2007 10:46 pm

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.

chulett · Post by **chulett** » Sun Apr 22, 2007 11:00 pm

I'm not suggest you have to do it. 'Get' as in 'make arrangements with whomever manages the server' to have the DSN created.