Page 1 of 1
Does datastage have the capabilty to write to a EXEL Spreads
Posted: Wed Nov 08, 2006 12:50 pm
by tiozzo
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
Posted: Wed Nov 08, 2006 12:52 pm
by I_Server_Whale
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.
Re: Does datastage have the capabilty to write to a EXEL Spr
Posted: Thu Nov 09, 2006 10:09 pm
by Chuah
DS can write to a csv file.You can link your Excel spreadsheet to the csv file for the values,I think.
Posted: Thu Nov 09, 2006 10:26 pm
by parag.s.27
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.
Posted: Thu Nov 09, 2006 11:39 pm
by chulett
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.
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.
Has anyone actually done this successfully? Is there a specific ODBC driver for Excel?
Posted: Fri Nov 10, 2006 12:35 am
by ray.wurlod
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).
Posted: Fri Nov 10, 2006 1:13 am
by narasimha
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.
Posted: Fri Nov 10, 2006 2:59 am
by parag.s.27
chulett wrote: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.
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.
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.