Load and rename field

[#1]

Hi all!

I want first tothank you all for your support and continue by asking a question.

I want to load data and rename the field in the scrript it should look like this:

TMP:

LOAD

    RIGHT(TRIM(@1),4)   AS Jaar

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

    (ooxml, no labels, table is [Uploadsheet jaarplan], filters(

    Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

    ));

In the name of the table, the 4 last caracters are 2015 and I want 2015 to renamed as year (jaar) in dutch language.

anyone who can help please.

[ashfaq_haseeb · Accepted solution]

Hi,

Try this

LOAD

    RIGHT(TRIM(@1),4)   AS Jaar,

right(Filebasename(),4) as Year,

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

    (ooxml, no labels, table is [Uploadsheet jaarplan], filters(

    Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

    ));

Regards

ASHFAQ

[jagan]

Hi,

Try FileBaseName() to get the loading file name and then use Right() to get right most characters

LOAD

    Right(TRIM(@1),4)   AS Jaar,

Right(Filebasename(),4) as Year

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

    (ooxml, no labels, table is [Uploadsheet jaarplan], filters(

    Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

    ));

Regards,

jagan.

[its_anandrjs]

Hi,

This is your field expression RIGHT(TRIM(@1),4)   AS Jaar

Or you want to extract from the Filename the Year values. Or you can try the below expression also

Load

Year(Date(Date#( RIGHT(TRIM(@1),4),'YYYY'),'YYYY') )  AS Jaar,

Right(Filebasename(),4) as NewTestField

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

    (ooxml, no labels, table is [Uploadsheet jaarplan], filters(

    Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

    ));

Regards

Anand

[#5]

Actually what I don't know is how step by step I can load the file i have and use filters and renaming. after choosing table files and enable transformation step, I can conditionaly delete some rows but I'm failing to rename the  Right(TRIM(@1),4)   AS Jaar

[its_anandrjs]

What comes in the field @1 and i believe by using Right(TRIM(@1),4)   AS Jaar you rename @1 field to Jaar will it works or not nut your expression is correct.

Regards

Anand

[ashfaq_haseeb]

Hi,

Can you post sample data

Regards

ASHFAQ

[MarcoWedel]

$(v.HuidigSeizoen) as year