Working with formules in Excel

[manuel.gomez]

Hello everybody,

I am working with server job, reading and writing to Excel sheets via ODBC.

But we are having problems with formules. Typical excample would be to give cells in column C the value of column B plus 3:

C
=B1+1
=B2+1
=B3+1

and so on. But when we deploy this column in datastage transformer (giving value '=B':@INROWNUMBER:'+1' )
what we see in resulting Excel is the formule string
If we just edit the cell, and press Enter, formule takes it value and it works. To give an example:

Resulting Excel after transforming:

B C
1 =B1+1
3 =B2+1
4 =B3+1

And if I just enter these three cells and press enter (not modifying anything at all), the result turns into:

B C
1 2
3 4
4 5

Obviously, it is not valid for us to do the sum operation in the datastage transformer. We need the formule to be valid, because data can be updated by user after Datastage has generated the resulting sheet

Any ideas? Thanks very much!!!

[ogmios]

No such luck, with DataStage you can only add values to excel, not formula's.

[WoMaWil]

I don't understand what you want to do finally. But it seems to me, that you have not yet thought about the advantages and sense of each tool. Maybe, that when you tell why you want to do something we might show you the best way to do it.

[manuel.gomez]

No such luck, with DataStage you can only add values to excel, not formula's.

Thanks ogmios, I was afraid to get this answer

I don't understand what you want to do finally. But it seems to me, that you have not yet thought about the advantages and sense of each tool. Maybe, that when you tell why you want to do something we might show you the best way to do it.

I thought very clearly about advantages and sense of each tool.
The reason I am using this is quite simple: I will have to work with Excel sheets within a customer project shortly, and they gave these specifications. Of course, he doesnt allow to be said: "no, dont use your excels any longer"
With datastage, we need to read data from some source excels, and produce destination ones, that will be used for final users and not feed some system else

I thought, despite not my perfect English, I was very clear on my question.
Basically, and not to repeat myself, is that I want to generate an Excel sheet with formulas from Datastage, but the only thing I get at the moment is just a string 'looking like' a formula.

[WoMaWil]

Querido Manuel!

It is not you English what is not understandable. If you work in a BI and DWH environment you should at least work with a 3 or more Tier architecture.

The first TIER for the saving of Data without any formula, only flat, flat data.

The second TIER for selection and calculation (!!! here and only here FORMULA!!!) of data.

The third TIER for the presentation of the selected and calculated Data (colorfull for the End user)

You might hide the first and second TIER to the end user and access it via Buttons or else in the Third TIER.

Fill the First TIER with DataStage-Means.

Programm the second and third TIER with VBA and you will have happy End Users and happy ETL operator.

If you want to work with only one TIER you will make things complicated without any need.

[ray.wurlod]

You could, of course, use DataStage to populate one data-only worksheet, and have formulae referring to these data cells on other worksheets in the same workbook.

[ray.wurlod]

You could, of course, use DataStage to populate one data-only worksheet, and have formulae referring to these data cells on other worksheets in the same workbook.

[manuel.gomez]

Just to make things much easier, I uploaded a capture of the result I am getting after datastage process execution:

http://www.cuelgalo.com/viewer.php?id=1 ... rmulas.JPG

As you can see, third column are formulas that would show same value that column B. But what they are showing, is formula string itself.
For first data row, I just edited the cell value, pressed enter (changing nothing at all) and got correct result.

So, is there any way to avoid delivering this to the final user, but formulas properly working, WITH NO HUMAN PREVIOUS INTERNVENTION?

Thanks very much all of you for your answers!! :D

[gateleys]

I feel stupid suggesting this since I really don't understand or recommend the concept of 'spreadmarts' that is said to be plaguing datawarehouse. Anyways, what if you followed your derivation with a Carriage return, as below -


'=B':@INROWNUMBER:'+1' :CHAR(13)

[manuel.gomez]

'=B':@INROWNUMBER:'+1' :CHAR(13)

hehehe, thanks a lot for your answer, but I must confess: I already tried
and tried also with char(13):char(10)
of course, none of them worked

[manuel.gomez]

As far as it seemed there was no option to solve this via Datastage, I decided to write a macro to be executed as the Excel was opened, so it was automatic and transparent for the user. The macro is this:

Code: Select all

Private Sub Workbook_Open()

Sheets("Mailing4").Activate
 Dim i As Integer
 i = 2
 Do While Cells(i, 1).Value <> "" And Cells(i, 2).Value <> ""

        Cells(i, 8).NumberFormat = "General"
        Cells(i, 8).Formula = Cells(i, 8).Formula
        i = i + 1
 Loop
 
End Sub

Now the problem is different. When I open the Excel, the macro is executed and the formula cells look brilliant. But if I tried to run again the DS job (dropping and creating the 'table' again, as there is no other way, at least I cant find it, to clear the excel worksheet to insert new data), the job ends in error.

This is because Excel asks for confirmation to drop the worksheet (I got this doing it manually in Excel). The message would be something like this:

Code: Select all

Selected worksheets can hold data. If you still want to delete them, press Acept

I triend executing 'Application.DisplayAlerts = False' on the macro, at the begining and in the end, but error persists.
I also tried with this in the following subroutines (but it did not work out):

Private Sub Workbook_Activate()
Private Sub Workbook_Deactivate()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Although I know I had only few chances to find somebody who passed through this earlier than me....is anybody out there who can help me?

[ogmios]

It's one of the smaller weaknesses of DataStage, in 1 project I worked around it via Kettle/PDI... but that's another ETL tool and off-topic here :D