How to parse CSV files in Retool using Java Script

Parsing CSV files and uploading them to the Retool database or any other source is typical for any business. Retool will help you parse your CSV, and then you can upload that data to your target source. This works perfectly when you don’t have to map your CSV with your Table.

But what if you must upload your CSV into multiple tables, or your CSV columns don’t match your data source, and you need to map those columns?

You can do this quickly by using Retool JS queries to transform the CSV into your desired shape.

Example:

Let’s begin the tutorial to see how to parse the file in Retool using Java Script. First, you can prepare your CSV. In this tutorial, I’m using the following CSV.

But My Table Structure is a bit different. I don’t have an exact column-to-column mapping, which means I can’t use Retool’s bulk insert because that Query is based on column name mapping.

As you can see, instead of the “mobile number”, I have a “phone number” column. Same for “company website” and website. Also, I’ve modified the “isActive” column while inserting the record.

Java Script Transformer

Let’s begin writing our Java Script transformer in Retool to map our CSV with our table columns. Before that, you must upload a CSV file in Retool using the Upload file and check the parse file checkbox. After uploading it, it will parse the file and store the parse information under the parsedValue array. If you’ve one sheet, all of the data will be under the first index of the parsedValue array, which you can access like parsedValue[0]. Then, you must loop over all the CSV columns and construct a new array of objects according to your desired data shape.

				
					var data = fileButton1.parsedValue[0];

const newData = data.map(obj => {
 
  return {
   username: obj.username,
   company: obj.company,
   phonenumber: obj.mobilenumber,
   website: obj.companywebsite,
   emailaddress: obj.email,
   isActive: obj.activeornot=="yes"?1:0
  };
});

return newData;

So, in this Transformer, we’re reshaping our data according to our table structure. Now, you can refer to this in your Bulk insert query, and then Retool will take care of all the insertions.

If you have more questions or are looking for someone to complete your Retool project, please contact me.