The 3 best ways to connect Excel to a Postgres database are 1) using the ODBC driver, 2) with Microsoft Query to pull data into a data table, pivot table, or pivot chart, and 3) using Power Pivot. The following post walks through each of the 3 methods for connecting a Postgres database to Microsoft Excel as well as how to install the Postgres ODBC driver, which is needed for all 3 connection options.
Table of Contents:
- Background
- What is Postgres?
- Benefits of connecting Postgres to Microsoft Excel
- Considerations when connecting Postgres to Microsoft Excel
- How to Install the PostgreSQL ODBC Driver - Needed for all 3 methods below
- 3 Popular Methods for connecting a Postgres database to Excel:
- Row Zero
- Conclusion
Background
Connecting a PostgreSQL database to Microsoft Excel can make it easy for business teams to pull data directly into a spreadsheet for analysis. The spreadsheet is often the preferred tool for building models and combining various data sets into one analysis. The following post covers the benefits and considerations and provides step-by-step instructions for connecting a Postgres database to Microsoft Excel.
What is Postgres?
Postgres, short for PostgreSQL, is an open source object-relational database management system (ORDBMS). It was developed at the University of California Berkeley in 1986. PostgreSQL is popular because it is open source (free) and runs on all major operating systems, including Linux, Unix, Mac OS, and Windows. It supports text, image, sound, and video files. Lastly, it has programming interfaces for many of the more popular languages, like C/C++, Java, Perl, Python, Ruby, and others. You can find all the details about PostgreSQL at postgresql.org.
Benefits of connecting Postgres to Microsoft Excel
- Production Data - Connecting Microsoft Excel to a Postgres database enables financial and accounting models to be built on production or operational data stored in the database.
- Connected Models - Once a Postgres database is connected to a spreadsheet, like Microsoft Excel, it is easy to re-run the data import to pull the latest dataset into the spreadsheet without having to recreate another static analysis.
Considerations when connecting a Postgres Database to Microsoft Excel
- Data Set Size - While Microsoft Excel is a great tool it cannot easily support large data sets. Microsoft Excel will often slow down or crash as data set sizes reach tens or hundreds of thousands rows Excel has a 1,048,576 row limit, which we've written about here. It is common for data sets stored in databases to be quite large. To import data from Postgres to Microsoft Excel, the postgres query will need to trim the data down to a size Microsoft Excel can manage. There are options within Microsoft Excel, like data model and power query, to enable work with large data sets but they don't allow users to see all the data or use the same spreadsheet flexibility Excel supports with smaller data sets. If the data set is large and a spreadsheet UI is the preferred way to analyze data, try Row Zero, a blazingly fast spreadsheet designed for big data.
- Sharing - Microsoft Excel is offered in two versions, desktop and Excel 365. Desktop Excel is the traditional version everyone thinks of. The program runs on local computers and is fast and performant. Desktop Excel is difficult to share due to the fact that it runs on users' local computers. It is common for teams to email around multiple different versions creating static one off analyses that need to be updated and versioned. The workbooks can get out of sync if two users make updates to the same version. Companies often rely on cloud storage repositories like Sharepoint to manage all the files with checkout and check-in logic that preserve spreadsheet integrity. Excel 365 is the new version of Excel that makes sharing easy, though it is not commonly used for big models or data analysis because it is slower than desktop Excel. If a fast powerful spreadsheet with the ability to share across team members is desired, Row Zero is powerful spreadsheet that runs in the cloud and can be shared with anyone
- Complexity - Connecting data sources to Microsoft Excel is not as easy and intuitive as one might think. There are often complications that can be hard to resolve. The information below does its best to help prevent errors that will inevitably occur and there is plenty of content online to help resolve each one.
Install the PostgreSQL ODBC Driver
The first step in connecting Microsoft Excel to a Postgres database is downloading and installing the PostgreSQL ODBC driver. The purpose of the PostgreSQL ODBC driver is to provide a standardized interface for applications to access Postgres databases regardless of the programming language or operating system used by the application.
1. Download the PostgreSQL ODBC Driver
In order to download the driver, visit https://odbc.postgresql.org, click on the link to go to the download site, click on 'msi' and then download the most recent version that matches the version of windows running on your computer (x32, x64, or x86). You can also click on the link here to go straight to the ODBC driver downloads page.
2. Install the PostgreSQL ODBC Driver
Once you've downloaded the correct drive version, unzip or decompress the file in your downloads folder. Inside, you will find an executable file type (e.g. psqlodbc_x64.msi). Double click on the executable to install the driver and follow the steps in the installation wizard. 
3. Configure and Add the PostgreSQL ODBC Driver in Windows
In Windows, go to the start menu and search for 'ODBC Data Sources.' Select and open the ODBC Data Source Administrator. 
Navigate to the 'Drivers' tab and look for your PostgreSQL driver in the list. If you don't see it, it has not been installed correctly and you should try repeating the previous step. Assuming your driver is visible in the list, click on the 'User DSN' or 'System DSN' tabs, click 'Add,' select the PostgreSQL driver from the list, and click 'Finish.' 
Next add the connection credentials for your postgreSQL database. You will need to match the settings on your database. For example, if the database allows encryption, you will need to toggle the 'SSL Mode' value to match it. Once credentials have been entered, click 'Test' to test the connection. If the Test is successful, click 'Save.' The PostgreSQL driver will now show up in either System DSN or User DSN lists. 
1. Import from Postgres to Excel using ODBC Driver
In Excel, click on Data > Get Data > From Other Sources > From ODBC. Then select the PostgreSQL option from the drop down menu and click 'ok.' Enter the user name and password and click 'connect.' 
Next navigate to the table you wish to import in the menu on the left. Excel will try to display a preview of the data but this can be slow to load. If you aren't familiar with Excel's performance issues, you can read more about them in Why is Excel slow. Once the preview loads or you are ready, click 'Load.' 
Depending on the size of your Postgres data set, loading the data might take some time. If your data set is bigger than the Excel row limit, Excel will alert you with an error message and only load the first 1,048,576 rows. There are other options in Excel for bigger data sets that are covered in subsequent sections. 
2. Postgres to Excel with Microsoft Query
In Excel, click Data > Get Data > From Other Sources > From Microsoft Query. Excel will then display a dialog box in which you can select the Postgres data source and click 'ok.' 
The Microsoft Query editor will then open and display a list of tables. Select the table you wish to query. Use the Microsoft Query editor to write a query or select the tables to be imported. Once the data is selected, click File > Return Data to Microsoft Excel to import the data to Excel. Then choose whether to import the data as a Table, Pivot Table, or Pivot Chart, and the location. 
3. Connect Postgres to Excel with Power Pivot
In Excel, Click on Power Pivot in the top line menu and then click 'Manage' in the upper left corner. Then click Get External Data > From Other Sources. When the Table Import Wizard Opens, select 'Others (OLEDB/ODBC) and hit 'Next.' 
When the Specify a Connection String window opens, click on 'Build.' Under the 'Provider' tab, select 'Microsoft OLE DB Provider for ODBC Drivers' and hit 'Next.' Then on the 'Connection' tab, select PostgreSQL from the drop down and login credentials. Test the connection before clicking 'OK.' 
Back on the Table Import Wizard, click 'Next,' choose the method of import by either selecting from a list of tables or writing a query. Then click 'Finish.' Select the method of import and data will be loaded into Excel.
Row Zero
Connect Postgres to a Row Zero Spreadsheet
Row Zero is a new spreadsheet designed for modern data analysis. It can process 100M+ rows of data, connect to any data source, and support real-time collaboration. To connect your own PostgreSQL database to Row Zero, check out our instructions here. Row Zero makes it much easier to get data from a postgres database into a spreadsheet and has more power than Excel to easily handle big data sets and complex analyses. To get started, watch the video below and connect your Postgres instance to Row Zero for free.
Conclusion
In this post we reviewed the 3 most common ways to import data from a Postgres database to Microsoft Excel. The main benefits are the ability to build models off of production data and to refresh the data import without having to re-build the model. The main issue to consider when importing any data into Excel is the data set size. Importing data straight into the spreadsheet will be limited to 1,048,576 rows and even with Power Pivot and Microsoft Query's abilities to handle bigger data sets, Excel will run slower, the bigger the data gets. Once the data is in, it is not easy to share analysis with team members with Excel. Excel 365, the web based version of Excel, has better sharing features but is even less powerful than desktop Excel. Additionally, Excel's big data features don't allow for the usual flexibility and complexity a spreadsheet affords because they limit interactions to data tables and pivot table type analysis. Row Zero is a better option for connecting a spreadsheet to a database and working with a big data set.
If you use another cloud data warehouse or blob storage, Row Zero also connects to Snowflake, Redshift, and S3.