Excel Reading

suryeshchatwani · Post by **suryeshchatwani** » Fri Oct 17, 2014 5:57 pm

Hi,

I have requirement to read Excel in such a way :

xxxxx Apple Orange Mango
Peter 5 9 10
Ellie 9 6 3
Sarah 2 10 7

and covert it into such a way:

Peter Apple 5 (or Apple Peter 5)
Peter Orange 9
Peter Mango 10
Ellie Apple 9
Ellie Orange 3
and so on

I am thinking Excel Read + Pivot OR Excel + Transformer with loop

but in either case i cant materialize the implementation concretely nor think of a better idea...

Would love somebody's help.

Thank you

Suryesh

ArndW · Post by **ArndW** » Sat Oct 18, 2014 12:15 am

Your first suggestion is the best approach. Read the Excel into DataStage and then pivot the results on the person.

chulett · Post by **chulett** » Sat Oct 18, 2014 7:58 am

Will you be attempting to read the xls directly or converting it to a csv first?

suryeshchatwani · Post by **suryeshchatwani** » Sun Oct 19, 2014 6:11 am

I was planning to use the unstructured stage to read excel directly...
I am new to datastage, i dont know how converting to csv would help, wouldnt it be better to just leave it as xls? and read it?...

chulett · Post by **chulett** » Sun Oct 19, 2014 8:27 am

Better? Depends. A csv is just a flat file and anything can read it and it is what you had to do before things like that new stage showed up. That or, since you are on Windows, use an ODBC driver for Excel. Saves an extra step since you don't have to do the "Save as" but many people find dealing with ODBC and Excel... problematic. Never used that Unstructured Data stage so will be curious how it goes.

Let us know!

suryeshchatwani · Post by **suryeshchatwani** » Mon Oct 20, 2014 2:44 am

Hi ArndW, Thanks for reply...I might be doing this wrong, but when I do what you say:

what I get is :
null Apple
null Orange
null Mango
Peter 5
Peter 9
Peter 10
and so on

null = xxxxx (I put xxxx so that the table spaces out)

and yeah, I am using Pivot Enterprise and Horizontal Pivot...