This afternoon I was using PGAdmin and needed to export some data from a query I had run but realized I wasn’t even sure if PGAdmin had this capability. Typically I would export data using the PSQL command line but since I was already working in PGAdmin I wanted to try and figure out if the capability existed and if so how to do it. After some playing around I was able to figure out how to export PGAdmin data to a CSV (Comma Separated Value) format that would be Microsoft Excel friendly. Follow the directions below to export PGAdmin data to a .CSV file to open in MS Excel.
Export PGAdmin Data To CSV And Open In Microsoft Excel:
- Launch PGAdmin & Connect To Database: First launch PGAdmin by your preferred method. After opening PGAdmin connect to the database you wish to export CSV reports from. Once connected the PGAdmin interface will look similar to the below example image.
- SQL Query Window: Once connected to the database server and database you want to export data from click the SQL button right below the top navigation bar which will launch the SQL Query window as shown below.
- Execute SQL Query: Now enter the SQL query into the top left box of the Query window, select Query from the top navigation bar, and click Execute from the drop down as shown in the example image below.
After executing the query the data will display in the bottom box of the Query window as displayed in the example image below.
- Export PGAdmin Data To CSV File: Now with the data displayed in the PGAdmin Query window you can export the data to a CSV file. Select File from the top navigation window and click on Execute from the drop down as shown in the example image below.
Once you click Export the below data export configuration window will display.
Make sure the settings are the same as the above example image so the exported data is opened in a friendly format in Microsoft Excel. These settings include Row Separator set to CR/LF, Encoding set to Local Charset, Column Separator set to “,”, Column Names included with a check mark, and Quoting set to “no quoting”. Browse to the location you want the file saved and click the OK button to export the PGAdmin data. Once the data has exported successfully the below message window will display.
- Open CSV In Microsoft Excel: Now locate the file where the PGAdmin data was exported and open the file using Microsoft Excel to verify the data is displayed properly. The settings we modified should separate each column and row properly in MS Excel as shown in the below example image.
The above steps allow you to use PGAdmin to export data to a CSV file that is easily opened in Microsoft Excel.