Hi-
I'm trying to bring some data over into a new Retool Database Table from existing data from Google Sheets that I have saved as a CSV file. I have a few fields that are defined as either "date" or "enum" with "Nullable==true". Some of the rows I'm trying to import have no data for these fields, but the CSV imported is complaining because it doesn't like the values. I've tried all of the following:
field is "" (empty)
field is "null" (text string)
field is "NULL" (text string)
How can I get the CSV importer to map these fields to NULL in the Retool DB?
Passing an empty value for a particular cell (i.e. ,,) should read as null for "date" columns, however, for "enum" or other text columns the value is indistinguishable from the empty string and gets read as the latter. It doesn't look like Retool has a way to specifically set a null value identifier at the moment.
I can file it as a feature request with the dev team - would you mind expanding a bit on why it's important for your use case to use null? Is it general best practice or something more specific?
@Kabirdas I am having the same issue. It is not a matter of needing some certain "value" to recognize as null - it is simply giving the error whether I pass ,, or ,NULL, or ,null, - no matter what, I get this:
I ended up just writing a python script to do my import, and handled the NULLs myself.
I'm not sure there would be a way to represent a NULL in a CSV file uniquely, unless they did something special like match a specific string like '' as a special case. And then they would need to enable that mode, because it's possible that whatever string they chose could be part of your input data.
My guess is you're better off rolling your own import script, or fixing up the DB post-import if you need this.
Hey folks! Sorry for initially underestimating the impact here and thanks for following up on the thread. I've bumped the issue with the dev team and can let you know here when it's fixed.
In the meantime, you might try manually importing the data as @sr112233 mentioned. It is possible to do this with Retool as well! File input components have a Parse files setting that will automatically read CSV files:
With the setting turned on, any uploaded CSV should be accessible in JSON form:
As such, empty fields will still be represented as empty strings, but you can map over the result to convert them to null values with something like
Thanks for the solution presented @Kabirdas. It works well, and useful if expecting repeated uploads, but a bit of a slog if just trying to prototype quickly. Any updates on some sort of direct switch or other solution in the Retool DB when clicking "Import CSV" from there?
Also, it seems I cannot set a Boolean column to NULL in the Retool DB front end. Can work around it, but again would be nice for quick prototyping to be able to modify directly.
I've filed a request for handling null values better via the UI. The original issue in this thread is still being looked into, one thing that was found was that different file extensions can cause an issue (e.g. .numbers as opposed to .csv). If that isn't the case for you and you have a sample file that's causing problems that you can share, it might also be helpful!
Edit: The dev team has changed some messaging around file extensions within the app, if folks are still seeing issues uploading with accepted file extensions please let us know/share examples!
Same issue for me.. Just want to import a CSV that contains some dates.
Technically I could populate every field with a default date but that removes possibility for criteria where date field is empty and will cause issues in UI where items are rendered conditionally based on field having a value
I'm going to try the above workaround and see if it fixes it.
It would be better for me to do that than transform in a text field as I'll have reformat every occurrence,
I think importing CSV containing an empty date value is probably quite common so hope the devs can fix it soon as it's been 15 months since the issue was 'Bumped' with dev team as per above, and issue still here in November 2024
Just to clarify, are you trying to import data into an existing table or a brand new table? The former looks like it still has issues but the latter works as expected if you leave the cell blank instead of null.
+1 on wanting blank values in a CSV to import as NULL when inserting into an existing table regardless of data type. Presented workarounds were not desirable.
Workaround I used: Inserting into a new table and writing a custom SQL query to insert into old table. The task took me 2 hours of time.
Thanks for keeping this thread updated, @ScottR and @Christopher_B! I've confirmed that this is still an issue on the most current cloud release and have bumped the corresponding ticket internally.
While there are workarounds, this is one of those things that should just work.
This has been a continued frustration. In our use cases, every table includes a numeric or date field, and in most cases there are null values. This makes the database upload functionality entirely useless.
Thanks for providing some additional context, @Sean_Willerford! I've passed on your comment internally as an argument for prioritizing this particular issue.
I'm still not able to import null values via the Retool DB UI "Import via CSV" feature into a boolean field. The error mentioned before is triggered.
I've tried null, NULL and empty.
