Hi all, I currently have a job where I have to upload CSV data into a database on a monthly basis. This job is working fine. I now have a scenario where I am to re-enter a particular month data into the database.
For this - a) I need to delete the same month data already present in the database. b) Then re-upload the data for that particular month from the CSV file.
As such, I modified my current job and added a tMysqlRow component with a Delete query. I tried to place an "OnComponentOk" on my input CSV file to link with the tMysqlRow component. When I run the job, I see duplicate records for a particular month in the database ( Old monthly data does not get deleted) Could someone help me on this.... Best Regards, Zohaib
The OnComponentOK link does not always do what you expect. This has to do with the architecture of Talend-- each component consists of a BEGIN, MAIN, and END section. The OnComponentOK trigger fires when the MAIN section of the code completes without error. Because of this, I always recommend avoiding using this trigger type unless you are sure of what it will do for that specific component. I would suggest reworking your job to avoid use of OnComponentOK. to accomplish this, I imagine a high-level job structure like this: 1) examine file and compare to control table. is this a new file? 2) if it exists in the control table, delete all rows in data table and control table with the same load_id. if it does not exist in the control table, do nothing 3) Create row for control table and Load the file.
Thanks for the reply. I do not have a specific ID/Key field in the table. As such, I cannot determine, if the data in the CSV contains new rows or not. Is there a way, where I can execute a delete operation, before the CSV data is loaded into the table? Thanks again, Zohaib
yes. When you load the original csv, log the file name into a control table-- also you will want to generate a load_id that will be tagged onto every row of data loaded from that csv. when you receive a file of the same name, you will be able to retrieve the load_id, and then use that to delete all data associated with the previous file before loading the new one.