Number becomes space (_) and turn columns into rows

[Anonymous]

Hi 

I am totally a newbie for Talend. With an excel file as an input, I have 2 issues here:

data sample

numbers become _

1. Columns with years (1960, 1961, 1962, ..., 2017) becomes _960, _961, _962, ... , _017 (as shown in second picture). Can someone explain to me why is this happening and how to fix it?
2. I want to transpose all years columns to rows like diagram below:

output

I saw discussion on using tUnpivotRow but I am currently using Talend Open Studio which that component cannot be used in TOS.

Full data is attached.

Your help is much appreciated.

[Anonymous]

Hello,

Could you please refer to this topic about:https://community.talend.com/t5/Design-and-Development/Transpose-Columns-to-Rows/td-p/40077 to see if tjavaflex component is able to achieve your goal?

Best regards

Sabrina

[akumar2301]

Also , looks like your input is some kind of yearly report. So year columns might increase or change every year.get confirmation from your input provider and design your job accordingly.

[Anonymous]

hi @uganesh ,

I got this data from open source (World Bank) and I believe it is in yearly report. The years are fine in the Excel file but messed up in Talend. So I need clarification why is this happening and how to solve it?

[Anonymous]

Hi @Catty 

      As per the naming conventions, you cannot directly use 1960 as a column name.

     If you try to add a new column with 1960 in the schema, you will get above error. The variables in schema should start with either an alphabet or an underscore.

    So could you please change the naming convention accordingly. When you are making columns to rows, you can use a case statement to convert it to 1960.

     Your second query about transpose have already been answered by others.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂