Importing CSV into Retool Database Table with null values

Queries and Resources
1

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?

Thanks!

1 Like
2

Hey @sr112233!

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?

3

@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:
image

4

Hello there,
I'm having the same issue, would be great if this canbe fixed

5

I'm having the same issue. Been playing around with the different options including passing "NULL" as the default value or as SQL. Please help!

6

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.

7

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

{{fileInput.parsedValue[0].map(row => _.mapValues(row, (value) => value === "" ? null : value))}}

(More docs on _.mapValues here!) You can then use that result in an upsert query to your Retool Database resource:

Can you let me know if that works for now?

1 Like
8

FIY, this worked for me :blush:

I was having the same problem

1 Like
9

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?

10

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.

11

Hey @jg80!

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!

12

Yeah - dev getting around to fixing this will be sweet.

My scrappy workaround was instead of formatting my columns as type DATE, I made them varchar and put a CHECK in place e.g.

term_date varchar CHECK (term_date ~ '^\d{4}-\d{2}-\d{2}$' OR term_date = '')

The bad thing about this is downstream I'll have to cast these varchar to a date.

1 Like