Update existing rows when importing a CSV file automatically

Working with databases can be a bit tough, especially when it comes to updating rows. Usually, when you import data, it adds all the rows as new, even if they’re already there. But what we really need is to update the rows that exist, using key values like the main field or primary key. This would make handling data much easier, especially when it’s from other tools.

Baserow is a relational database that lets you sort your data neatly, and it feels like using a spreadsheet. But Baserow is way stronger than a regular spreadsheet and can manage more data. If you haven’t yet, give our 60+ templates a shot. They’ll help you quickly set up databases for things like your website, CRM, apps, or CMS.

What we’ll do

In this tutorial, we’ll walk you through the process of updating files during import processes, preventing duplication of rows, and ensuring data integrity. We’ll cover the following steps:

• Set up your Baserow database
• Configure table structure
• Create a database token
• Add a CSV module to import data
• Add Baserow module to list rows
• Add the Baserow module to update existing rows
• Test and schedule the Make scenario

Prerequisites

To complete this tutorial, you’ll need the following:

• A Baserow account with a table where you want to import data.
• Access to a Baserow workspace and table.
• CSV containing data you want to import.
• Make, an automation tool that lets you connect different apps and services to automate workflows and repetitive operations.

Let’s explore how to do just that.

Link data to existing Baserow rows

1. Set up your Baserow database

Log in to your existing Baserow account or create a new account if you don’t have one already.

Once you log in, you can access the dashboard to add workspaces and databases. You can work with an existing workspace, database, or table, or set up a new workspace from scratch.

In this tutorial, we will set up a new workspace and create a database from scratch.

1. To create a new workspace, simply click on the + Create workspace button. Then, click on Add workspace.

   If you’re just starting with Baserow, we recommend you read our support article on how to create a workspace and how to add collaborators to a workspace.

2. Now that the workspace is set up, you can either create a database from scratch or choose a template from our template library. Let’s add a database to the workspace.

3. Click on the + Create new button to start creating a new database.

4. Select the “Database” option to create a database from scratch.

5. Next, add a new table or click on a table within the existing database to open it. A default table will be added to the newly created database.

That’s it! we’ve successfully created a new database. Now we can populate, manage, and organize data in Baserow.

2. Configure table structure

The database will contain the table we need. In this case, we have a destination table where you want to import the data. Make sure the table structure matches the fields in your data source.

When you import the data from the CSV, you can use the unique code as the key. Baserow will recognize the unique codes in the data in the CSV to match the data in the Baserow table.

3. Create a database token

Within your Baserow user account, you can create multiple unique database tokens that allow Baserow to connect with other apps. You can create and find the database token under your account settings.

1. Go to your profile, then navigate to your Settings page
2. Click on the Database tokens tab
3. Click on the Create token + button
4. Write a descriptive name for the token, and select the existing workspace that you want to tie the token to
5. Click on the Create token button to create a new database token for the selected workspace and for the authorized user.

Database tokens provide similar functionality to login credentials, but they provide additional security and flexibility. A token, like your username and password, should be kept secure and handled with the utmost confidentiality.

Creating database tokens is available to all Baserow users; you don’t need to be on a paid plan.

4. Add a CSV module to import data

Next, specify the data source you want to import from. This might involve selecting a file, connecting to a database, or providing a URL. It could be a database, a spreadsheet, a CSV file, or from a web service.

Ensure that your data source is accessible and formatted correctly for importing. If necessary, clean up the data to remove any inconsistencies or errors.

In this case, let’s use a csv file. Once we have our list, we can import these to update the corresponding rows on Baserow. When the CSV data is imported, we use the unique code as the primary key.

To get started, create a new Make account or log into your existing account. Then create a scenario and add modules to the scenario.

1. Select the app that will trigger your scenario. Click the first + button and select CSV as the trigger app.

2. Choose one of the actions from the list. In this case, select the Parse CSV action.

   The Parse CSV transformer lets you parse a csv text from a received text value or a file. If your data comes in binary form (typically from a file), you have to use the toString() function to convert the binary data to String:

3. Configure the module.

4. Click OK to save this module and Run once to test the connection.

Now, let’s view the data in our Baserow table.

5. Add Baserow module to list rows

We want Baserow to recognize that the unique code in the CSV data matches the data already in the list.

1. Select the app that will trigger your scenario. Click the + button and select Baserow as the trigger app.
2. Choose one of the actions from the list. In this case, select the List rows action.
3. Create a connection or select a previously configured connection from the dropdown options. To create a new connection:
   1. Enter your Baserow API URL. Find your Baserow API URL in your API documentation. If you are using SaaS version baserow.io, the default URL is https://api.baserow.io, otherwise, replace the URL with your self-hosted URL.
   2. Enter your Database token. Find your Database Token in Account → Settings → Database tokens as directed above.
4. Enter the table ID of the existing table to update. Find the Table ID by clicking on the three dots next to the table. It’s the number between brackets.
5. Click OK to save this module and Run once to test the connection.

You should get a response back that resembles the following:

Now, let’s have Baserow read and link the unique codes in the existing rows to update when we import data into the Baserow table.

6. Add the Baserow module to update existing rows

Once the data source is connected, we’ll need to map the fields from your data source to the corresponding fields in the destination table. This ensures that the data is imported into the correct columns.

We’ll use the same database as in the first Baserow module. But, the table ID will be the one we want to store the data.

1. Click the + button and select Baserow as the app.
2. Select the Update a Row action. This updates existing rows.
3. Select the previously configured connection from the dropdown options.
4. Enter the table ID where the row will be created in. Find the Table ID by clicking on the three dots next to the table. It’s the number between brackets.
5. Map the ID from the Baserow module and map the details from the CSV module.
6. Click OK to save this module and Run once to test the connection.

7. Test and schedule the Make scenario

Our scenario is set up and ready to activate, but we may still need to run a quick test. Run the scenario, Make will load the prompt and store the data in the Baserow database. After the import is complete, review the imported data to ensure that everything looks as expected.

Let’s jump back to Baserow. Check your Baserow table for the responses!

Specify when and how frequently an activated scenario runs. To do so, go to the scenario detail page and locate the Schedule tab or click on the clock icon to set a preferred schedule.

To set the schedule, adjust the Run scenario to set the time when it should run.

Save and activate the scenario by toggling on the scheduling switch at the bottom left corner.

Make offers advanced options like conditional logic and filters for more complex scenarios. You can add filters to the CSV trigger and Baserow action to only process specific data. Explore the “Update Rows” action in Baserow if you need more granularity over field updates.

Explore Make’s other features like multi-step modules for advanced automation and define how Make handles errors.

Summary

This tutorial shows you how to match unique IDs in your spreadsheet with rows in a Baserow table using Make. You can tweak it for your own spreadsheets and tables. By following this step-by-step process, you’ll be able to link your data to Baserow automatically. This saves time and helps keep your data correct and consistent.

Other useful resources

The following articles may also be helpful:

• How to link Baserow rows in bulk automatically
• How to model your Baserow data
• Automate custom notifications from Baserow form submissions with Zapier Webhook

In case you’ve run into an issue while following this tutorial, feel free to reach out to ask for help in the Baserow community.