Transforming Flat File with multiple rows for 1 record to Flat File with mutliple columns

Anonymous — Tue, 21 Apr 2020 15:19:32 GMT

Hi everybody,

I usually manage to do what I want with Talend Data Integration, but not that time...

I have a flat file extracted from a SAP Portal that gives me the Java roles associated with their "actions", their users, and their groups.

The file is quite simple.

It is structured that way :

[role]

rid=<ID of the Java role>

rdesc=<description of the role>

action=<portal actions possible when the user has this role, delimited with ;>

user=<users affected to this role, delimited with ;>

group=<group of users affected to this role, delimited with ;>

...and you have that structure for each Java role of the SAP Portal...

The relationship is :

rid - rdesc : 1-1

rid - action : 1-n

rid - user : 1-n

rid - group : 1-n

I would like to transform my file into 2 column files, structured that way :

rid | rdesc | user

...and another one with the group :

rid | rdesc | group

As an example I give you an input file :

[role]
rid=portal_content/mybeautifullrole1
rdesc=My beautifull role 1
action=ACTN.AUTH_DS.un:N$sap.com/com.sap.security.core.ume.service$Manage_My_Profile

group=ZROLE1;ZROLE2

[role]
rid=portal_content/mybeautifullrole2
rdesc=My beautifull role 2
user=TOTO;TATA;TITI

group=ZROLE1

[role]
rid=portal_content/mybeautifullrole3
rdesc=My beautifull role 3
user=TATA;TITI

...and the result should be :

File for the users :

portal_content/mybeautifullrole2 | My beautifull role 2 | TOTO

portal_content/mybeautifullrole2 | My beautifull role 2 | TATA

portal_content/mybeautifullrole2 | My beautifull role 2 | TITI

portal_content/mybeautifullrole3 | My beautifull role 3 | TATA

portal_content/mybeautifullrole3 | My beautifull role 3 | TITI

File for the groups :

portal_content/mybeautifullrole1 | My beautifull role 1 | ZROLE1

portal_content/mybeautifullrole1 | My beautifull role 1 | ZROLE2

portal_content/mybeautifullrole2 | My beautifull role 2 | ZROLE1

I have tried to do it with tFileInputMSDelimited, tFileInputMSPositionnal, tDenormalize, ... without success.

Does anyone have an idea to do that sort of work ?

Regards.

Guillaume

Re: Transforming Flat File with multiple rows for 1 record to Flat File with mutliple columns

Anonymous — Wed, 22 Apr 2020 11:04:26 GMT

I finally succeed to do that quite simply.

You have to use the tFileInputMSPositionnal to do that :

The important functionnality is the parent line to establish the link with the ID !

The Head value is defined in relationship with the position "0-4" :

rid= for rid=
rdes for rdesc=
acti for action=
user for user=
grou for group=

Here are all the schemas :

...and the corresponding model is "4,9999" (4 for "rid=").

You have to add a "rid" field that will be filled by the parent line.

...and the corresponding model is "6,9999,9999" (6 for "rdesc=").

You have to add a "rid" field that will be filled by the parent line.

...and the corresponding model is "7,9999,9999" (7 for "action=").

You have to add a "rid" field that will be filled by the parent line.

...and the corresponding model is "5,9999,9999" (7 for "user=").

You have to add a "rid" field that will be filled by the parent line.

...and the corresponding model is "6,9999,9999" (6 for "group=").

I obtain 5 files with the key "rid" and all the relationships I need : rdesc, action, user and group :

I hope I have been clear enough.

Regards.

Guillaume

topic Transforming Flat File with multiple rows for 1 record to Flat File with mutliple columns in Talend Studio

Transforming Flat File with multiple rows for 1 record to Flat File with mutliple columns

Re: Transforming Flat File with multiple rows for 1 record to Flat File with mutliple columns