How to transform any spreadsheet into a database

Every task, process, and system creates unique information. If you’ve been part of a multi-department organization, you might have pondered on merging this data to draw insights.

Trying to document intricate processes in spreadsheets is like using LEGO bricks to build a castle: doable, but rigid. Spreadsheets excel at displaying and calculating data, but when it gets too tangled, seeking alternatives becomes necessary.

Imagine if there was an approach to take care of linking, surveys, attachments, and more? It would ease up managing info, making storing, organizing, and managing your data easier.

The great news is that a more efficient way to handle this info does exist: through a database. In this tutorial, we’ll be using Baserow.

What we’ll 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’ll explore:

• Exporting spreadsheet data as CSV, XML, or JSON
• Creating a workspace for your project
• Three ways of importing data into your workspace
  • 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 the workspace
• 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 organize 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 organize and categorize 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’ll 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):

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 workspace

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

Baserow is organized into workspaces. Each workspace has users invited to become members. Users can use Baserow completely for free and be in a workspace 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 workspaces and databases, click the ‘+ Create workspace’ button. This button will also be visible on the sidebar.

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

Create a database

Next, we need to create a database in the workspace. 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 workspace:

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.

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’ll 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.

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.

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’ll 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.

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 workspace

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 workspace and assign permissions to grant them access.

You can choose a permission type when adding a new collaborator to a workspace. 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’.

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

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.

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

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

What’s your story? Tell us how you use Baserow