How To Export a Database Table as a Spreadsheet

Exporting your database tables as a spreadsheet provides a great way to share your data with others and further edit or analyze that data using common Excel functions. You can easily export your database tables as a spreadsheet in MySQL or PostgresSQL with just a few simple commands.

Image for post
Image for post
Photo by Farzad Nazifi on Unsplash

Exporting With MySQL

To export your MySQL database table as a spreadsheet, you can use Transact-SQL (T-SQL) to insert your table into an OPENROWSET function to save the table as a spreadsheet. The easiest way of doing this involves inputting a set of code into a query editor type then executing it.

First input the following code into your query editor:

After inputting the code into the query editor simply execute the function and your database table will be exported into a spreadsheet file.

Once you execute the code, you may encounter the following error:

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

If you receive this error, it means your Ad Hoc Distributed Queries option is not enabled. To enable this option, input and execute the following SQL code within the query editor:

EXEC sp_configure ‘show advanced options’, 1RECONFIGUREEXEC sp_configure ‘Ad Hoc Distributed Queries’, 1RECONFIGURE

After executing the code, you will receive the following message confirming that the option was enabled:

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you receive this message, simply re-execute the initial code and your database table will be exported as a spreadsheet.

Exporting With PostgresSQL

To export your database table as a spreadsheet within PostgresSQL, you must know the name of the database table and decide on a name and location for the spreadsheet file. Once you have this information, simply input the following code to the command line within PostgresSQL with [Table Name] and [File Name] replaced with the name of your table and the name/location of the spreadsheet file:

This exports your database table as a .csv file which can be opened with any spreadsheet tool or text editor.

If you need any help exporting or linking your databases with your spreadsheets, then feel free to email us at info@lovespreadsheets.com!

Products and resources that magically simplify hard spreadsheet tasks. Check us out at www.lovespreadsheets.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store