topic Re: How to convert vertical data to rows in Talend Studio

How to convert vertical data to rows

Phil3 — Wed, 02 Oct 2019 17:44:10 GMT

I have data from Remedy Service Desk that looks similar to this:

Serial Number+ : 2004012345O
# of CPUs :
# of Disk Drives :
# of Slots Used :
AC Input Current(536871005) :
AC Input Voltage(536871004) :
Access Method :
Access MethodFR(536871135) :
Acquired Method(260000000) :
Action on Submit(250000015) :
ArchiveReady(536871128) :
Asset History(200003000) :
Assigned-to(4) :
Assignment Group :
Assignment Individual :
Associated Program :
AssociatedProgramFR(537038842) :
Associationdel'utilisateur(936870998) :
AT User? : No
Attribute Notes :

This repeats (there are more fields mind you) for every workstation. How do I convert this to rows?

Re: How to convert vertical data to rows

Anonymous — Wed, 02 Oct 2019 21:36:05 GMT

Hi,

Could you please try tPivotToColumnsDelimited for converting to columns?

Please refer the link below for component details.

https://help.talend.com/reader/L0Sr3ydCNjhsuv4ObSVt4Q/~ZjLZkfhCa1Za5X0sGrzjQ

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

Re: How to convert vertical data to rows

Phil3 — Thu, 03 Oct 2019 11:46:53 GMT

I looked at that, but I can't get it to work. Can you be more specific on how to configure it for my applications (see the sample data posted previously)?

Thanks.

Re: How to convert vertical data to rows

Phil3 — Thu, 03 Oct 2019 11:53:40 GMT

What kind of tFileInput… is this type of file? How do I get Talend to parse the field labels into columns?

Re: How to convert vertical data to rows

TRF — Thu, 03 Oct 2019 13:18:51 GMT

@philjmaier you need a little transformation before to go with tPivotColumnsDelimited component.

For that, you have to add a tMap like like this one:

Here is what the expressions associated to local variables are:

column : a sequence from 1 to n (resetted when the number of properties per device is reach)
group : a sequence from 1 to n with the following expression:

Var.column == 1 ? Numeric.sequence("group", 1, 1) : Var.previousGroup; 
if(Var.column == 20) Numeric.resetSequence("col", 0);

2 things are done here:

- if current value for Var.column is 1 then add 1 the group number else get the group number from Var.previousGroup wich is defined later

- if current value for Var.column is 20 then reset Var.column to 0 (as soon as you have more or less of 20 properties per device you need to change this value - maybe using a context variable)

previousGroup : setted from Var.group

The tMap output flow is composed of 3 columns:

current column number varying from 1 to 20 depending on the number of properties per device
current group number varying from 1 to n depending on the number of devices
value : the value for the current column

Next is component is tPivotColumnsDelimited which configured as explained in the documentation:

And here is what the output file looks like (2 devices in my case):

group;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20
1;Serial Number+ : 2004012345O;# of CPUs :;# of Disk Drives :;# of Slots Used :;AC Input Current(536871005) :;AC Input Voltage(536871004) :;Access Method :;Access MethodFR(536871135) :;Acquired Method(260000000) :;Action on Submit(250000015) :;ArchiveReady(536871128) :;Asset History(200003000) :;Assigned-to(4) :;Assignment Group :;Assignment Individual :;Associated Program :;AssociatedProgramFR(537038842) :;Associationdel'utilisateur(936870998) :;AT User? : No;Attribute Notes :
2;Serial Number+ : xxxxxxxxxxxxxO;# of CPUs :;# of Disk Drives :;# of Slots Used :;AC Input Current(536871005) :;AC Input Voltage(536871004) :;Access Method :;Access MethodFR(536871135) :;Acquired Method(260000000) :;Action on Submit(250000015) :;ArchiveReady(536871128) :;Asset History(200003000) :;Assigned-to(4) :;Assignment Group :;Assignment Individual :;Associated Program :;AssociatedProgramFR(537038842) :;Associationdel'utilisateur(936870998) :;AT User? : No;Attribute Notes :

When you will read the file, just ignore the header line and the group column and that's it (I think so).

Re: How to convert vertical data to rows

Phil3 — Thu, 03 Oct 2019 14:50:45 GMT

Thanks, it's almost working. The problem now is that there are some fields that are not on a single line:

Non-Operating Temp(536871008) :
Not Found On(200003002) :
Notes : 2015-01-29 10:09:53 AM mtm032
ANNA-LISA WALKER no longer associated with this work

Notification Address(250000009) :
Notification Address2(251000009) :

Is there a way to account for this type of scenario?

Re: How to convert vertical data to rows

TRF — Thu, 03 Oct 2019 14:58:00 GMT

Push the result from the DB to an output delimited file with CSV option "Text enclosure" setted to """. Then reuse this file as the input for the rest of the job.
Thank's to mark your case as solved (Kudo also appreciated).

Re: How to convert vertical data to rows

Phil3 — Thu, 03 Oct 2019 15:06:01 GMT

I don't quite understand what you mean?

This is what I have so far:

tFileInputFullRow -> tMap -> tPivotToColumnsDelimited

Note that some of these multi-line fields are 30 rows long..

Re: How to convert vertical data to rows

TRF — Thu, 03 Oct 2019 15:21:16 GMT

Input values must be enclosed between "".

If values are comming from a DB, connect the tDbInput component to a tFileOutputDelimited, set the schema with a single column called "line", the go to "Advanced setting" and tick "CSV options".

This will produce records like this:

"Value starts here
continues here
finishes here"

Then, replace your tFileInputFullRow by a tFileInputDelimited.
Set the schema with a single column called "line" and tick "CSV options".
Values with line feed should now be considered as a single logical record.

If you cannot transform the input file, I'm afraid you have a problem...

Re: How to convert vertical data to rows

Phil3 — Thu, 03 Oct 2019 16:00:20 GMT

The values are coming from a file, not a DB. I now have:

tFileInputFullRow -> tFileOutputDelimited ->tMap -> tPivotToColumnsDelimited

The tFileOutputDelimited is producing:

"Non-Operating Temp(536871008) : "
"Not Found On(200003002) : "
"Notes : 2015-01-29 10:09:53 AM mtm032"
" ANNA-LISA WALKER no longer associated with this work"
" "
"Notification Address(250000009) : "
"Notification Address2(251000009) : "

What I think we need is similar to this:

"Notes : 2015-01-29 10:09:53 AM mtm032
ANNA-LISA WALKER no longer associated with this work
"

Re: How to convert vertical data to rows

TRF — Thu, 03 Oct 2019 16:09:50 GMT

You need to replace tFileInputFullRow and change the file content as explained in the previous post. Changing the file content maybe an other case. I suggest you to mark this case as solved and open a new one with this specific subject (something like "How to retrieve logical records when values may be on more than one physical record and not enclosed by "")

Re: How to convert vertical data to rows

TRF — Thu, 03 Oct 2019 16:23:13 GMT

Thank's!
Also think about creating your post in "Design and Development" forum.