Insert CSVs into a database

You've got a CSV of records, and you want to insert some of them into your database.

📘

Get started with Retool Utilities

Write a CSV to Postgres or MySQL in < 3 minutes (and 20+ other developer utilities) with Retool Utilities.

Let's say we're engineers, and operations people keep on bugging us about CRUD-related tasks, like bulk adding rows into our database via uploaded CSVs. We'll build a tool to selectively upload rows from a CSV into our API, in 5 minutes.


If you haven't already, you can create a Retool account to follow along.
Create an account

1. Uploading files

Let's start by dragging in a File Button component from the right panel and toggle on Parse files:

Great! This button will now automatically parse CSV and JSON, so we can try uploading a file now. If you don't have a CSV ready, you can download one here.

After uploading the CSV, you should see the .parsedValue of the File button component populate with rows:

Some parsed rows after uploading a file.Some parsed rows after uploading a file.

Some parsed rows after uploading a file.

Great!

2. Render uploaded rows

Now that our CSV is in Retool, we want to render its rows into a Table. Let's drag one on from the right hand side. (By default, it will come with some placeholder data, but we'll replace it with our new user data soon.)

A table, with some placeholder data.A table, with some placeholder data.

A table, with some placeholder data.

Nice! Now let's change the Table to pull in data from our File Button component, rather than from its placeholder data. Change the table data property to: {{fileButton1.parsedValue[0]}}:

Changing our `Table` to pull in data from our `FilePicker`.Changing our `Table` to pull in data from our `FilePicker`.

Changing our Table to pull in data from our FilePicker.

Great! Now, in your table, you should have the book data we previously downloaded:

Uploaded users, rendered in a `Table`.Uploaded users, rendered in a `Table`.

Uploaded users, rendered in a Table.

3. POSTing rows to a sample API

Almost there! Now let's add an action button on to each row, so we can choose which rows to update.

Let's create another query query2 and attach this query to an action button on a table.

To add an action button to a table click on the Table, and scroll down on the right hand side until you see Action, and where you can add an action button. Let's enable it, give it the text Upload, and attach it to query2.

Adding an action button on our `Table`.Adding an action button on our `Table`.

Adding an action button on our Table.

Now let's edit query2 and select REST API as the Resource type.

In our API call, we can pass in the parameters, such as name, image_url, etc. in the JSON body. If it's safe to do so (eg. you're on a staging API), then you can run the query to see if it's successful. When you're ready, let's hit "Save", and we're ready to move to the next step.

🚧

The sample onboarding API

When we say "our API" we mean the sample onboarding REST API that we include in all of your Retool apps. There's no purpose built Retool API for data persistence.

4. Verify it's working

Last step! Let's write a SQL query to actually pull in data from our database, and render it onto a Table beneath this one. To write a SQL query, let's hit the "New Query" button on the query editor, and then choose the onboarding_db resource:

The SQL for our example database is:

select * from users order by id desc;

Let's preview it, and save it if it looks good. To render it on to a Table, let's first drag one on from the right hand side. By default, it should pull in your last saved query. If not, connect your Table to your query by typing in {{ query3.data }}. Now you have your actual users pulled in!

To really test if our tool works, we'll need to refresh query3 whenever we upload a user (since we don't want to refresh the page after every insert). So let's click on the query2, and scroll to the "After this query runs" section. We want to add query3 there, so every time we add a user, our tool will pull in the new users from the database:

That's it! When you hit the action button on the first Table, it'll make a POST request back to our API. After that succeeds, the query to pull in users from our database will refresh, and we'll be able to see all the users we've added. Here's the complete tool we built in a few minutes:

This tool is now production-ready. If you don't want coworkers bugging you about CRUD tasks again, you can send them the link to this Retool, which they can use themselves. If you give them specific permissions, they won't be able to edit the tool and change the queries - they'll just be able to use the tool you've created.

Well done!


Want to build your own data processing tools?
Create an account


Did this page help you?