Want to move MySQL data into Excel so you can use pivot tables, filter, chart, and leverage other useful features of Excel, connecting mysql and MS Excel makes a lot of sense.
This way you can avoid writing and modifying complex sql statements, or users who can’t write complex sql statements can instead use the power of Microsoft Excel. Excel lets many users look at and analyze data using tools they’re comfortable with.
- How to connect to a MySQL data base inside Excel
- Common errors when connecting
- Basic SQL queries for beginners
- Syncing and refreshing your Excel mysql integration
- SyncWith is a free Excel App
- SyncWith allows you to move data from any API into Excel including databases like MySQL - check out the 1000s of api integrations we support
- ⭐️⭐️⭐️⭐️⭐️ Reviewed by hundreds of businesses
- We’re the highest rated API add on with over 100,000 installs across Excel, PowerBI, AirTable, Data Studio and Google sheets. Install today and get your data into Excel in minutes.
To connect MySQL to Excel we’ll be using the SyncWith addon (available for free in Microsoft’s AppSource). SyncWith makes it super easy to connect 1000s of APIs and databases to Excel. It’s trusted by over 100,000 businesses and it’s easy to use.
Once you have the SyncWith app installed you can search for
MySQLand you’ll get an option to use our
You can also connect to other databases including:
- Postgres to Excel
- Notion database to Excel
Connecting to a MySQL database is super easy but if you’ve never connected to a database before and your dev team is opening up the resource to you to use in Excel it’s worth an overview of what the connection requires.
- Give the connection a name - for instance if you’re connecting to your users table to do some analysis of your users in Excel you might call your connection
Users- because syncwith supports multiple connections this helps you distinguish between each connector. For instance you might have a users table as well as leads table and you could pull both of them into Excel for analysis
- Input your username and password - You’ll probably need to talk to your IT / Devops / Database Administrator to get a username and password with read permissions to the database and table you want to access
- Host is the location of the database - if your host is on your local machine SyncWith cannot connect to it currently it
needs to be accessible to the internetso you would need to punch through a port or your router and map it to your machine (which in a corporate setting isn’t likely feasible)
- Configure your Port which for MySQL is typically 25060
- Finally you’ll need to provide the database you’re connecting to, it’s possible you’ll want to connect to multiple databases in your excel spreadsheet so this allows each connection your create to work with a specific database.
PRO TIP- You can use the connection string to enter in all the above information in one shot. Often you can simply ask your database administrator or dev team for a connection string and simply enter it here. It leaves less room for error when connecting your MySQL database to Excel.
Connection strings will look like:
Once you’ve entered your connection parameters you can test the connection by hitting the
Test connectionbutton and SyncWith will make sure we can connect to your database and that the credentials provided are correct.
Incorrect Username- MySQL returned an error: Access denied for user 'doadmi'@IP Address (using password: YES) (ER_ACCESS_DENIED_ERROR)
- Showing access for user
doadmiis denied because the proper username in this example is
Incorrect Password- MySQL returned an error: Access denied for user 'doadmin'@'IP' (using password: YES) (ER_ACCESS_DENIED_ERROR)
You may get the message
Timeout trying to connect, please ensure network security is not blocking the connection (ETIMEDOUT)- this indicates that while trying to connect we timeout - meaning we kept trying and could not connect to the host. This could be happening for a few reasons:
Security PermissionsThe most common reason is that the security settings on your host need to be updated to allow SyncWith to connect.
- Typically databases will specify trusted sources - a list of ip addresses or ranges that are allowed to access the database. In the addon you’ll see the SyncWith Static IP Addresses you’ll need to register
- As an example if your database is hosted in Digital Ocean you would add these two IPs to your trusted sources:
Incorrect PortBy default most MySQL instillations will use port 25060 but that could be different for your server or you may have accidentally deleted or missentered the port
If you get the message
MySQL returned an error: Unknown database 'defaultd' (ER_BAD_DB_ERROR)then you’ve specified a database that doesn’t exist. In this example the proper name of the database is
defaultdentered by accident
Now that you’ve successfully connected to your database it’s time to write a simple SQL query to get MySQL data into Excel. If you’re going to be using Excel to filter your database data then we just really need to get all the data from a table. If we have a table called
Userswe would do that as follows:
Select * from "Users"
If your Users table is relatively small relative to the limits of Excel - eg maybe Users is 5000 rows big with 20 Columns - 100k cells in total - this is completely fine for Excel - dump the data in as is and use Pivot Tables, Filter Views and Slicers to play with the data.
But some data sets are quite large and it isn’t reasonable to get all the records from all time into a Excel from MySQL. In this instance you likely want to filter.
Many tables have record created at timestamps, typically these are columns like
CreatedAt. For big datasets where you only want to say look at Users created in the last year we can change the query. As an example lets find users created after Sept 1, 2022.
select * from Users where "createdAt" >= '2022-09-01 00:00:00'
Maybe you just want to get the last 1000 or 10,000 records and you’re more concerned with getting as much in as possible for Excel to analyze, you know you can strip out old records. To limit the number of records coming back we can use the
limitoperator in SQL.
select * from Users limit 5000
Now we’ll only get 5000 users, BUT WAIT - which 5000 users will we get, we want to set an ordering, because maybe we want the 5,000 most recent users, for that we’ll use the
select * from Users order by "createdAt" desc limit 5000
This get the users in descending order, hence the
order by "createdAt"
descwe’re sorting the list of users in descending order based on their createdAt timestamp, aka newest users first. If you want the oldest users first you’d change it to ascending order,
order by "createdAt"
By filtering, ordering and limiting your query you can dump in the data that is important to you from a MySQL database table into Excel and then use Exel to do more advanced filtering and analysis - perfect for folks not skilled with SQL
Now that we have the data we want coming in we can refresh it any time we want. SyncWith allows you to have multiple connectors, for instance you could have 2 google analytics connectors pulling in website stats, search console pulling in SEO rankings, shopify pulling in your most recent sales and mysql pulling in your custom user data.
You can edit any of your connections with the context menu using the
You can refresh the data by hitting the