PostgreSQL Query to Excel Sheet

Question

I need to export some data from PostgreSQL to Excel (quick customer wish), and the last time Excel had serious problems opening or importing my COPYd csv files (line endings, utf-8 encoding, etc), and it took me an hour at best.

Does someone know a quick, elegant solution that generates a real Excel file? Like a small shell script or the like?

I want this to be done either on my Linux box (Debian 5.0 Lenny) or on Windows (XP or higher).

It was ME taking a hour getting Excel to open and import my CSV file, because at first the encoding was wrong, then multiline strings broke the import, etc. — Daniel
– Daniel, Commented Apr 1, 2011 at 5:31

StackExchange saddens dancek · Accepted Answer · 2011-04-01 17:08:40Z

18

You could install the PostgreSQL ODBC driver on the Windows machine, and then connect Excel to the database like explained in this blog post (except using ODBC rather than OLEDB).

I haven't tested this. I'm not really sure if this makes it any easier than exporting CSV and fixing it (given what Excel is like).

EDIT (thanks @Tometzky): The best solution is to use the PostgreSQL ODBC driver to connect Microsoft Access to the database and from Access export to Excel.

edited Apr 1, 2011 at 17:08

answered Apr 1, 2011 at 6:19

StackExchange saddens dancek

3,4121 gold badge29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Daniel Over a year ago

+1 Good idea. I have the datasource installed already anyway, so this really should be the fastest way.

Tometzky Over a year ago

+1 But I'd recommend using Access for importing data from Postres and then export from Access to Excel.

Daniel Over a year ago

+1 @Tometzky: This comment would be my accepted answer, because only access allowed me to select the tables from my ODBC driver. Excel could not find any table. I upvoted another answer of you in another question instead :)

StackExchange saddens dancek Over a year ago

@Tometzky: Good idea, I'll edit that into the answer (sorry that I get rep that sort of belongs to you)

Albert Rothman Over a year ago

This killed me for a while, so to future readers: You will first need to set up the ODBC connection. To set up the ODBC: Click on the Start Menu. Select Control Panel. Select Administrative Tools and double click the Data Sources (ODBC) icon. Click on the System DSN tab. More detailed article that I pulled the ODBC set-up text from: stoneedge.com/help/mergedProjects/3rdparty/…

|

Flatliner DOA · Accepted Answer · 2015-01-16 06:45:28Z

1

Apparently you can use Power Query Add-in and the .Net PostgreSQL Data Provider for importing PostgreSQL data into Excel 2010 or Excel 2013...

Download http://www.microsoft.com/en-au/download/details.aspx?id=39379

How to for using PostgreSQL: https://support.office.com/en-ie/article/Connect-to-a-PostgreSQL-database-bf941e52-066f-4911-a41f-2493c39e69e4

answered Jan 16, 2015 at 6:45

Flatliner DOA

6,3774 gold badges32 silver badges39 bronze badges

1 Comment

Mehness Over a year ago

Hi just fyi apparently this does not work for all versions of office (eg Student 2013) for a stupid drop menu bug - this post describes the issue - I think only works for Pro (worked for me on office 2010 pro): superuser.com/questions/950100/…

Collectives™ on Stack Overflow

PostgreSQL Query to Excel Sheet

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related