7

I used MS SQL Server 2008 R2 (MS SQL) where I could right click the query result, copy/paste it with headers to Excel for easy exploration. Now with PG Admin (PostgreSQL) I have to do export (File > Export > CSV) then bunch of Excel steps (Text To Columns).

Is there an easy way to copy/paste the query result with headers into Excel?

vvvvv
  • 25,404
  • 19
  • 49
  • 81
user2051936
  • 71
  • 1
  • 1
  • 5
  • 1
    You can't copy + paste from a backend server. Are you talking about the MS-SQL management console compared to PG-admin? Or are you using one of the other client applications? Oh - and I was assuming this was on Windows, but then you mentioned /var/lib... – Richard Huxton Feb 07 '13 at 18:32
  • Creating client side exports (CSV, XLS, ...) is not really what the "database" does - be it PostgreSQL or SQL Server. It's always a client program which does the conversion. Which SQL client are you using? Are you looking for a different SQL tool which could do that? Or are you looking on how to do it with the tool you are using? –  Feb 07 '13 at 20:21
  • Sorry to leave out - I was using SQL Server Management Studio 2008 R2, and I am using PGAdmin III. – user2051936 Feb 07 '13 at 21:25
  • Yes, '/var/lib/postgres/myfile1.csv' is misleading. I am using Windows so it should be c: etc etc. I just meant that one way is to include code instead of using GUI. One way, you mentioned, is to click the corner to select all, copy-paste, but that is leaving out the headers. – user2051936 Feb 08 '13 at 22:15
  • I meant this one Copy (Select * From foo) To '/tmp/test.csv' With CSV; from http://stackoverflow.com/questions/1517635/save-postgres-sql-output-to-csv-file – user2051936 Feb 08 '13 at 22:26

5 Answers5

12

For pgAdmin 4, there is an option to "Copy with headers". It is a drop-down beside the copy button in the Query Tool menu:

Drop-down beside Copy button

quinz
  • 1,282
  • 4
  • 21
  • 33
Nico Mongcal
  • 121
  • 1
  • 2
  • Correct, tried it with Excel and it works, now the results are pasted with the header row. It should be the accepted answer. – Rafs Jul 20 '20 at 14:32
5

PgAdmin seems to make semi colon the default field separator. Excel seems to like tabs by default.

You could try and change excel or each time just do the "text to columns" feature.

I personally would go to Preferences->Query tool->Results grid and change the following

Result copy quote character: "
Result copy field separator: Tab
Copy column names: True

This will make it more behave more like sql management studio.

Phillip Fleischer
  • 1,023
  • 12
  • 14
0

There's a lot of different ways to accomplish what you want here. The question is a bit confusing because you are talking about Excel, but then you table about '/var/lib/postgres/myfile1.csv', which makes me think you are now using some favor of Linux.

I'm using Ubuntu 12.04 with pgAdminIII 1.16.0. And I have Open Office installed with LibreOffice 3.5.4.2 as the Excel replacement.

I'm not sure why you want to take the information out of the grid in pgAdminIII, but assuming just wanting to take the data and move it over to a spreadsheet to play it for some reason, then about the easiest way to do it is run your query and click the upper left corner of the results (which just like a spreadsheet selects everything) and copy. Then, you should be able to open LibreOffice and paste in the information. It will bring up the same dialog as you would see when importing a CSV file.

Also, you should be able to start psql and then do a "COPY" command. If you get a permissions error, then try the suggested "\COPY" instead. Please see the PostgreSQL docs. Here is a link to a wiki page here.

If I'm missing what you are trying to do, please ask questions in the comments section, and I'll try to improve my answer accordingly.

David S
  • 12,967
  • 12
  • 55
  • 93
0

You have to set your query tool output to text not the grid data. That way the Column names and the query results are all in the same cut past text file. When you do this you are no longer doing CSV. The whole results and field names comes over as a text file in the cut and paste process.

M T Head
  • 1,085
  • 9
  • 13
0

Answering to quite an old post: The answer by @Phillip Fleischer seems to be the best way, at least in pgAdmin III. But for pgAdmin III version 1.22.2 (the one I am using), instead of Preferences..., the settings mentioned were seen under File > Options > Query tool > Results grid.

kpk
  • 13
  • 5