replace a value in string in file with diferent row schema

Five Stars

replace a value in string in file with diferent row schema

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?

 


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

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

@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:

Capture.JPG.png

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.


TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

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

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.

TRF
Five Stars

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

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;"

 

Fifteen Stars TRF
Fifteen Stars

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

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

TRF
Twelve Stars

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

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

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

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 ?
Twelve Stars

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

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Fifteen Stars TRF
Fifteen Stars

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

@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:

Capture.JPG.png

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.


TRF
Five Stars

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

thank you TRF and fdenis,

 

you both provided me with solutions to my issue.

 

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download