Update and Delete data in a file target

[tnewbie]

Hello All,

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

Example

Data in Src_file :

empno. empname, employment_status

10,john,employee

20,mary,retired

30,sam,employee

Data in Tgt File(Assuming this is for Active employees only):

empno.empname

4,Hary

5, Phil

10,john

20,mary

30,sam

Now that Mary has retired, i need to delete row#2, I am hoping that if "Delete" is possible on a target file, then "Update" is going to be pretty similar.

can you please help.

[TRF]

This is not possible to delete or update records from a sequential file using Talend.

You need to read the file you want to change, apply your logic using components such as tMap, tJavaRow and so on, and finally rewrite your filte including records which are not changed at all.

[tnewbie]

Hello TRF,

I seriously doubt if that can be the case...Lets assume that we have one source file CA_Sacramento_emp_dtls.txt  (with HR details) coming in from each location of my company and at the enterprise level i want to keep a aggregated file Org_wide_emp_dtls.txt that holds data of all active employees from all locations.Now, as and when an employee retires or have there status changed(employees may get converted from contractor to full-timers ), i want to delete or update that record from the master file Org_wide_emp_dtls.txt.

Can you please give a second shot and suggest if there is a way.

[Anonymous]

@tnewbie 

I agree with @TRF 's thoughts.

If you are using files to handle these type operations using files, that may create serious data issues in future. Database is the ideal candidate for update operations like employee status code modification (in your example). I seriously doubt any enterprise customer will plan to use files to do these type of operations.

So the file operations are basically to either overwrite or append but not selective data correction.

Also think about a scenario where file will get corrupted. In that case, the entire California employee data details are gone forever but if you are using database, roll back is very easy.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[tnewbie]

Hi Nikhil,

Thanks for jumping in..

I have put in file sources/targets just to portray a simpler scenario, however there are no hard and fast rules that files cannot be used as intermediary sources for the down stream applications. Especially when you talk about multi layered data hops, Files were always the first choice of preferences. File can be anything like a json file, xml,EDI or just an entry into msg queue.

Getting back to the point, please let me know if it is possible to update and delete entries in the file targets (which are not relational targets) if deletes is a problem, do we have anything for updates at least?

Thanks in advance.

[Anonymous]

Hi,

    I am not against file based processing but for the first scenario you had depicted, I would say file is not a right candidate. But files are quite useful for various requirements for data processing.

    Coming to your query, you cannot do row (or individual item if it is files like XML or JSON) based data modification or deletion using Talend file components. If you have to do that, you will have to first read the file, modify or remove the data using data modification and filtering components of Talend and then push it back to file system.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[tnewbie]

I would assume the inference from all this is, there is no direct component / ability to update or delete flat file data using Talend. The only work around may be to cache the data and play around with it either through java custom code or something similar.

Thanks again Nikhil...

[Anonymous]

You are right.

I would also recommend to refer below post where the solution provided by @TRF was accepted by the Talend Community member.

https://community.talend.com/t5/Design-and-Development/Insert-Update-and-delete-in-destination-file/...

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂