A step-by-step guide to connect your spreadsheets to a database in the cloud

Spreadsheets are valuable for organizing and managing customer data, especially for small-scale businesses handling small datasets with few complex relationships.

As your data grows and becomes more complex, with more users needing to access it, managing data with spreadsheets becomes very inefficient. Also, observing and tracking changes made to a spreadsheet is more complicated, often resulting in multiple versions of your data.

Cloud-hosted databases enable better data management by offering a platform for accessing, managing, and organizing your data.

This article demonstrates how to connect popular spreadsheet tools, Microsoft Excel and Google Sheets, to cloud-hosted databases MariaDB, MySQL, and PostgreSQL to improve data management.

Prerequisites

To follow along with this tutorial, ensure you have the following:

• A populated Google Sheet. We’ve provided a sample sheet for you to use for this demonstration.
• Coefficient installed on Google Sheet.
• A Microsoft Excel workbook with the Devart plugin installed
• pgAdmin4 and MySQL Workbench installed. They are graphical interfaces for interacting with the database.

The essentials of spreadsheet and database integration

Cloud-hosted databases offer organizations a database as a service (DBaaS), allowing them to host, deploy, and manage databases while eliminating the time and resources needed to purchase, configure, and maintain hardware.

Some of these databases include:

• PostgreSQL — a robust open-source relational database known for its reliability, extensible features, and high performance. It supports integration with numerous tools and technologies, helping you build scalable applications.
• MySQL — a popular open-source relational database, offering users scalability, flexibility, and reliability for building SQL and NoSQL applications. It provides a high-performing, available database for powering business-critical applications at an economical cost.
• MariaDB—MariaDB is another open-source relational database that can handle large or small amounts of data, making it a dependable choice for most businesses. Although it has numerous similarities to MySQL, it is more scalable and has a faster querying speed, making it well-suited to performance-critical workloads.

Cloud-hosted databases ensure uninterrupted business operations through numerous features, such as automatic backups, version control, and disaster recovery. Additional benefits are:

• Scalability
• Flexibility
• Business agility
• Security
• Cost savings

Thanks to tools like Kinsta, you can set up PostgreSQL, MySQL, and MariaDB instances in minutes. Kinsta offers access to a cloud-hosted database system, which you can use to follow along with this tutorial risk-free and without charge.

Prepare and organize your spreadsheet data

Fresh spreadsheet data can contain errors, such as duplicate figures, noise, outliers, and other flaws, that decrease data quality and affect integration.

1. Prepare your data

Here are some ways to organize and prepare your data for database integration:

• Employ templates — Google Sheets and Excel contain many spreadsheet templates to help speed up your data formatting and organizing. Although finding a template that serves your business use case may feel tedious or challenging, using one sets you on the right course.
• Format your data — Formatting modifies your data to help you visualize and understand it. This process may involve splitting a single complex sheet into multiple sheets, sorting columns alphabetically or numerically in ascending or descending order to ease readability, or changing cell colors to indicate importance.
• Data cleaning — Data cleaning removes outliers, duplicated values, or special characters. It may also involve splitting a single text column into multiple columns to avoid parsing errors during integration or using conditional formatting to identify erroneous data.
• Hide unnecessary data — Sometimes, your data may contain information that’s not currently helpful but may be valuable later. Excel and Google Sheets provide features that help you hide this unnecessary data.

2. Structure your data for integration

When preparing spreadsheets for database integration, here are some best practices:

• Record metadata — Metadata provides essential details about your current data structure and its origin. Recording your metadata helps ensure accurate mapping of all data points for successful database integration.
• Represent null and zero values—Zero values differ from null values and affect your data quality. Accurately record your zero values when preparing data sheets for integration, as the database may interpret them as null values, which may cause constraint errors.
• Avoid special characters in field names — Introducing numbers, special characters, and other Unicode characters in your column names may cause parsing errors when importing data from spreadsheets. Best practices when naming fields include using camel case (for example, studentName) or underscores to make names more descriptive.

With your data structured, you’re ready to integrate it with a cloud database.

How to integrate with MariaDB: A step-by-step process

First, start by creating your MariaDB database with Kinsta. Next, this guide uses Coefficient — a no-code connector for importing spreadsheet data, to connect your database instance to Google Sheets. Be sure to install this connector.

Connect MySQL workbench to MariaDB

First, provide your MariaDB database instance with external connection details.

1. Open the External connections page and copy the External hostname, Username, Password, and Database name fields.

   

   Here, connect MySQL Workbench, which provides a graphical user interface to interact with the MariaDB instance. You connect MySQL Workbench to your database instance by adding a new connection.

2. On the Welcome to MySQL Workbench page, click MySQL Connection in the lower left corner.
3. On the Setup New Connection page, enter the external connection details provided by your MariaDB database instance.

   

4. Click Test Connection at the bottom of the page. A connection warning about an incompatible or nonstandard server version appears. Ignore the warning. You’ve now connected your database instance to MySQL Workbench.
5. Next, create a table named diabetes_table with columns using the following SQL statement.

   CREATE TABLE `diabetes_table` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `Pregnancies` varchar(45) NOT NULL,
     `Glucose` int(11) NOT NULL,
     `BloodPressure` int(11) NOT NULL,
     `BMI` decimal(3,1) NOT NULL,
     `DiabetesPedigreeFunction` decimal(4,3) NOT NULL,
     `Age` int(11) NOT NULL,
     `Outcome` tinyint(4) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `id_UNIQUE` (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3

Connect Google Sheets to MariaDB

1. Open Google Sheets.  The spreadsheet already contains a comma-separated values (CSV) file (diabetes.csv) with seven columns.

   

2. Click Extensions.

   

3. Go to Coefficient Salesforce, Hubspot Data Connector, and then click Launch.

   

   This step opens up the Coefficient connector on the right side of your sheet, which lets you import and export data between Google Sheets and the MariaDB database.

4. Click Export to in Coefficient, then click MySQL. Although you’re connecting to a MariaDB database, you click MySQL because MariaDB is a fork of MySQL. This means it’s a MySQL database with extra features.
5. Enter the connection details provided by your MariaDB instance and click Connect.

   

6. In the Source Data section, select diabetes from the Tab list and Row 1 from the Header row list.

   

7. In the Destination section, select Sheets-db diabetes_table from the Table list.
8. Select Insert from the Action list to insert the spreadsheet data.

   

   In the Schemas panel, you will see the spreadsheet columns.

   

9. Map the spreadsheet columns to the headings of the MariaDB table and click Save.

   

10. Select Specific rows on sheet and click Next.
11. Test the mapping by selecting row 12 and click Done selecting rows.

    

12. Confirm your selection by clicking Insert 1 row in MySQL.The spreadsheet now has a Record ID column, a Result column showing OK, and a Timestamp column showing the time of the export.

    

13. Click Done.
14. Now, select more rows to export. Click Insert X rows in MySQL and then Done.
15. Use this query to show imported data in the MariaDB table.

    SELECT * FROM <your_db_name>.diabetes_table;

    

Connect Excel sheets to MariaDB

Ensure you have the Devart plugin. This plugin lets you connect your Excel sheet to MariaDB, import and edit the data on Excel, and update the changes to your database. The plugin comes with a guide to help with installation.

1. Open a blank Excel sheet.
2. Click Devart on the top navigation bar. You see the Devart tab if you installed the plugin.

   

3. Click Get Data to open the Import Data Wizard.

   

4. Select MySQL database as the Data Source and enter your MariaDB database details to connect to it.

   

5. Click Test Connection. A “Successfully connected” message appears.
6. Click OK, then click Next.
7. Use the Visual Query Builder or a custom SQL query to import all the data from the diabetes table to the Excel sheet.

   

8. Click Finish. Now, you have an Excel sheet with data from the cloud-hosted database.

   

9. To edit and update this sheet and the database, click Edit Mode.

   

   If you choose not to save the password when setting up the connection, this prompts you to enter your database password.

10. Retest the connection to ensure you are still connected after entering your password.
11. Select two new records to add to the database.

    

12. Click Commit, then click OK to apply these changes and commit the changes to the MariaDB database.
13. Perform a query to see the updated database. You now have two new records.

    

Establishing a connection with PostgreSQL

Before connecting to and importing data from Google Sheets to your PostgreSQL database, you must establish a reliable connection to ensure a seamless data import process.

Create a PostgreSQL database on Kinsta and use the connection details to connect pgAdmin4, a graphical user interface (GUI).

As with the previous section, connect your database instance to Google Sheets using Coefficient.

Connect and import Google and Excel data to PostgreSQL

1. In the Register – Server dialog box, provide your PostgreSQL connection details. The details include:
   • Hostname/address
   • Port
   • Maintenance database
   • Username
   • Password

   

2. Create a sequence for your table ID values using the SQL statement below:

   CREATE SEQUENCE IF NOT EXISTS public.diabetes_table_id_seq
       INCREMENT 1
       START 1
       MINVALUE 1
       MAXVALUE 2147483647
       CACHE 1
       OWNED BY diabetes_table.id;

3. Now, create a PostgreSQL table named diabetes_table with columns whose data type and restraints match the spreadsheet table.

   CREATE TABLE IF NOT EXISTS public.diabetes_table
   (
       "Pregnancies" smallint NOT NULL,
       "BloodPressure" smallint NOT NULL,
       "BMI" numeric(3,1) NOT NULL,
       "Glucose" smallint NOT NULL,
       "DiabetesPedigree" numeric(4,3) NOT NULL,
       "Age" smallint NOT NULL,
       "Outcome" boolean,
       id integer NOT NULL DEFAULT nextval('diabetes_table_id_seq'::regclass),
       CONSTRAINT diabetes_table_pkey PRIMARY KEY (id)
   )
   WITH (
       OIDS = FALSE
   )
   TABLESPACE pg_default;

4. Open diabetes.csv in Google Sheets.
5. Click Extensions, go to Coefficient: Salesforce, Hubspot Data Connector, then click Launch.
6. Next, to export the spreadsheet data into the PostgreSQL database, click Export to.
7. Click Connect beside PostgreSQL.
8. Enter your PostgreSQL connection details and click Connect.

   

9. Define how you want to export your data by selecting diabetes from the Tab list and Row 1 from the Header row list.

   

10. Select public.diabetes_table from the Table list in the Destination section.
11. Select Insert from the Action list.

    

12. Map the sheet’s columns to your PostgreSQL table.
13. Select the second row and click Done selecting rows.
14. Confirm your selection by clicking Insert 1 row in PostgreSQL.The spreadsheet now has a Record ID column, a Result column showing OK, and a Timestamp column showing the time of the export.
15. Test your integration by exporting more rows.
16. Perform a query to view the recently imported data.

    SELECT * FROM diabetes_table;

    This query shows all the data in the diabetes table.

Connect and export Postgres data to Excel

First, you need your PostgreSQL connection details.

1. Open a blank Excel sheet and click Devart.
2. Click Get Data to open the Import Data Wizard.
3. Select PostgreSQL database from the list of data sources, and in the Import Data Wizard, enter the connection details to connect to your database.

   

4. Click Test Connection to check for a successful connection.
5. Select your object and query your database using the visual query. You can use the Visual Query Builder or write your own custom SQL query to query your database.

   

6. Click Finish. You now have an Excel sheet with data. Click Refresh to ensure your worksheet is up to date.

   

7. Click Yes to confirm.
8. Next, click Edit Mode to edit and update this sheet and the database.
9. Add a new record to the spreadsheet and click Commit to commit the change.

   

10. Now, perform a query to see the updated database. You can see that the database has a new record.

Summary

Cloud-hosted databases offer a collaborative workspace that allows you to store, access, establish, and manage dynamic relationships with data.

Using Kinsta, you can spin up PostgreSQL and MySQL database instances and use the connection details provided to connect to your spreadsheets. With this connection, you can create your database tables, map your spreadsheet fields to that of your cloud database, and begin exporting your data.

Get started with Kinsta to take advantage of the better management provided by cloud-hosted databases.

Do you still manage large amounts of data with spreadsheets? Share how you manage large data effectively in the comments below!

Jeremy Holcombe Kinsta

Senior Editor at Kinsta, WordPress Web Developer, and Content Writer. Outside of all things WordPress, I enjoy the beach, golf, and movies. I also have tall people problems.

• LinkedIn