Regarding Transforming Excel Data

[#1]

Hello Friends,

I appreciate your time. I am looking for some transformation step while loading my excel file. Kindly have a look at my attached excel file and I am looking for a data model as attached text file. Kindly could anyone help me regarding this issue ASAP.

Thank you again!

Best regards,

Kiruthiga

[john9inno]

Hi~!

Try below

Directory;

LOAD @1 as Phase,

     @2 as Result,

     @3 as Value1,

     @4 as Value2

FROM

test.xlsx

(ooxml, no labels, table is Sheet1, filters(

Rotate(left),

Replace(1, bottom, StrCnd(null))

));

[#3]

Hello John,

I appreciate your help.Thank you

I do have another query,how to eliminate some columns(eg col2,col3..col10)while loading excel through transformation?Could you kindly let met know?

Thank you again

Regards,

Kiruthiga

[john9inno]

add below in the load script

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 10))

.......etc

thanks.

John

[#5]

Thanks so much John; I sincerely appreciate your time and help!

Best regards,

Kiruthiga

[john9inno · Accepted solution]

happy to hear that it was helpful.

[#7]

Hello John,

My pleasure and again started

I have 100's of column to delete. Is there any way we could try using column range or some other function to delete columns while loading?

Thanks for your time again and I sincerely appreciate it.

Kiruthiga

[john9inno]

is there any commonality among columns you want to delete?

thus we can input condition to delete selectively.

John

[#9]

Hello John,

Thanks again for your time again.Actually, I have attached the sample source and data model. Could you kindly have a look and if possible get a script to achieve the data model.

Thank you!

Kiruthiga

[#10]

Actually, The column I am deleting doesn't have anything in common. I am deleting those because it is not specified in my data model provided

Thank you!