Import data from xls or csv files with other delimiters than comma

[Gwena_Jaouen]

I have a few questions regarding data import into the Retool database:

1. Is it possible to import XLS sheets instead of CSV files?
2. If not, can we import CSV files with delimiters other than commas?

I'm working with files that need to be delimited with either a TAB or a semicolon. This is because the data contains lots of text with commas, making it impossible to save as a CSV with comma delimiters.
How can we work around this issue? Thanks a lot for your help!

[pyrrho]

Hello!

From the Retool side, you could probably throw together a quick app that takes the data and inserts it into an already created table with matching headers but that seems a bit clunky and hyper specific.

Would it be possible to export your XLS sheet data to CSV but change the column formats to include the quotes? This can be done in Excel for any column that could contain commas:

Select the cells / Right click the column
Choose Format Cells and then pick the Custom option
Replace the default @ Type value with \”@\”

I think this might let you then import the CSV directly through the Retool DB

[jg80]

Short Answer

(1) Yes, it is possible (and easy!) to import Excel files into Retool
(2) Sure, but you would need to code this up, and it shouldn't be required because (1) and also because a properly formed CSV file can contain commas within a field, provided the data for the field is surrounded by double quotes.

Longer Explanation

In Excel - When you save an Excel file with commas in the fields as a CSV (sample):

Click Yes

It should wrap fields with commas in double quotes in the CSV file (like so):

The data in the file should look like this:

Col1,Col2,Col3
"This, is an Excel file","In the excel, there are, in fact, lots of commas","The commas, are within the values of the, many wonderous, columns"
"And, my friend, this is yet another row","This row, like the first, also uses commans","Comma. Comma. , , , , , "

In Retool - when you upload the file with the file input component, you can tell it to parse the file.

Then the data is available to you as fileDropzone.parsedData[x] where the [x] is necessary for File Dropzone since the component allows you to upload more than 1 file. For an Excel file, the data gets parsed by sheet, so you need to play around a bit depending on the format of your file. In my example, I used {{fileDropzone2.parsedValue[0].Sheet1}}:

If you drop the CSV instead, you can still parse, but the data will come differently. In my case, {{fileDropzone2.parsedValue[0]}}

Good luck and let us know if you come across any issues!

[Gwena_Jaouen]

I went for the easy solution of opening my dataset in Google Sheet and downloading it in csv. That CSV was then easy to import into Retool. Thanks everyone for the suggested solutions!