Writing / Reading Excel Spreadsheets
Posted: Wed Oct 15, 2008 6:14 am
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.
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.