How to convert vertical data to rows

[Phil3]

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?

[Anonymous]

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

[Phil3]

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.

[Phil3]

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

[TRF]

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

[Phil3]

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?

[TRF]

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

[Phil3]

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

[TRF]

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

[Phil3]

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
 "