tutorials
Back to overview

How to transform any Spreadsheet into a Database!

Google sheets and excel export

Information is everywhere. This principle is what drives the creation of modern enterprise software. Every task, process and system generates information in a unique way. If you’ve ever worked in an organization with several departments, you may have wondered how to effectively combine all that information and draw insights from it.

Documenting complex processes in spreadsheets is like building a castle out of LEGO bricks: it’s possible, but not very flexible. Spreadsheets are great for displaying and calculating data, but when the data becomes too complex, you can look for alternatives.

What if there was a way to handle all the heavy-duty work of linking, surveys, attachments and other tasks? It would simplify information management and make it easier to store, manage and organize your data.

The good news is that there’s a much more effective way to manage information - by using a database.

What we will cover

When you’re used to working with spreadsheets, the idea of switching to new software can be intimidating. However, there’s no need to feel stressed. Transitioning is simple when you know how.

In this tutorial, we will explore:

  • Exporting spreadsheet data as CSV, XML or JSON
  • Creating a group for your project
  • Three ways of importing data into your group
    • Create a new table from a CSV/XML/JSON file
    • Add new records from a CSV/XML/JSON file into an existing table
  • Inviting team collaborators to group
  • Making data available to viewers publicly

Why Teams Should Use Databases Instead Of Spreadsheets

Are you using a spreadsheet to manage more complex data? Spreadsheets are popular because so many people use them to track budgets, store lists, or organise information. However, they fall short in certain use cases.

There’s a better way to organize and record data. Here are the top reasons why you should harness the powerful capabilities of databases:

  1. Robust functionality: While spreadsheets are great for storing and sharing information, they aren’t built for organizing different data types. With Baserow, you can integrate your data for analysis and reporting by adding any of the field types that Baserow provides, such as file, boolean, or collaborator.
  2. Flexibility: Databases have structure and offer significantly more flexibility to organise and categorise your data. Database APIs are the hooks and code that allow developers to plug in their own data-sets or applications.
  3. Collaboration: A database is a better option for maintaining control of your information and keeping teams on track. When you set up connections in a database correctly, you’ll be able to track changes and collaborate in real time.
  4. Data visualization: A table can have multiple views based on different filters and sort. A database can be used to store any type of data that is required to carry out operations like keeping track of mailing addresses, building shopping carts, or even sending emails.
  5. Relational: A database can contain relational data that is linked across multiple tables to handle complex workflows. This is ideal for complex projects in which various pieces of information are critical.

With Baserow, you can start with a database, then add a table for new employees, and another table for company data — and link every piece of information in logical ways.

Now let’s get into transitioning from your spreadsheet in Google Sheets or Microsoft Excel to Baserow.

Export spreadsheet data

First, you need to export your data. For this tutorial, we will export a spreadsheet that was built to manage all the departments in an organization. There are many different kinds of departments, such as those for finance, law, human resources, advertising, and so on.

Open your spreadsheet in Google Sheets, then click on the tab with the data you want to convert to the CSV format.

Next, go to File → Download → Comma Separated Values (.csv):

enter image description here

Your file will be saved on your device, which can then be imported into Baserow.

Import data into Baserow

Let’s get started with storing data in Baserow. Data can be added in a number of various file formats, such as XML, JSON or CSV.

Create a group

Create an account or log in to your existing Baserow account. To get started, we will first create a group called ‘Employee’. A group is a workspace where you can store multiple databases and invite collaborators.

Baserow is organised into groups. Each group has users invited to become members. Users can use Baserow completely for free and be in a group in both the self-hosted and baserow.io SaaS versions. To learn more about Baserow plans, read our documentation.

On the Dashboard page where you can see an overview of all your groups and databases, click the ‘+ Create group’ button. This button will also be visible on the sidebar.

enter image description here

Name the group to represent whatever you are working on. You can rename a group after it’s created or at any time.

Create a Database

Next, we need to create a database in the group. A database is a collection of tables , each containing rows and columns. A Baserow database can include multiple tables containing information for different projects.

To create a new blank database within a group:

  1. Click ‘+ Create new’
  2. Select ‘Database’ from the dropdown option
  3. Enter a name for the new database. When you create a database, be sure to give it a descriptive name. You can subsequently change the descriptive name as needed.

enter image description here

For more detailed instructions, view this article on how to create a database.

Option 1: Create a new table from file import

We want to import a new table within our database by uploading a file with tabular data.

Click on ’+ Create table’ within the database, then select the file format of the data you want to import. In this instance, we will import a CSV file. Alternatively, you can select XML / JSON / Paste table data.

Select ‘Import a CSV file’ then input a name for the table. Click the “Choose CSV file” button to browse and upload a file from your computer. Baserow allows you to configure the column separator, header and encoding while previewing the changes made by these configurations in real time.

Click the ‘Add table’ button to import. Your data will be immediately visible in your database.

enter image description here

For more detailed instructions, view this article on how to add a new table via import to a database.

Modify field type

When you import your data, you may notice that some fields are not properly categorized due to the lack of flexibility of spreadsheets.

From our Google Sheets → Baserow import, we have the following fields:

  • Name. The name of the department.
  • Description. A short description of the department.
  • Employee count. A field that counts the number of active employees under this department.
  • New hire count. A field that counts the number of new hires under this department.
  • Is active. A checkbox that indicates whether the department is active or not.

Baserow allows you to edit an existing field using the values from the imported data.

  1. Click on the dropdown icon next to the field you want to edit
  2. From the field customization menu, select ‘Edit field’
  3. Search and select a new field type from the dropdown.
  4. Click the ‘Save’ button when all desired changes are made

This will convert the values in your existing field to the new field format.

enter image description here

Option 2: Add new records from file import into an existing table

Now that we have the Departments in the table, we want to add additional data to an existing table in the database that has a list of job roles and their descriptions. The table is already populated with a set list of titles for a software development business with the following fields:

  • Name. The position name.
  • Level. A select field that indicates the level of the title in the organization.
  • Description. A description of the title, which may include position specifics and responsibilities.
  • Employee count. A formula field that counts the number of active employees with this title.
  • New hire count. A formula field that counts the number of new hires with this title.
  • Related employees. This field is linked to the Employees table, identifying all employees or staff members using this title.

To add new records to this table, click the ellipsis ••• beside the view at the top of the table. From the menu, click ‘Import file’ then select the file format of the data you want to import. In this instance, we will import an XML file. Alternatively, you can select CSV / JSON / Paste table data.

Select ‘Import an XML file’ then click the ‘Choose XML file’ button to browse and upload a file from your computer.

The columns of the Baserow fields will be automatically mapped to the correct fields in your table. You can change the mapping by selecting the desired target field from the dropdown menu. Any incompatible cell will remain empty after the import.

Tip 💡 Toggle between the Import Preview and File Content preview to compare the logic of which fields to merge and where.

enter image description here

For more detailed instructions on how to import additional data into an existing table, view this article on how to map fields.

Invite collaborators to the group

Give your team members access if you want them to have the resources they need to complete their tasks. You can invite collaborators to your group and assign permissions to grant them access.

You can choose a permission type when adding a new collaborator to a group. Anyone with Admin permissions can invite a new collaborator.

  1. Click ‘Invite Others’ in the sidebar
  2. Add the email of the collaborator.
  3. Set the user permission to customize what access the user has
  4. Add an optional message to appear when users join.
  5. Click ‘Send Invite’.

enter image description here

For more detailed instructions on working with collaborators, view this article.

Make data available to viewers publicly

Sharing a specific view from a table is useful for collaborating with people outside your organization who only need access to a few items in your database.

Once you’ve created a table, you can share the link with anyone. When sharing the view, you can control how others see what you share by using Filters or Hide fields.

Click the ‘Share view’ option of the supported view that you want to share, then create a private shareable link to the view. Share the view using the link or ‘Copy link’ icon.

enter image description here

For more detailed instructions on public sharing, view this article.

Voila 🎉! You have successfully imported your spreadsheet into Baserow.

release
November 3, 2022 by Bram Wiepjes
1.13 release of Baserow

Today we are announcing Baserow 1.13 with two new security features as a part of Baserow Enterprise: role-based access control and single sign-on.

info
May 21, 2020 by Bram Wiepjes
Best Airtable alternatives
info
July 14, 2022 by Hiram
The no-code landscape