topic Column Headers to rows in Talend Studio

Column Headers to rows

Anonymous — Sat, 16 Nov 2024 07:11:05 GMT

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

Re: Column Headers to rows

TRF — Wed, 28 Nov 2018 16:21:47 GMT

tMap is your friend

Re: Column Headers to rows

Anonymous — Wed, 28 Nov 2018 16:31:57 GMT

Hi,

I'm a beginner.

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

Re: Column Headers to rows

TRF — Wed, 28 Nov 2018 16:44:24 GMT

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:

Re: Column Headers to rows

Anonymous — Wed, 28 Nov 2018 17:02:18 GMT

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?

Re: Column Headers to rows

TRF — Wed, 28 Nov 2018 17:04:50 GMT

tFixedFlowInput is here just for the example.

Replace it by a tFileInputDelimted and that's all.

Re: Column Headers to rows

Anonymous — Wed, 28 Nov 2018 17:16:27 GMT

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.

Re: Column Headers to rows

TRF — Wed, 28 Nov 2018 18:10:30 GMT

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).

Re: Column Headers to rows

Anonymous — Wed, 28 Nov 2018 18:16:12 GMT

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?

Re: Column Headers to rows

TRF — Wed, 28 Nov 2018 18:35:29 GMT

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

Re: Column Headers to rows

Anonymous — Wed, 28 Nov 2018 18:40:00 GMT

Thanks