22

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).

intgr
  • 19,834
  • 5
  • 59
  • 69
Daniel
  • 27,718
  • 20
  • 89
  • 133
  • 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 Apr 01 '11 at 05:31

3 Answers3

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.

StackExchange saddens dancek
  • 3,318
  • 1
  • 27
  • 37
  • +1 Good idea. I have the datasource installed already anyway, so this really should be the fastest way. – Daniel Apr 01 '11 at 06:31
  • 3
    +1 But I'd recommend using Access for importing data from Postres and then export from Access to Excel. – Tometzky Apr 01 '11 at 10:46
  • 1
    +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 :) – Daniel Apr 01 '11 at 14:50
  • @Tometzky: Good idea, I'll edit that into the answer (sorry that I get rep that sort of belongs to you) – StackExchange saddens dancek Apr 01 '11 at 17:06
  • I configured a Postgresql connection with oledb odbc bridge and the postgresql odbc driver and it works (MS Office 2007). At the end of configuration is also the chance to select the desired table or view. – OkieOth Nov 12 '12 at 14:01
  • If you are looking for a 64 bit solution, try http://code.google.com/p/visionmap/downloads/detail?name=setup_psqlODBC_x64_8.3.4.0.exe&can=2&q= – RyanN Feb 22 '13 at 20:53
  • 1
    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: http://www.stoneedge.com/help/mergedProjects/3rdparty/Creating_ODBC_Connections_in_Microsoft_Windows.htm – Albert Rothman Jul 19 '17 at 17:46
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

Flatliner DOA
  • 6,128
  • 4
  • 30
  • 39
  • 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): http://superuser.com/questions/950100/connect-to-postgresql-database-from-excel-2013-power-query-with-npgsql – Mehness Jan 01 '17 at 19:41
-4

This is fairly easy.

Make this the first line of .asp page: <%response.ContentType="application/vnd.ms-excel"%>

Make the rest like any other HTML page with a table in it.

Use ASP to fill the table with data from the database.

The server will create an Excel spreadsheet for you.

Ken
  • 1