Working with excel
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 13
- Joined: Thu Jul 17, 2008 4:11 am
Working with excel
can somebody tell me the steps to load xl file and to read the data from xl file?
Do we need any plug-in load the data into xl?
Do we need any plug-in load the data into xl?
Chandra Sekhar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Presumably you mean Microsoft Excel - why not say so?
You need an ODBC driver for Microsoft Excel. None is supplied with DataStage, therefore you will need to source this yourself.
Otherwise contemplate working with CSV files.
You need an ODBC driver for Microsoft Excel. None is supplied with DataStage, therefore you will need to source this yourself.
Otherwise contemplate working with CSV files.
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: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
You are on Unix and access Excel is very difficult. Unix just doesn't know what an Excel file is and any ODBC driver would lack the Windows libraries needed to read it. You need some type of ODBC bridge from Unix to a Windows server - or just exchange data via CSV of XML files. If you are on DataStage on Windows it becomes easier - you just read Excel as a worksheets as a data table via an ODBC connection.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 11
- Joined: Mon Mar 17, 2008 3:14 pm
Re: Working with excel
First your question is not clear to me.
First you need to change the Excel file as .CSV file.
IF you are on Unix server you need to FTP the file(.CSV file) from windows to Unix.
and after that you can use directly sequential file stage and provide delimiter as , and quote as none.
try it out.
First you need to change the Excel file as .CSV file.
IF you are on Unix server you need to FTP the file(.CSV file) from windows to Unix.
and after that you can use directly sequential file stage and provide delimiter as , and quote as none.
try it out.
chandra_babu_999 wrote:can somebody tell me the steps to load xl file and to read the data from xl file?
Do we need any plug-in load the data into xl?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Working with excel
This is not true if you have an ODBC driver for Excel.ppavani_km wrote:First you need to change the Excel file as .CSV 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: 5
- Joined: Wed Sep 17, 2008 12:24 am
- Location: Think BIG ,Start small and Skills fast
Re: Working with excel
try this one..
you need a sequential file(import the metadata) then link it to a copy file and load it to your target file.
you need a sequential file(import the metadata) then link it to a copy file and load it to your target file.
Think BIG,start small & skills FAST...
Hi,
To read a excel file from DataStage you can use the jxl.jar (you need to download) and the class used is ExcelRead in it
in Stage -> Properties:
1. Java additional classpath - /<path>/jxl.jar
2. Transformer class name - ExcelRead
3. User's custom properties is to be as,
File=<path>/<filename>
Sheet=1
TopLeft=A1 <cell where data starts>
BottomRight=B <cell where data ends, there is only cell name>
Try this out, this definitely works in 7.5.
Thanks
To read a excel file from DataStage you can use the jxl.jar (you need to download) and the class used is ExcelRead in it
in Stage -> Properties:
1. Java additional classpath - /<path>/jxl.jar
2. Transformer class name - ExcelRead
3. User's custom properties is to be as,
File=<path>/<filename>
Sheet=1
TopLeft=A1 <cell where data starts>
BottomRight=B <cell where data ends, there is only cell name>
Try this out, this definitely works in 7.5.
Thanks
Hi,
To read a excel file from DataStage you can use the jxl.jar (you need to download) and the class used is ExcelRead in it
in Stage -> Properties:
1. Java additional classpath - /<path>/jxl.jar
2. Transformer class name - ExcelRead
3. User's custom properties is to be as,
File=<path>/<filename>
Sheet=1
TopLeft=A1 <cell where data starts>
BottomRight=B <cell where data ends, there is only cell name>
Try this out, this definitely works in 7.5.
Thanks
To read a excel file from DataStage you can use the jxl.jar (you need to download) and the class used is ExcelRead in it
in Stage -> Properties:
1. Java additional classpath - /<path>/jxl.jar
2. Transformer class name - ExcelRead
3. User's custom properties is to be as,
File=<path>/<filename>
Sheet=1
TopLeft=A1 <cell where data starts>
BottomRight=B <cell where data ends, there is only cell name>
Try this out, this definitely works in 7.5.
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That is not the case if you use ODBC. You create a DSN to point to the workbook; each worksheet is a "table" within that DSN.xch2005 wrote:In addition to previous post, if I remember correclty the input excel needs to have only 1 sheet.
Thanks
Last edited by ray.wurlod on Mon Sep 22, 2008 4:13 pm, 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.