I'm using a workflow to extract data from an SFTP server and then pull that data into a data project. This works great, but rather than updating current records using a unique ID, or appending the current records with the new import, I want to fully overwrite the current data with the new import.
Essentially, each time the workflow runs I want to replace the current data with the new import. Is this possible?
Extract From SFTP/Load Into Data Project-FULL OVERWRITE
Userlevel 6
+33
Userlevel 5
+25
Hi jmborzick,
It sounds like you might be able to use the Datasets API to tackle what you need. There are endpoints for deleting and updating records, which reference the primary key of each record so it should hopefully be straightforward.
You can add API calls as a Web Service task in your workflow.
Best of luck!
Userlevel 6
+33
bgooldfed Thank you for pointing me in this direction. I have no experience with APIs so this is a learning curve for me. In reading the documentation linked, it reads as if I need to identify the specific record I want to delete each time the API runs. Am I reading this correctly? If so, how could I modify that so that it would delete all records every night so that the next time the SFTP workflow runs only the newest data remains?
Userlevel 5
+25
https://community.qualtrics.com/XMcommunity/discussion/comment/52813#Comment_52813Ah, you probably can't do this in a workflow then. There's no way of telling the API to delete all records, instead you need to gather all of the record IDs and loop through each one, deleting them one at a time. Workflows don't currently have the ability to loop, so no dice.
The next best solution would be to write your own script (implementing the aforementioned loop), but if you're running it every night you will need a machine/server running during that time.
Userlevel 6
+33
Thank you bgooldfed, that wasn't the answer I was hoping for, but I appreciate your help.
Leave a Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.