I've a table.. for example "Users" with simple schema such as:
first name
last name
birthdate
email
But.. I've to keep this table updated regularly (per-day) with script (Javascript).
At the beginning, I just import the CSV manually from GUI and dump to table "users".
I've the script already to pull the CSV file from the link, and parse the CSV into array data. But then.. I'm a bit confused how to access to existing table in retool and do add new records / update the existing data if necessary.
Is there any example code how to do that?
Thanks, I'm newbie here using Retool
If you have the script setup to pull the CSV data and it is/can be formatted into an array of object data then you should be able to use the Retool DB Resource (I assumed your initial load of data went into a Retool DB table and not a SQL Server or other DB system) and select the Bulk Upsert via a Primary Key action from the GUI setup.
If you are able to share the format of the CSV data after the script has grabbed from the link I'm sure the proper PK/object format can be achieved.
It looks this works.
But.. it seem I've trouble when do bulk insert (update seem to be fine) when the records still not exists.
This is related with "date" value. The error message like this:
invalid input syntax for type date: ""
Note:
This is also happened when I do bulk import via Retool GUI using CSV file too.
It also said something wrong with the "date" value.
But if you import from the fresh table, it just works fine
This might require some formatting on the date using a new Date() or moment() if your CSV data is a string. You also might want to test changing the column type in your DB from datetime to string or ensure that the column is nullable in the DB setup.
