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.
Hello Tom,
You should not use a validate step. Validate step stops the execution of server action if the validation fails.
You need a repeat which repeats each record, and inside it a condition step which checks if the id already exists in the DB - if it exists then update record, else insert.
Thanks Teodor, I realised the validation step stops execution if it fails, but what you describe is what I want to do it, but haven't discovered how to.
inside it a condition step which checks if the id already exists in the DB
How do I do this? The only way I've found to test for a value existing (or not) is:
(To answer you question @a.alpasian)
.. but it doesn't return any value to use as a condition. I tried {{stock_id}} as the condition.
You just need an additional query step, in the repeater, before the condition. These are the steps:
The first steps are the same as in the import and inset video:
Database connection
File Upload
Import CSV
Repeat (import CSV)
Then in the repeater steps you need a query, which will be used to check the table, if the record exists or not.
So add the table in the query, and then switch to Filters to filter the database table ID by the ID from the CSV file, it is returned from the repeat after import csv step:
After the query add a condition step, and use the query as a value – this checks if the filtered query returns a result or not (i.e. if the record already exists in the database table or not):
THEN (if the record exists) -> add an Update Record step, add the values that need to be updated in the db and click filters tab. For filter value use the ID from the CSV file.
ELSE (if the record does not exist) -> add an Insert Record step, add the values that need to be inserted in the db table.
Great - thanks Teodor. All the individual parts were working fine (upload, import, insert and delete). It was the condition which was the problem. I’ll try this out.
Thanks - this works very well, at least for imports which are not too large.
I was concerned that the import would seem very slow, compared to using LOAD DATA INFILE, and because so many queries are executed importing like this. However, it seems fine but exceeds the default PHP script execution time quite quickly - with a file containing 10,000 records, it only gets as far as about 7000.
Is there any way of capturing errors like this: ?
Also, is the information which is usually returned from MySQL accessible (info about the number of inserts and changed records etc.)? Assuming it’s not, I tried to put this into sessions, eg:
… but don’t know to display the information on the page. I created sessions in App Connect with the same names and added them to the page like this: {{uploadresults.data.insertcount}} but nothing is displayed. Do I have to refresh the page in some way? I also tried putting the information into a Notification, but that didn’t work either.
Yes, I know. I usually I have it set higher than the default 30 seconds anyway, but if it’s not set higher, the import will just stop, having missed out records. Therefore I wondered if there is a way to capture the error that I showed. As it is, I can display an error message if the script times out, but without any information. It’s not critical, but in general it would be useful to know if error details are accessible in such a way they can be displayed to the user.
More importantly, do you know why the information about the number of inserts/updates is not displaying in the way I’m trying to do it? The session variables contain the correct information. I expect there’s a better way of doing this.
What is your session containing exactly?
The server action just stops on server error, but you can use the catch step to set a value and then use this value to show a notification on the front-end.
I would like to give a summary of the upload/import in any case - not necessarily when there was an error.
I’ve just done a test import. Here are the contents of the session file: updatecount|d:98;insertcount|d:59;
I would like to display this information after the import (obviously in a slightly modified form).
Using the way I usually do imports, MySQL would return something like this:
Records: 500 Deleted: 0 Skipped: 0 Warnings: 0
(I realise Wappler couldn’t return something like this as it executes lots of queries rather than just one when importing)
Thanks @brad. I found it after you mentioned it earlier. I had never noticed it before.
I usually set the timeout in an ini file on the server. However I’ve just tried the Wappler setting on a server where I can’t make changes directly - and it didn’t make any difference. I set the script timeout in global settings to ‘300’ (presumably the number is in seconds, without ‘s’ etc.). I think it probably depends on the server configuration whether this option works or not.
[Perhaps this should be moved to a new thread - ‘Using sessions and variables created in Server Connect, in App Connect’]
I got round the problem (displaying import results - see screenshot below) by displaying the session values using PHP and a line of Javascript to refresh the page (so the session values would be up to date). The session values are set in Server Connect, so I would have thought they should be accessible in App Connect - but I can’t find out how.
I also tried ‘Set Value’ to create variables in Server Connect. In this case I could access the variables in App Connect but only their initial value was displayed - not the values after the import process. I know the variables contain the correct values as I can see them if I run the query straight from Server Connect. This is the value I want to access in App Connect:
After much trial and error, I found I could get the variable to update as I wanted, like this:
?
