- WHAT YOU’LL LEARN: Everything you need to know (and nothing you don’t) on how to export SQL data to Excel and update the export automatically (with any SQL database: MySQL, Microsoft SQL Server, PostgreSQL).🤓
- YOUR TAKE-AWAY: Save hours of doing the exact same process over and over again.⌚
- WHO IS THIS TUTORIAL FOR? Anyone spending way too much time copy-pasting data from a SQL database into Excel.🤖 BONUS: Save your cheat sheet at the end for future reference.📄

‍

How To Export SQL Data to Excel and Update the Export Automatically?

It's a 4-steps process:

1. Connect Excel to an external data source: your SQL database‍
2. Import your SQL data into Excel
3. Create a Pivot Table with an external SQL data source
4. Automate Your SQL Data Update In Excel With The GETPIVOTDATA Function

Let's get to the details on how to implement those step in this tutorial to save you hours in the future!

‍

‍

Connect Excel to an external data source: your SQL database

If you work on a Mac and you experienced difficulties connecting your SQL database, I’d like to tell you that I share your pain. I’ve been through it too… only to realize that there is kind of a bug on Excel for Mac 🤦‍♀️ (to be precise, I work on Microsoft Excel for Mac version 16.33). The good news is: I have the solution to your problem!

If you want to learn how to connect Excel to your SQL database with a Mac in order to query data from your SQL database into Excel, read the following lines.

#1 Install An ODBC

You need an ODBC (Open DataBase Connector) to load data from a SQL database to Excel. In order to do so on Mac, you need to install an interface to manage ODBC configuration files. I’ve used ODBC Manager for instance. ODBC Manager is a Mac OS utility application that configures ODBC data sources such as Microsoft Excel. Mac OS used to include an ODBC Administrator utility but Apple stopped including the ODBC Administrator with Mac OS starting with the release of Mac OS X "Snow Leopard". ODBC Manager was developed as a replacement for ODBC Administrator.

#2 Install A Driver

You have to install a driver for your SQL database. For instance, I’m using MySQL so I installed the MySQL driver for Mac. You can download here the Microsoft ODBC Driver for SQL Server. Click here for the official PostgreSQL ODBC Driver.

#3 Avoid A Common Error

Here might be the part where you struggled if you tried it by yourself. At least I did. To make it work, you need to move the SQL driver folder from /usr/local/ to /Library/ODBC/.

Why? Because the driver gets downloaded to /usr/local but Excel Microsoft Office is a sandboxed application, so it doesn't have access to /usr/local. Therefore, Excel doesn't know how to fetch the config file in the right place.

How did I figure this out? It happened after having wasted tons of time, growing increasingly frustrated and feeling totally stuck with the following error message:

‍

If you got that one too, you know what I’m talking about! Help came from Stack verflow 🙏. I would never have guessed it and discover that many other people spent half a day looking for a solution. So here is the explanation for you to save time and move your MySQL ODBC driver to /Library/ODBC/:

1. Copy the driver's whole directory /mysql-connector-odbc-8.0.12-macos10.13-x86-64bit to /Library/ODBC
2. Update the file odbc.ini, also located in /Library/ODBC/, with the driver's new location.

#4 Create a DSN

You have to create a DSN (Data Source Name) config using the SQL driver in ODBC Manager and fill in the database connection information.

To configure a Connector/ODBC DSN on macOS, you can either:

• Use the command-line utility (myodbc-installer)
• Edit the odbc.ini file within the Library/ODBC directory of the user
• Use the ODBC Administrator GUI
• NB: The ODBC Administrator is included in OS X v10.5 and earlier but users of later versions of OS X and macOS need to download and install it manually

To create a DSN using the myodbc-installer utility, you only need to specify the DSN type and the DSN connection string.

For example:

• With Connector/ODBC 8.0:

            shell> myodbc-installer -a -s -t"DSN=mydb;DRIVER=MySQL ODBC 8.0 Driver;SERVER=mysql;USER=username;PASSWORD=pass"

• With Connector/ODBC 5.3:

            shell> myodbc-installer -a -s -t"DSN=mydb;DRIVER=MySQL ODBC 5.3 Driver;SERVER=mysql;USER=username;PASSWORD=pass"

To use ODBC Administrator:

1. Before you start, make sure you are well set up:

• For correct operation of ODBC Administrator, ensure that the /Library/ODBC/odbc.ini file used to set up ODBC connectivity and DSNs are writable by the admin group. If this file is not writable by this group, then the ODBC Administrator may fail, or may appear to work but not generate the correct entry.
• Some issues with the macOS ODBC Administrator and Connector/ODBC may prevent you from creating a DSN using this method. In that case, use the command line or edit the odbc.ini file directly. Existing DSNs or those that you created using the myodbc-installer tool can still be checked and edited using ODBC Administrator.
  

2. Then, in the Applications folder, open the Utilities folder and click on the ODBC Administrator:

3. This will open your ODBC Administrator dialog box. Here is my ODBC Administrator dialog with the System DSN tab open. As you can see, I’m working with MySQL but you can as well work with Microsoft SQL Server or PostgreSQL. This tab includes Add, Remove, and Configure options. Additional tabs are User DSN, Drivers, Tracing and Connection Pooling:

4. From the ODBC Administrator dialog box, choose either the User DSN or System DSN tab and click Add. It will open a dialog box asking you to choose a driver, here is how it looks like:

5. Select the Connector/ODBC driver and click OK:

6. You will be presented with the Data Source Name (DSN) dialog box. Here is my ODBC Administrator Data Source Name dialog box:

7. Enter the Data Source Name. Here, I named it “Sample”: 

8. Enter the optional Description for the DSN. Here, I named it “Connection to sample my Sample database”. You can skip this step if you want:

9. Click Add to add a new keyword/value pair to the panel. You need to configure at least four pairs to specify the server, username, password and database connection parameters (further resources here on Connector/ODBC Connection Parameters):

10. Here will appear a first pair, set by default to “Keyword” and “Value”:

11. To modify its value, simply double click on “Keyword” to change it:

12. Here I want my first pair to be server=mysql so I replace “Keyword” by “server”:

13. And then double click on “Value”:

14. I replace here “Value” by ”mysql” and I click Add:

15. Another line set with “Keyword” and “Value” as default values will appear. I repeat the same process to setup my second keyword/value pair: user=sakila, click Add ; I continue with my third keyword/value pair: password=Sample, click Add ; and I finish with my fourth and last keyword/value pair: database=test_world, and finally click OK:

16. Hereunder is how my completed DSN configuration looks like with Data Source Name, Description and key=value pairs:

17. Click OK to add the DSN to the list of configured data source names and you’ll get a new line with you new DSN:

‍

‍

Import your SQL data into Excel

#1 Click Where You Want Your Pivot Table

Now that you connected Excel to your SQL database, you’ll be able to query data from your SQL database directly into Excel using the pivot table feature, without needing to copy-paste it. Position yourself on a new worksheet in the cell where you want to insert your pivot table. We clicked on cell A1 here:

Let’s zoom in:

#2 Click Insert

#3 Click Pivot Table

A Create Pivot Table dialog box will open:

Let’s zoom in:

#4 Click Use an external data source, then Choose Connection

This will open the iODBC Data Source Chooser dialog box on the User DSN tab.

#5 Click on the System DSN tab

You will see the list of your System Data Sources:

#6 Select the DSN created in ODBC Manager

If needed, remember you can refer to the first part of this tutorial entitled “Connect Excel to an external data source: your SQL database”.

#7 Fill the requested username and password

#8 Avoid a Common Error

It’s quite common to get an error message at this point so, if it happens to you, no worries, we’ll cover it (otherwise, you rock, congrats for getting it to work on the first try 👏). Here is the error message I had for instance > “Alert [MySQL][ODBC 8.0(a) Driver]Unknown MySQL server host ‘Value’ (0)”:

This means that my host value had a wrong configuration. So I checked my dialog box and, indeed, it appeared that I had been forgetful when configuring my first keyword/value pair to the Data Source Name dialog box: I forgot to change the default “Value”🙃:

So I quickly corrected this oversight and clicked OK.

#9 Access The Microsoft Query Dialog Box

When Excel correctly connects to the MySQL server, the Microsoft Query dialog box opens:

Let’s have a closer look at the box here:

#10 Click on the arrow to see the list of tables in your database

‍

#11 Select the table you want to query data from your SQL database into Excel

Only one table at a time should be selected in the SQL Statement. Once selected, the following query will automatically be created: “SELECT * FROM [table_name] ;”. If you know about SQL, you can also write your own custom SQL query here.

#12 Click on Return Data when you’re done with your selection

You’ll be brought back on the Create Pivot Table dialog box. Click on the OK button to get back to the pivot table:

‍

‍

Create a Pivot Table with an external SQL data source

#1 Discover Your Pivot Table Fields

At the end of the second part of this tutorial where you imported your SQL data into Excel, you get an empty pivot table with Pivot Table Fields on the right hand-side so that you can show the fields you want and rearrange them to create your own layout:

Here is a closer look at my PivotTable Fields, coming from my SQL database:

#2 Drag A Field To The Rows Area

In order to build your pivot table, drag a field from the field section to an area in the areas section underneath, Columns, Rows or Values.

Here, I’d like to create a pivot table to show the Quarterly Revenue per Product. It means that I will need the list of products as rows, the quarters as columns and the revenues as values.

So I first dragged Product from the field section to the Rows area:

#3 Drag A Field To The Columns Area

Then, the Quarter from the field section to the Columns area:

#4 Drag A Field To The Values Area

Finally, the Revenue from the field section to the Values area:

Here is a closer look:

Congrats on building your pivot table! 🎉

One question though: would you share the table as is? As for me, I deeply thank Microsoft engineers for having come with such a powerful tool for turning thousands of records of detailed data into a concise summary in a few clicks. But sorry folks, I don't share the same aesthetic tastes.🙈

Hereafter, I’ll show you how to transform this powerful-but-ugly pivot table and then use the GetPivotData function to populate a great-looking dashboard.

‍

Automate Your SQL Data Update In Excel With The GETPIVOTDATA Function

You’re in the right place if you want to learn how to update your great-looking dashboard automatically.💪

After this tutorial, never again you will have to painfully recreate pivot tables every week only to get results that you can format in a beautiful way.

Now, when new data will be added to your data set, you’ll refresh the pivot table, and your great-looking dashboard will automatically get new data from the pivot table, with a perfectly formatted report, ready to share and print.🤟

#1 Build Your Final Dashboard Structure

On a new worksheet, build your perfectly-looking report with no value inside.

Here is my initial pivot table:

And here is how I would rather present my data to my team:

#2.1 Try A Dashboard With Breakable Formulas

What I initially thought I would do (and it would work as long as I didn't change anything) is that I would just hit an equal sign and then come over to the pivot table, left click in cell, hit enter:

And there you go, you have the dollar value that matches here:

Then, I could take my mouse, go into the lower right hand corner of the cell that I’ve selected until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:

If that's what I wanted, and I’d never change this pivot table, then I’d be fine, and just leave it like that. But, because this pivot table is dynamic, it’s going to break these formulas.🙀

#2.2 Build a Robust Dashboard That Will Automatically Update

Luckily, we don’t have to worry as there’s a way to get around these broken formulas, and it’s precisely what we’re going to explain here. You’ll learn how to specify the value you want to return from the pivot table using the function GETPIVOTDATA.😍

I have to admit I was a bit puzzled by the function syntax the first time I was trying to determine the arguments I should use. FYI, here is the official syntax:

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

One solution you could use, which is the easiest one but that I do NOT recommend to implement as it ends up taking way too much time, is to type =GETPIVOTDATA and click on the cell you want to retrieve data from. You will get the correct data but it won’t let you reuse the formula for other cell, forcing you to manually enter each cell data. Here the formula Excel automatically proposed to me when I typed =GETPIVOTDATA and selected the corresponding cell in the pivot table:

=GETPIVOTDATA("revenue",Sheet1!$A$1,"product","Career Transition Coaching","quarter","Q1")

The problem here is that, if I take my mouse, go into the lower right hand corner of the cell that I’ve selected until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:

Mmm… not very interesting. Why is that? Because I didn’t lock the field and item that are used by the function.

I had to go through a bunch of elliptical tutorials and felt no one was really clear. So I’d like to make it easy for you, and I believe that a graph is definitely the most simple way to understand how to write you GETPIVOTDATA function. Here is how:

So here is the syntax detailed description for you to fully grasp it:

The function syntax by the book:

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

The function used in our example, where it returns the quarterly revenue per product from a pivot table:

GETPIVOTDATA("revenue",Sheet1!$A$1,"product",$A3,"quarter",B$2)

The GETPIVOTDATA function syntax has the following arguments:

‍

Now that we wrote the function in a way that will allow us to work faster, let’s click on the cell with the GETPIVOTDATA function, go into the lower right hand corner of the cell until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:

Yes! That’s what I wanted! 🥳. I do the same dragging it towards the right and I’m done:

Does your GETPIVOTDATA return the #REF! error value?

It might be for one of these reasons:

• The pivot_table argument is not a range in which a PivotTable is found.
• The arguments do not describe a visible field.
• The arguments include a report filter in which the filtered data is not displayed.

You can add a chart if you like:

#3 Refresh Your SQL to Excel Dashboard In 1 Click

Now, let’s test the automation of our table and ensure it updates if we change the data in our SQL database. So here is the initial data of my pivot table and the associated great-looking dashboard and chart:

Now, in my SQL database, I’ll boost the “Confidence Coaching” revenue in Q4 to $10,200. Let’s see how easy it is to automatically update the data in Excel. I click on Refresh all:

And boom, everything is refreshed in one click 😎:

You can keep your dashboard data up to date by refreshing the link to its source. Each time you refresh the connection, you see the most recent data, including anything that's new or has been deleted.

‍

Your Cheat Sheet

Save your cheat sheet for future reference: bookmark it, download it and share it with others who might also need it.

‍

Last thoughts: try this new tool

‍

The reason you want to push MySQL data to Excel is probably because you want to make this data accessible to team members who are confortable in a spreadsheet but maybe not confortable with SQL or with a Business Intelligence tool.

‍

If that's the case, you should definitely try out Actiondesk. Actiondesk is the easiest way for startup teams to make better decisions with their data. You can access your company key data without code or writing SQL, you'll just need basic spreadsheet skills.

‍

Actiondesk looks like a spreadsheet and works like a spreadsheet. Users used to Google Sheets and Excel will be able to learn Actiondesk very fast.

‍

If you're looking for a more solid solution, check out Actiondesk, you can try it for free.

‍