Does datastage have the capabilty to write to a EXEL Spreadsheet
The task involves reading a flat file transforming the data and filling in rows and columns in a predefined spreadsheet which includes complex formulas.
Any ideas
Does datastage have the capabilty to write to a EXEL Spreads
Moderators: chulett, rschirm, roy
-
I_Server_Whale
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
As far as my understanding goes, I'm pretty much sure that this is certainly possible in DS.
However, there is no native stage for EXCEL. So, you'll be better off using an ODBC stage.
Thanks,
Whale.
However, there is no native stage for EXCEL. So, you'll be better off using an ODBC stage.
Thanks,
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Re: Does datastage have the capabilty to write to a EXEL Spr
DS can write to a csv file.You can link your Excel spreadsheet to the csv file for the values,I think.
-
parag.s.27
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Yes you can write in an excel using datastage.
Just use an ODBC stage. Create DNS driver for excel in ODBC in Administrative tools.
After creating the DNS. When you will open the ODBC stage in DS, you can select that DNS and you can link the excel.
Just use an ODBC stage. Create DNS driver for excel in ODBC in Administrative tools.
After creating the DNS. When you will open the ODBC stage in DS, you can select that DNS and you can link the excel.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Keep in mind the fact that this answer is pretty much exclusive to DataStage on Windows. And does work for sourcing from Excel spreadsheets, which has been discussed here a number of times. Not sure, however, you can simply open one up via ODBC and write to it.parag.s.27 wrote:Yes you can write in an excel using datastage.
Just use an ODBC stage. Create DNS driver for excel in ODBC in Administrative tools.
After creating the DNS. When you will open the ODBC stage in DS, you can select that DNS and you can link the excel.
Has anyone actually done this successfully? Is there a specific ODBC driver for Excel?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is a specific ODBC driver for Excel, from Microsoft curiously enough.
You can write to an Excel worksheet, but it must already exist and have the column headings in place. Excel treats a worksheet as a system table for some reason only known to the authors of the ODBC driver, and there are restrictive rules (such as it must be in columnar format with the first row ($1:$1) being column headings).
You can write to an Excel worksheet, but it must already exist and have the column headings in place. Excel treats a worksheet as a system table for some reason only known to the authors of the ODBC driver, and there are restrictive rules (such as it must be in columnar format with the first row ($1:$1) being column headings).
Last edited by ray.wurlod on Fri Nov 10, 2006 7:59 am, edited 1 time in total.
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 have read/written from/to Excel in the past.
I had to replace a buggy .dll which was used to convert an excel sheet to a csv file.
It was very painful to figure it out. I come across lot of hurdles doing it.
You will find some information about reading from Excel here
viewtopic.php?t=93701&highlight=excel
To write to excel, you need to take a similar approach, except that now the target stage is the ODBC Stage
If you are ok with writing to a csv file, go that route. It is much easier.
I had to replace a buggy .dll which was used to convert an excel sheet to a csv file.
It was very painful to figure it out. I come across lot of hurdles doing it.
You will find some information about reading from Excel here
viewtopic.php?t=93701&highlight=excel
To write to excel, you need to take a similar approach, except that now the target stage is the ODBC Stage
If you are ok with writing to a csv file, go that route. It is much easier.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
parag.s.27
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
chulett wrote:Keep in mind the fact that this answer is pretty much exclusive to DataStage on Windows. And does work for sourcing from Excel spreadsheets, which has been discussed here a number of times. Not sure, however, you can simply open one up via ODBC and write to it.parag.s.27 wrote:Yes you can write in an excel using datastage.
Just use an ODBC stage. Create DNS driver for excel in ODBC in Administrative tools.
After creating the DNS. When you will open the ODBC stage in DS, you can select that DNS and you can link the excel.
Has anyone actually done this successfully? Is there a specific ODBC driver for Excel?
Yes we are using an Excel as our target file in our project for a client. And till now it had no problems. I generally faced the problems if the DNS is not linked to a particular sheet in the Excel.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0