Insert Update and delete in destination file

[MaheswarReddy]

Hi Team,

I have a file input and file output,

It's an every day load and i will get file everyday
I want to lookup my output file and check if there is any change in the new input file and if the record doesn't exists  in the output file , we need to insert into the output  file, if the row already exists , I need to update the data in the output file .

My source and target are files, So I'm stuck in the implementation part.

Can anyone help me out on this.

Thanks for your help.

Mahesh

[TRF]

If you have fields such as unique key and version number, you may just use tMap with inner join with new file as main row and previous one as lookup.
If you have to be able to detect any change value for any field you have to get the MD5 value for all the fields but the key giving the following schema: keyField, md5Field. Then you can compare both versions using a tMap with new MD5 file as main row and previous one as lookup. Use keyField for the inner join.
If key exists but md5 is not the same value, the record has been updated.
If key doesn't exist, the record is new.
If you need to detect deleted records, you need an other subjob where you main row becomes the lookup, then rejected records are deleted ones.

This link may help you https://community.talend.com/t5/Design-and-Development/How-to-generate-MD5-hash-value/td-p/43927

[MaheswarReddy]

Thanks for your quick reply,

But is there any other way to do other than writing a routine ?

Thanks,

Mahesh

[TRF · Accepted solution]

You can also join each main row field to the corresponding one in the lookup.

You need 2 steps:

1- does the record exist in the previous file?

2- no, this is a new record. yes, have a 2nd tMap with all fields joined to each other to get changed records.

[tnewbie]

Hello TRF,

Can you please explain how to 'update' or 'delete' record in a flat file target.

Example

Data in Src_file :

col1,col2,col3

1,john,employee

2,mary,retired

3,sam,joining

Data in Tgt File(Active employees only):

empno.empname

1,john

2,mary

3,sam

4,Hary

5, Phil

Now that Mary has retired, i need to delete row#2, can you please help