How to change column names every refresh

Highlighted
Six Stars

How to change column names every refresh

Hi. i need to refresh my workflow every month and there will be minor changes in the column headers every time. How do i make this change affect my workflow with minimum effort? i am using the Talend Open studio 6.5. Any suggestions are welcomed. 


Accepted Solutions
Eleven Stars

Re: How to change column names every refresh

@Aishwarya 

One of the solution :

 

You need to read header as another data record. As the number of Column are fixed (12) 

 

1) create a generic schema (all string ) 

Col1

Col2

.

.

Col12

2) using tMap , split there in 12 different Output for each Col and add a sequence number for each Record and each Column.

So if Input 

A,B,C
1,2,3

then there will be 3 Output for each column

RouwNum, Column Number,Col Value 

1,1,A

2,1,1

 

1,2,B

2,2,2

 

1,3,C

2,3,2

 

3) Join all output of previous subjob using tJoin and sort on (Colnum and then ID)

1|1|A
2|1|1
1|2|B
2|2|2
1|3|C
2|3|3

4) tdenormisesortrow on Colnum 

1|A,1
2|B,2
3|C,3

 

5) filter id field 

A,1
B,2
C,3

 

So now even if colName change you will have correct result.

so for 

X,Y,Z
1,2,3

result will be 

X,1

Y,2

Z,3

 

Hope this will be helpful.

Regards
Abhishek KUMAR

View solution in original post


All Replies
Community Manager

Re: How to change column names every refresh

Can you give an example of what you mean please? For example, changes in column headings could mean just the header names or could mean that they change order. How will the job know this has happened? What information is there to identify what has changed?

Six Stars

Re: How to change column names every refresh

The order remains same. There will be these small changes, But I want these changes reflected.

Like when I unpivot the table I want to see the changes as my values.Sketch.png

Community Manager

Re: How to change column names every refresh

So you will always have 12 columns? The column headers are simply dates? Do you actually need headers? What exactly are you trying to do with this data? It might be that you do not need to worry about column headers.

Six Stars

Re: How to change column names every refresh

Yes, the structure of the data remains same(i.e., number of columns). Only the column names change. I will need the column names because I will unpivot the data and I want it to look like this. This happens every month. Like there will be 12 refreshes. So is there any way to change only the column namesSketch.png

Community Manager

Re: How to change column names every refresh

I see. So how does your data arrive to Talend? The reason I ask is that there is no need to hard code your column names in Talend. You can omit the column names and simply ensure the first row of data for your output file holds your column names. Does that sound feasible?

Six Stars

Re: How to change column names every refresh

What about the column names stored in the schema? they did not change when i made changes to the file Smiley Sad
Community Manager

Re: How to change column names every refresh

The schema column names do not matter, or maybe I do not understand. Why do you think your schema column names need to change? You could have 12 columns called "hello", "world", "empty", "ferrari", "sauber", "rolo", "jumper", "skateboard", "tea", "coffee", "milk" and "sugar", and your output data can be precisely as you want it without referencing any of the column names above.

Eleven Stars

Re: How to change column names every refresh

@Aishwarya 

One of the solution :

 

You need to read header as another data record. As the number of Column are fixed (12) 

 

1) create a generic schema (all string ) 

Col1

Col2

.

.

Col12

2) using tMap , split there in 12 different Output for each Col and add a sequence number for each Record and each Column.

So if Input 

A,B,C
1,2,3

then there will be 3 Output for each column

RouwNum, Column Number,Col Value 

1,1,A

2,1,1

 

1,2,B

2,2,2

 

1,3,C

2,3,2

 

3) Join all output of previous subjob using tJoin and sort on (Colnum and then ID)

1|1|A
2|1|1
1|2|B
2|2|2
1|3|C
2|3|3

4) tdenormisesortrow on Colnum 

1|A,1
2|B,2
3|C,3

 

5) filter id field 

A,1
B,2
C,3

 

So now even if colName change you will have correct result.

so for 

X,Y,Z
1,2,3

result will be 

X,1

Y,2

Z,3

 

Hope this will be helpful.

Regards
Abhishek KUMAR

View solution in original post

Eleven Stars

Re: How to change column names every refresh

Just to add , above solution will work even if input has n number of row.
Regards
Abhishek KUMAR

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog