topic Re: replace a value in string in file with diferent row schema in Talend Studio

replace a value in string in file with diferent row schema

uzix — Mon, 10 Sep 2018 19:47:46 GMT

Hi all,

i need some help, i have a input file1 like this:

00;column1;column2;column3;

11;column1;column2;

22;column1;column2;column3;column4;

33;column1;column2;column3;column4;column5;

00;column1;column2;column3;

11;column1;column2;

22;column1;column2;column3;column4;

33;column1;column2;column3;column4;column5;

i have other file2 with column1;column2

i need to replace the value of column3 on the lines that start with 00 doing a join between the 2 files where

file1.column2=file2.column2

maintaining the same lines of file1 at the end with the replaced value- final file.

i thought of using the tfileinputfullrow - file1,thashmap-file 2 extracting and treplace but who can i do a join ? can somebody help me with this issue?

Re: replace a value in string in file with diferent row schema

TRF — Mon, 10 Sep 2018 20:59:38 GMT

You should have the expected result using a tMap to join both files with a left outer join.
Use a tFileInputDelimited to read file1 (with schema zero;column1;column2;column3;column4).
Connect it to the tMap as the main row.
Use a tFileInputDelimited to read file2 and connect it to a 1rst tMap to add a new column (say zero) initialized to "00".
Connect this tMap to the 1rst one and base the left outer join on columns 00 and column 2.
On the right part of the tMap, use the following expression for column3:
row2.column3 == null ? row1.column3 : row2.column3
In this expression, row1 is for main row and row2 is for lookup. Adapt the names depending on what they are in your job.

Re: replace a value in string in file with diferent row schema

uzix — Mon, 10 Sep 2018 21:27:47 GMT

hi TRF,

thanks you for your help,

a question, will the schema of the lines be preserved?

"00;column1;column2;column3;
11;column1;column2;
22;column1;column2;column3;column4;"

Re: replace a value in string in file with diferent row schema

TRF — Mon, 10 Sep 2018 22:54:22 GMT

Basically all records will have the same schema.
A second step using tFileInputFullRow should help to retrieve original schemas (probably with help of String.replaceAll to remove ";;" sequences).

Re: replace a value in string in file with diferent row schema

fdenis — Tue, 11 Sep 2018 08:14:19 GMT

you can use tMap.
when tmap is into your job you can select it and press F1 to reach help and sample.
It's one of the greatest Talend component.
replay if you need more help.
Regards

Re: replace a value in string in file with diferent row schema

uzix — Tue, 11 Sep 2018 09:09:25 GMT

hi TRF,

you pointed me in the right direction, i thank you.
i used a tmap and left join.
if i use tFileInputFullRow and then use replace, do you know how can i save the lines to a file without schema ? maybe to a txt and then change extension to csv ?

Re: replace a value in string in file with diferent row schema

fdenis — Tue, 11 Sep 2018 09:19:16 GMT

there is multiple solutions
but you can directly save as csv.

you can define your csv with more columns who are filled or not you just have to manually manage separators
you also can define the last field as String (containing all columns in csv format).

Re: replace a value in string in file with diferent row schema

TRF — Tue, 11 Sep 2018 09:37:39 GMT

@uzix, you may use a tFileOutputDelimited with a single column schema as soon as you set the Field Separator to a special character which never appears into your file.

Here is how it should look like:

And the result file is like this:

00;column1;column2;column3;
11;column1;column2;
22;column1;column2;column3;column4;
33;column1;column2;column3;column4;column5;

You have a varying number of fields for each record.

Re: replace a value in string in file with diferent row schema

uzix — Tue, 11 Sep 2018 09:45:54 GMT

thank you TRF and fdenis,

you both provided me with solutions to my issue.