How to Connect Excel to a Database

This article explains how to connect Excel to Database, step-by-step.

Trigger warning 1: the process is very technical.

Trigger warning 2: if you have millions of rows of data, you won't be able to load it to Excel. Because Excel has a row limit in place of 1,048,576 rows.

But let’s assume you’re not working with big data. 

(Are you? If you're dealing with millions of rows, it's best to load it inside Gigasheet, a big data cloud spreadsheet platform that supports over a billion rows.)

How to connect Excel to a database

There are five different ways to connect Excel to MySQL. I’ll walk you through the most efficient one using ODBC (Open Database Connectivity). 

Let’s get started:

Step 1: Install MySQL ODBC driver

Download MySQL ODBC driver:

• Go to the MySQL website.
• Download and install the MySQL ODBC driver for your operating system (Windows, macOS, Linux).

‍

Step 2: Configure ODBC data source

Open ODBC data source administrator:

• Now that you’ve installed the driver, open it. On Windows, go to Control Panel > Administrative Tools > ODBC Data Sources (ODBC). Or, simply search for it.

Add a new data source:

• Then, go to the “User DSN” tab and click “Add”.
• Select “MySQL ODBC 8.0 ANSI Driver” (or the Unicode driver if you prefer).

Configure connection details:

• Enter a name for the data source.
• Write the MySQL server details. 
• Finally, test the connection to ensure it’s working. Then, save the data source.

‍

‍

Step 3: Connect to Excel

Now, open Excel and go to the “Data” tab.

Select “Get Data”:

• If you have the newer versions of Excel (2016 and above), click on Get Data > From Other Sources > From ODBC.

• If you have the older versions of Excel (2013 and below), you’ll find this under Get External Data > From Other Sources > From Data Connection Wizard > ODBC DSN.

Choose the ODBC data source you just configured:

A navigator window pops up. It has the available tables and views. Select the ones you want to import into Excel. Lastly, click “Load” to import the data into Excel.

Step 4: Refresh data (Optional):

If you find some information missing, hit the refresh button on the left.

It’s complicated and time-consuming if you’re not a techie.

Each step involves multiple layers and dependencies. Even with the most efficient method, you still need technical configuration and ODBC drivers.

What’s even more disappointing is that Excel has a strict row limitation. If you try to load more than 1,048,576 rows, it will simply get cut off. 

To bypass this limitation, you can use Gigasheet instead.

Gigasheet is a big data spreadsheet that helps businesses upload, analyze, and collaborate on up to a billion rows of data. Think of it as a database disguised as a spreadsheet.

Why use Gigasheet as a spreadsheet?

This no-code platform is a database disguised as a spreadsheet, at a cloud scale. It’s quick to connect to MySQL with only 3 steps. The best part?  You can manage up to a billion rows, without worrying about data cutoffs or technical complexities.

Why Gigasheet: 

1. Handles a billion rows, without splitting data
2. Requires no setup, training, or coding skills. Even non-technical professionals can use it
3. Supports real-time collaboration

Quick comparison: Excel vs. Gigasheet

How to connect Gigasheet to a database

Connecting Gigasheet to MySQL is as easy as 1,2, 3. Follow these simple steps:

Step 1: Hover to data connectors

Log in to your Gigasheet account (if you don’t have one, sign up for free). Head over to New>Data Connectors.

‍

Step 2: Add MySQL connection

Select MySQL from the list.

‍

Step 3: Configure connection details

Add the MySQL credentials and the table you want to import to Gigasheet. Once you’re done, hit “Submit”.

• Host: the URL that will host the connection
• Port: the port to connect
• Database: the name of the database in MySQL.
• Username/Password: your username and password to login to MySQL
• Tables: name of the tables you would like to import to Gigasheet.

‍

Gigasheet organizes your data into a series of folders: main folder>sub-directories>multiple SQL directories>table.

In a few minutes, you’ll look at a screen like this 👇🏻. Voila, you’re done! 

‍

Overcome Excel’s Shortcomings with Gigasheet

Excel is a popular tool for data management. It’s ideal for SMBs that work with limited data. But the moment you scale, Excel struggles to keep up. Its performance slows down, and the screen freezes. 

That’s why you need a platform better suited to your growing needs. 

A spreadsheet (also a database) that scales as you scale. 

Sign up for a free account today!