Writing / Reading Excel Spreadsheets

ianm · Post by **ianm** » 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.

DeepakCorning · Post by **DeepakCorning** » Thu Oct 16, 2008 10:39 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.

DeepakCorning · Post by **DeepakCorning** » Thu Oct 16, 2008 10:40 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.

WoMaWil · Post by **WoMaWil** » Tue Oct 21, 2008 1:34 am

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.