Column Headers to rows

Five Stars

Column Headers to rows

Hi,

I've a CSV file in he below format

UserId|Col1|Col2|Col3|Col4

1111|20.5|23.5|25.5|27.5

2222|40.5|43.5|45.5|47.5

3333|60.5|63.5|65.5|67.5

4444|80.5|83.5|85.5|87.5

5555|90.5|93.5|95.5|97.5

 

I need it in below format

UserId|Data|ColHeader

1111|20.5|Col1

1111|23.5|Col2

1111|25.5|Col3

1111|27.5|Col4

2222|40.5|Col1

2222|43.5|Col2

2222|45.5|Col3

2222|47.5|Col4

3333|60.5|Col1

3333|63.5|Col2

3333|65.5|Col3

3333|67.5|Col4

............

................

...................

 

Is there any way to achieve this using the Data Integration tool?

Please help

 

Highlighted
Fifteen Stars TRF
Fifteen Stars

Re: Column Headers to rows

tMap is your friend


TRF
Five Stars

Re: Column Headers to rows

Hi, 

I'm a beginner.

Could you please be more elaborate about using Tmap for above use case

Fifteen Stars TRF
Fifteen Stars

Re: Column Headers to rows

Sorry, I read your post too fast!

In this case, your friend is tSplitRow.

Here is the job design, the tSplitRow settings and the according schema:

Capture.png


TRF
Five Stars

Re: Column Headers to rows

The issue here is I have a .csv file. I need to perform the above operation in that file. I think tFixedFlowInput is not suitable for reading from a file. Am I correct?

Fifteen Stars TRF
Fifteen Stars

Re: Column Headers to rows

tFixedFlowInput is here just for the example.

Replace it by a tFileInputDelimted and that's all.


TRF
Five Stars

Re: Column Headers to rows

Your answer is correct. But in my case, my file contains about 60 headers and 1000s od data. So it's practically not feasible to map all the columns in the above mentioned way. And I like to have a generic method so that any CSV file in the above format but with different header names should also be processed.

Fifteen Stars TRF
Fifteen Stars

Re: Column Headers to rows

60 columns is not so huge.
Also, the volume of data doesn't matter and you don't have to worry about the header line as soon as you use constant values for the target.
I recommand you to keep simple using standard components. Else, for this case you will have to do it yourself using a tJavaRow component (I'm afraid).

TRF
Five Stars

Re: Column Headers to rows

The problem is that I've to apply this to different csv files with different number of columns and differnt header names. And I'm don't really understood "you don't have to worry about the header line as soon as you use constant values for the target.". Could you please elaborate or give an example?
Fifteen Stars TRF
Fifteen Stars

Re: Column Headers to rows

"different csv files with different number of columns". In this case you have to go with your own tJavaRow and elaborate your algorithm.

TRF
Five Stars

Re: Column Headers to rows

Thanks

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch