topic Re: convert columns to rows with no aggregation in Talend Studio

convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 08:25:38 GMT

Hi
I've got this kind of data table (Excel or csv):

name joe peter Kelly
surname smith warton hartford
age 23 28 34
occupation professor bartender manager

and I want to use the first column as names for my table fields like this:
name surname age occupation
joe smith 23 professor
peter warton 28 bartender
kelly hartford 34 manager
I've used several components but to no avail. Take into account I don't need aggregation whatsoever, just transpose and convert rows to columns using first column as header.

Thanks

Re: convert columns to rows with no aggregation

Anonymous — Wed, 30 Apr 2014 08:49:09 GMT

Hi,
Could you please have a look at a custom component TalendExchange:tTurnRow to see if it is what you're looking for?
Here is a related forum with use case https://community.talend.com/t5/Design-and-Development/resolved-row-to-column-transformation/td-p/98080.
Best regards
Sabrina

Re: convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 08:53:15 GMT

Hi xdshi
No such component on my Talend installation. My version is 5.4.1r

Thanks for your quick response and please forgive my duplicate post due to browser error

Re: convert columns to rows with no aggregation

Anonymous — Wed, 30 Apr 2014 08:57:58 GMT

Hi,
This is a custom component developed by our community user and shared on Talend Exchange, you can download it from Talend Exchange freely, read the below link to learn how to install custom component into Talend Studio.
TalendHelpCenter:Installing a custom component.
So far, there is no a proper talend component for your requirement.
Best regards
Sabrina

Re: convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 09:14:41 GMT

Hi Sabrina
TalendExchange:tTurnRow component does not work with 5.4 version

any other hint would be much appreciated

Regards

Re: convert columns to rows with no aggregation

Anonymous — Wed, 30 Apr 2014 09:20:17 GMT

Hi,
This component is old, it is not compatible with the new releases of Talend Studio, and mlange do not update it!
To get this component work with the new releases of Talend Studio, you have to modify two places.
1. Modify the tTurnRow_java.xml file, add the family element, for example:
<FAMILIES>
<FAMILY>Processing</FAMILY>
</FAMILIES>
<DOCUMENTATION>
<URL/>
</DOCUMENTATION>
2. Modify the tTurnRow_messages.properties file, remove this line from the file:
FAMILY=Processing
Feel free let me know if it is OK with you.
Best regards
Sabrina

Re: convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 10:00:56 GMT

Hi Sabrina

Followed your instructions but component does not show on palette.
any other suggestion?

regards

Re: convert columns to rows with no aggregation

Anonymous — Wed, 30 Apr 2014 10:07:21 GMT

Hi,
Have you checked "Troubleshooting" section in KB article TalendHelpCenter:Installing a custom component? Is it working after restarting studio?
Best regards
Sabrina

Re: convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 10:30:47 GMT

Hi Sabrina

Solved the issue deleting the Components Caché.

Thanks a lot !!!

Re: convert columns to rows with no aggregation

Anonymous — Wed, 30 Apr 2014 10:51:54 GMT

Hi,
Is this custom component working well for your use case? Don't hesitate to post your issue on forum.
Best regards
Sabrina

Re: convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 11:40:41 GMT

Hi Sabrina
I'm testing my ETL with a limited set of data. (see output before and after TurnRow)
Custom component seems to work fine but......(there's always a "but") it only processed 4 columns.
Taking into account that my first colum (the future column names) has 14 records, how can I force tTurnRow to process all data?
Thanks for being so helpful

OUTPUT BEFORE COMPONENT (INPUT DATA)
.---------------------+------------------------+------------------------+------------------------.
| tLogRow_1 |
|=--------------------+------------------------+------------------------+-----------------------=|
|Column0 |Column1 |Column2 |Column3 |
|=--------------------+------------------------+------------------------+-----------------------=|
|NAS-Port-Type |Wireless-802.11 |Wireless-802.11 |Wireless-802.11 |
|Event-Timestamp |Apr 21 2014 00:00:13 UTC|Apr 21 2014 00:00:13 UTC|Apr 21 2014 00:00:13 UTC|
|Acct-Session-Id |80203e90 |80267e90 |80203e90 |
|Acct-Output-Gigawords|0 |0 |0 |
|Acct-Input-Octets |2745995 |2746795 |2745995 |
|Acct-Input-Gigawords |0 |0 |0 |
|Timestamp |1398038413 |1345668413 |1398038413 |
|User-Name |user@name.com |user@name.com |user@name.com |
|NAS-Port |2149596816 |2149596816 |2149596816 |
|Acct-Session-Time |7022 |70 |7022 |
|Acct-Status-Type |Interim-Update |stop |Interim-Update |
|Acct-Output-Packets |20912 |23342 |20912 |
|Acct-Output-Octets |17889908 |17885408 |17889908 |
|Acct-Input-Packets |19376 |19345 |19376 |
'---------------------+------------------------+------------------------+------------------------'

OUTPUT AFTER COMPONENT (OUTPUT DATA)
.---------------+------------------------+---------------+---------------------.
| tLogRow_2 |
|=--------------+------------------------+---------------+--------------------=|
|Column0 |Column1 |Column2 |Column3 |
|=--------------+------------------------+---------------+--------------------=|
|NAS-Port-Type |Event-Timestamp |Acct-Session-Id|Acct-Output-Gigawords|
|Wireless-802.11|Apr 21 2014 00:00:13 UTC|80203e90 |0 |
|Wireless-802.11|Apr 21 2014 00:00:13 UTC|80267e90 |0 |
|Wireless-802.11|Apr 21 2014 00:00:13 UTC|80203e90 |0 |
'---------------+------------------------+---------------+---------------------'

Re: convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 12:05:03 GMT

Hi
in addition to this issue, the real production input file may have thousand columns (see that every column is a record for each field)
Maybe I'm doing it all wrong from the beginning.....

Regards

Re: convert columns to rows with no aggregation

_AnonymousUser — Wed, 30 Apr 2014 12:30:45 GMT

just for the record this is my original file contents to process (maybe I'm doing it all wrong from the beginning)

Mon Apr 21 00:00:13 2014
Acct-Status-Type = Interim-Update
NAS-Port-Type = Wireless-802.11
User-Name = "user@name.com"
NAS-Port = 2149596816
Acct-Session-Id = "80203e90"
Event-Timestamp = "Apr 21 2014 00:00:13 UTC"
Acct-Input-Octets = 2745995
Acct-Output-Octets = 17889908
Acct-Input-Gigawords = 0
Acct-Output-Gigawords = 0
Acct-Input-Packets = 19376
Acct-Output-Packets = 20912
Acct-Session-Time = 7022
Timestamp = 1398038413
Mon Apr 21 00:00:14 2014
Acct-Status-Type = stop
NAS-Port-Type = Wireless-802.11
User-Name = "user@name.com"
NAS-Port = 2149596816
Acct-Session-Id = "80267e90"
Event-Timestamp = "Apr 21 2014 00:00:13 UTC"
Acct-Input-Octets = 2746795
Acct-Output-Octets = 17885408
Acct-Input-Gigawords = 0
Acct-Output-Gigawords = 0
Acct-Input-Packets = 19345
Acct-Output-Packets = 23342
Acct-Session-Time = 70
Timestamp = 1345668413
REPEATED THOUSAND TIMES........

I've processed the file this way:
fileInputDelimited ("=" as field separator key/value)
> tMap (trimming and deleting empty lines)
> tDenormalize (column: values, Delimiter: ";")
> tFileOutputDelimited

So I get this:
---------------------+---------------------------------------------------------------------------------------------------.
| tLogRow_1 |
|=--------------------+--------------------------------------------------------------------------------------------------=|
|keys |values |
|=--------------------+--------------------------------------------------------------------------------------------------=|
|NAS-Port-Type |Wireless-802.11;Wireless-802.11;Wireless-802.11;Wireless-802.11 |
|Event-Timestamp |Apr 21 2014 00:00:13 UTC;Apr 21 2014 00:00:13 UTC;Apr 21 2014 00:00:13 UTC;Apr 21 2014 00:00:13 UTC|
|Acct-Session-Id |80203e90;80267e90;80203e90;80267e90 |
|Acct-Output-Gigawords|0;0;0;0 |
|Acct-Input-Octets |2745995;2746795;2745995;2746795 |
|Acct-Input-Gigawords |0;0;0;0 |
|Timestamp |1398038413;1345668413;1398038413;1345668413 |
|User-Name |user@name.com;user@name.com;user@name.com;user@name.com |
|NAS-Port |2149596816;2149596816;2149596816;2149596816 |
|Acct-Session-Time |7022;70;7022;70 |
|Acct-Status-Type |Interim-Update;stop;Interim-Update;stop |
|Acct-Output-Packets |20912;23342;20912;23342 |
|Acct-Output-Octets |17889908;17885408;17889908;17885408 |
|Acct-Input-Packets |19376;19345;19376;19345 |
'---------------------+---------------------------------------------------------------------------------------------------'

Re: convert columns to rows with no aggregation

Anonymous — Wed, 30 Apr 2014 12:46:55 GMT

Does this solves your problem?
If not what was your expected output?
Vaibhav

Re: convert columns to rows with no aggregation

_AnonymousUser — Thu, 01 May 2014 10:01:58 GMT

Hi sanvaibhav
This did not solve my problem.
As you can see TurnRow component just turned 4 columns but I could have thousands of them.
regards

Re: convert columns to rows with no aggregation

_AnonymousUser — Thu, 01 May 2014 10:04:35 GMT

Hi sanvaibhav
This did not solve my problem.
As you can see TurnRow component just turned 4 columns but I could have thousands of them.
regards

Not really thousands. The actual example you can see below has got 14 rows to pivot into 14 columns. The result transposed 4 columns only.
BEFORE COMPONENT (INPUT DATA)
.---------------------+------------------------+------------------------+------------------------.
| tLogRow_1 |
|=--------------------+------------------------+------------------------+-----------------------neutral
|Column0 |Column1 |Column2 |Column3 |
|=--------------------+------------------------+------------------------+-----------------------neutral
|NAS-Port-Type |Wireless-802.11 |Wireless-802.11 |Wireless-802.11 |
|Event-Timestamp |Apr 21 2014 00:00:13 UTC|Apr 21 2014 00:00:13 UTC|Apr 21 2014 00:00:13 UTC|
|Acct-Session-Id |80203e90 |80267e90 |80203e90 |
|Acct-Output-Gigawords|0 |0 |0 |
|Acct-Input-Octets |2745995 |2746795 |2745995 |
|Acct-Input-Gigawords |0 |0 |0 |
|Timestamp |1398038413 |1345668413 |1398038413 |
|User-Name |user@name.com |user@name.com |user@name.com |
|NAS-Port |2149596816 |2149596816 |2149596816 |
|Acct-Session-Time |7022 |70 |7022 |
|Acct-Status-Type |Interim-Update |stop |Interim-Update |
|Acct-Output-Packets |20912 |23342 |20912 |
|Acct-Output-Octets |17889908 |17885408 |17889908 |
|Acct-Input-Packets |19376 |19345 |19376 |
'---------------------+------------------------+------------------------+------------------------'

OUTPUT AFTER COMPONENT (OUTPUT DATA)
.---------------+------------------------+---------------+---------------------.
| tLogRow_2 |
|=--------------+------------------------+---------------+--------------------neutral
|Column0 |Column1 |Column2 |Column3 |
|=--------------+------------------------+---------------+--------------------neutral
|NAS-Port-Type |Event-Timestamp |Acct-Session-Id|Acct-Output-Gigawords|
|Wireless-802.11|Apr 21 2014 00:00:13 UTC|80203e90 |0 |
|Wireless-802.11|Apr 21 2014 00:00:13 UTC|80267e90 |0 |
|Wireless-802.11|Apr 21 2014 00:00:13 UTC|80203e90 |0 |
'---------------+------------------------+---------------+---------------------'

Re: convert columns to rows with no aggregation

Anonymous — Thu, 01 May 2014 10:59:18 GMT

Whether your columns in each block are varying?
Whether the order of the column is changing?
If the answer is Yes for above, then how do you plan to arrange the data in the output? Any logical or business suggestion?
Vaibhav

Re: convert columns to rows with no aggregation

Anonymous — Thu, 01 May 2014 22:37:53 GMT

I feel like this is a complete hack, but it works if you're ok hard coding the column names. I couldn't figure out how to make it more dynamic.
tFileInputDelimited -> tJavaFlex -> tMap
tFileInputDelimited schema only has 1 column... Column0. It's set to read the file for line breaks, but doesn't parse any of the data in each row.
tJavaFlex splits the column into a key/value pair and assigns them to variables. It then compares the Key to each column name and if it's a match, assigns the value to that column.
Because it's a tJavaFlex, once a value is assigned to a column, it stays assigned through subsequent rows until it is replaced. So when it finds column1 in the first row, it keeps that value all the way through row15. You get a growing table as it finds more values. 15 lines in your original file means 15 rows of output, but only the last row has all the rows filled in.
tMap has a filter so it only shows rows where Column14 is not null.
Like I said, it's a hack and all of your key names are hard coded into the code. But it accomplishes what you wanted to do. You could then loop through all your files or if this is all 1 big file, you can do something at the top of your Main code that looks to see if Column14 != null and if that's true, reassign null to all the column rows to empty them and start over.
Main code part of tJavaFlex
if (row1.Column0.indexOf("=")!=-1)
{
String key = row1.Column0.substring(0,row1.Column0.indexOf("=")-1).trim();
String value = row1.Column0.substring(row1.Column0.indexOf("=")+2).replace("\"","").trim();

if (key.equals("Acct-Status-Type"))
{
row2.Column1 = value;
}
else if (key.equals("NAS-Port-Type"))
{
row2.Column2 = value;
}
else if (key.equals("User-Name"))
{
row2.Column3 = value;
}
else if (key.equals("NAS-Port"))
{
row2.Column4 = value;
}
else if (key.equals("Acct-Session-Id"))
{
row2.Column5 = value;
}
else if (key.equals("Event-Timestamp"))
{
row2.Column6 = value;
}
else if (key.equals("Acct-Input-Octets"))
{
row2.Column7 = value;
}
else if (key.equals("Acct-Output-Octets"))
{
row2.Column8 = value;
}
else if (key.equals("Acct-Input-Gigawords"))
{
row2.Column9 = value;
}
else if (key.equals("Acct-Output-Gigawords"))
{
row2.Column10 = value;
}
else if (key.equals("Acct-Input-Packets"))
{
row2.Column11 = value;
}
else if (key.equals("Acct-Output-Packets"))
{
row2.Column12 = value;
}
else if (key.equals("Acct-Session-Time"))
{
row2.Column13 = value;
}
else if (key.equals("Timestamp"))
{
row2.Column14 = value;
}
}

Re: convert columns to rows with no aggregation

Anonymous — Thu, 01 May 2014 22:41:21 GMT

Last post didn't show the output.

Re: convert columns to rows with no aggregation

_AnonymousUser — Mon, 05 May 2014 12:34:44 GMT

Hi Sbaer
Thanks a lot for your suggestion but the issue is that nobody can assure me the fields are coming the same order every set of data. As sanvaibhav asked:
Whether your columns in each block are varying? Yes
Whether the order of the column is changing? Yes
My initial idea was to use TurnRow component forcing it to pivot all columns not only 4

Regards