I often need to create facilities to upload CSV or tab files files to a server and import the data into MySQL tables. I usually use LOAD DATA LOCAL INFILE for this. It’s extremely fast and has some useful options - in particular, if the import data contains a primary key which already exists (where a duplicate would be created), the record will be updated rather than inserted, using the REPLACE option. This is exactly how I want imports to work. I’ve been struggling for hours to get this to work with Wappler.
The upload and import work fine - as long as no duplicates are created. Wappler uses multiple inserts which would work, expect, as far as I can see, the ON DUPLICATE KEY UPDATE options is not available in Wapper; this could solve the duplicate issues. (I think it really should be available in Wappler - or perhaps it is.)
I thought it might be possible to check if the key which is about to be inserted exists and depending on the result, do an INSERT or UPDATE:
I thought this looked promising, and it all works fine if only inserts are needed. However, as far as I can see, all execution stops if the Validation check fails, and there’s no way to use the failure/error or branch conditionally.
I tried putting a Catch between the two actions:
… but this doesn’t work. Under Globals there is the $_ERROR option - perhaps this could be used with a Condition.
I’ve mainly been using trial and error, and haven’t made much progress. I can’t find much documentation about the approaches I’ve tried, so I’m just guessing really. I should point out that the uploading/importing of potential duplicates is itself not a problem or something which needs to be avoided. MySQL offers good solutions to such situations - hopefully there’s a solution using Wappler too. I would be grateful for any suggestions.