topic Re: Split a String with separator in multiple rows in Talend Studio

Split a String with separator in multiple rows

gadje1 — Fri, 17 Jul 2020 07:56:43 GMT

Hi everyboby,

I would like be able to split a String with @ separator like that :

ORxxx@ORxxx@ORxxx

in multiple rows like that :

ORxxx

Is there a simple way to do it ?

Here'is a sample of my input csv file :

OR00617441@

OR00595880@OR01018483@

OR00595875@

OR00617442@OR02458307@OR02413721@

OR00595879@

OR02413716@

OR00595864@

OR00075148@

OR00595863@

OR00595860@OR00595865@OR02457921@OR00595861@OR00595866@OR02457922@OR00595862@OR00595867@OR02457923@

Thanks in advance

Re: Split a String with separator in multiple rows

Anonymous — Fri, 17 Jul 2020 08:06:17 GMT

Read the entire file content as a string using tFileInputRaw, and normalize the row to multiple row to multiple items with tNormalize, remove the blank space if needed.

Please try and let me know if it works.

Regards

Shong

Re: Split a String with separator in multiple rows

gadje1 — Fri, 17 Jul 2020 08:39:42 GMT

Hi and thanks for your answer.

I tried what you said but i have an issue that talk about "The method split(String, int) is undefined for the type Object"

I put the screen shots of my job here.

Here is my issue message :

My tFileInputRaw config :

My tNormalize config :

Re: Split a String with separator in multiple rows

gadje1 — Fri, 17 Jul 2020 08:44:07 GMT

I tried with routine but my result in my csv file's like this :

mots_cles

[Ljava.lang.String;@dca536

[Ljava.lang.String;@17246de

[Ljava.lang.String;@f67ac7

[Ljava.lang.String;@16216b3

[Ljava.lang.String;@18f23c

[Ljava.lang.String;@17b1517

[Ljava.lang.String;@c0663d

[Ljava.lang.String;@1223dd8

[Ljava.lang.String;@c6a292

[Ljava.lang.String;@140d5f0

...

instead of :

OR00617441

OR00234719

OR02413723

OR00617439

OR00617440

OR00595872

OR00595873

OR00595868

OR00595874

OR00595870

OR00595880

...

Re: Split a String with separator in multiple rows

Anonymous — Fri, 17 Jul 2020 09:51:05 GMT

The input data type is Object On tFileInputRaw, check the 'read the file as string' box on tFileInputRaw, link it to a tConvertType and change the data type to String on the schema.

Re: Split a String with separator in multiple rows

JohnRMK — Fri, 17 Jul 2020 10:34:03 GMT

Hello ,

You have this kind of response because you read the memory address instead of reading the data(don't use XXXXX.toString() ) .

With tNormalize, it should work fine.

Re: Split a String with separator in multiple rows

Prakhar1 — Fri, 17 Jul 2020 10:41:58 GMT

Hi I have prepared a job for you please have a look

you need to go like this

csvfile -> tnormalize -> tmap( to remove empty rows generated by tnormalize)

I have attached ss for the job.

Please give KUDOS and Accept it as solution if you feel I solved it 😄

Re: Split a String with separator in multiple rows

Prakhar1 — Fri, 17 Jul 2020 10:42:39 GMT

My output is :

.----------.

|tLogRow_2 |

|=--------=|

|data |

|=--------=|

|OR00617441|

|OR00595880|

|OR01018483|

|OR00595875|

|OR00617442|

|OR02458307|

|OR02413721|

|OR00595879|

|OR02413716|

|OR00595864|

|OR00075148|

|OR00595863|

|OR00595860|

|OR00595865|

|OR02457921|

|OR00595861|

|OR00595866|

|OR02457922|

|OR00595862|

|OR00595867|

|OR02457923|

'----------'

Re: Split a String with separator in multiple rows

gadje1 — Fri, 17 Jul 2020 12:21:22 GMT

Thanks for your answer, JohnRMK,

i tried with tFileInputRaw->tConvertType->tNormalize->tMAp->tFileOutputDelimited but i don't know why, i still catch data that i don't need as show below.

OR00617441@

100329

OR00234719@OR02413723@

100741

OR00617439@

100742

OR00617440@

101455

OR00595872@

101488

OR00595873@

101490

OR00595868@

101491

OR00595874@

101632

OR00595870@

102054

OR00595880@OR01018483@

102316

OR00595875@

102336

OR00595869@

102367

This despite the filter i put in my tMap (row2.content.contains("@")), i checked if i didin't have a @ in the others columns of my entry file. It's continue to catch the values from the first column of the entry file.

I give you a sample of my entry file :

700334;OR00619716;BOULON CHARPENTE TETE CARRE ZN 16X180 ;70,38;86,25;D;CQ2019-2 Pg:122 Px:129.250 Rf:OR00619716 ;CQ2019- Info Stock : ARBA ACEM CAB GMB UAB ;U ;U ;CENT;1;100; ;I;VENDU A L'UNITE;02/04/04;19/02/20;10/06/2013;;OR00619716@

700335;OR00619717;BOULON CHARPENTE TETE CARRE ZN 16X200 ;73,44;90;D;CQ2019-2 Pg:122 Px:136.300 Rf:OR00619717 ;CQ2019- Info Stock : ARBA ACEM CAB GMB UAB ;U ;U ;CENT;1;100; ;I;VENDU A L'UNITE;02/04/04;19/02/20;10/06/2013;;OR00619717@

700336;OR00619062;NAISSANCE CHROME RONDE P/TUBE D.16 ;1,08;1,32;D;CQ2019-7 Pg:397 Px:1.510 Rf:OR00619062 ;CQ2019- Info Stock : CAB UAB ;U ;U ;U ;1;1; ; ;VENDU A L'UNITE;12/11/08;17/06/20;17/06/2020;0371220001645;OR00619062@

700339;OR00619718;BOULON CHARPENTE TETE CARRE ZN 16X250 ;97,92;120;D;CQ2019-2 Pg:122 Px:162.150 Rf:OR00619718 ;CQ2019- Info Stock : ARBA ACEM CAB GMB UAB ;U ;U ;CENT;1;100; ;I;VENDU A L'UNITE;02/04/04;19/02/20;29/04/2014;;OR00619718@

700340;OR00620051;BOULON CHARPENTE TETE CARRE ZN 16X275 ;82;102,5;F;CQ2019-2 Pg:122 Px:176.250 Rf:OR00620051 ;CQ2019- Info Stock : ARBA ACEM CAB GMB ;U ;U ;CENT;1;100; ;I;VENDU A L'UNITE;02/04/04;25/04/19;25/04/2019;;OR00620051@

700341;OR00619719;BOULON CHARPENTE TETE CARRE ZN 16X300 ;122,4;150;D;CQ2019-2 Pg:122 Px:195.050 Rf:OR00619719 ;CQ2019- Info Stock : ARBA ACEM CAB GMB UAB ;U ;U ;CENT;1;100; ;I;VENDU A L'UNITE;02/04/04;19/02/20;28/04/2015;;OR00619719@

...

Here is my job structure and tNomalize config :

I had understoud that was memory addresses and that the method "toString" just convert from object to String, and don't catch the value itself.

Do you know if is there a way to catch the value instead address memory ?

Thanks in advance.

Re: Split a String with separator in multiple rows

gadje1 — Fri, 17 Jul 2020 12:30:27 GMT

Hi Prakhar,

Thanks for your answer.

As show above, i had an issue in my tNormalize that came from type that was an object. For fix it, i used a tConvertType as Shong said to me to do. Now i'm able to catch the ORxxx references but an other column's data too.

Re: Split a String with separator in multiple rows

Prakhar1 — Fri, 17 Jul 2020 14:43:33 GMT

okay no problem

Re: Split a String with separator in multiple rows

gadje1 — Mon, 20 Jul 2020 08:20:31 GMT

I am close to the solution but i don't recover all the split values, for the lines i have to split like those as shown below in my CSV entry :

i maked a routine that split my strings with a @ separator as shown below :

This is my job structure and the routine that i calling :

Everything's ok in console view, i catch both tokens corresponding at the above example :

But not in my csv file output, i just catch the second token of my splited line

Is there somebody who knows why ??

Thanks in advance.