I'm doing a proof of concept on reading and writing to excel spreadsheets.
I've created a spreadsheet with half a dozen rows and columns.
I've created an ODBC connection and can read and write to the spreadsheet.
BUT !
I put an @SUM calculated field on the end of the rows that totals them up.
If I update the spreadsheet from datastage by sending some random numbers to it, the job runs ok and the date of the file changes on windows explorer.
When I read the spreadsheet again the field values have changed but the calculated field doesn't change. It still holds the old value.
When I open the Excel spreadsheet and look at it, the calculation obviously refreshes itself and updates. As i can see it's changed and without doing anything when I close the spreadsheet it asks me if I want to save the changes and then when I do a read through datastage the calculated field has changed.
To cut a long story short is there a way I can get the calculated field to refresh itself without having to open the spreadsheet.
The field isn't updateable through datastage. (That's the error message I get if I try anyway).
Incidently if your wondering why I'm doing this we have a number of users who have got some mainframe systems sending data direct to excel spreadsheets and they do some manual summaries based on the figures they receive. They want us to read these spreadsheets and make the data available via Oracle with business objects.
Writing / Reading Excel Spreadsheets
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Do not know what can be an ideal solution but here is one thing that comes to my mind - After you job is finished can you edit the document (just open) (try this through OS commands) through Datastage and then Save it w/o changing and see if the calc field changes due to this.
Thanks
Deepak Patil
Convince Them Confuse Them .. What's the difference?
Deepak Patil
Convince Them Confuse Them .. What's the difference?
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Do not know what can be an ideal solution but here is one thing that comes to my mind - After you job is finished can you edit the document (just open) (try this through OS commands) through Datastage and then Save it w/o changing and see if the calc field changes due to this.
Thanks
Deepak Patil
Convince Them Confuse Them .. What's the difference?
Deepak Patil
Convince Them Confuse Them .. What's the difference?
Excel is a very good tool for calculation and analysis. I like it very much.
But using it as input for DataBase can be crusial.
It doesn't mind if you use SAS, DataStage or any other Tool on the market.
All can read and write to Excel.
You need colleguaes with a 100% dicipline in Excel. If they are only 99.9999999% you will get a big problem.
Take the data direct from mainframe to Oracle via DataStage.
Never use, if you have any influence, Excel as source!
You can never predict to problem you will run in.
But using it as input for DataBase can be crusial.
It doesn't mind if you use SAS, DataStage or any other Tool on the market.
All can read and write to Excel.
You need colleguaes with a 100% dicipline in Excel. If they are only 99.9999999% you will get a big problem.
Take the data direct from mainframe to Oracle via DataStage.
Never use, if you have any influence, Excel as source!
You can never predict to problem you will run in.
Wolfgang Hürter
Amsterdam
Amsterdam