how do I open a postgreSQL database with Excel/Access?

[RobH]

I have a postgreSQL backup dump file and I want to be able to extract data
from this using Excel or Access

 

[John Nurick]

Hi Rob,

The obvious thing to do is to get postgres running, import the file and
then connect Excel or Access to the database via an ODBC driver.

Otherwise it's a matter of munging (parts of) the file into a form that
can be read by software that you do have.

 

[John Nurick]

Rob,

According to the PostgreSQL FAQ at
http://www.postgresql.org/docs/faqs/FAQ.html :

Starting with version 8.0, PostgreSQL now runs natively on
Microsoft Windows NT-based operating systems like Win2000,
WinXP, and Win2003. A prepackaged installer is available at
http://pgfoundry.org/projects/pginstaller.

and there are ODBC drivers.

See also http://techdocs.postgresql.org/guides/MSAccess

Otherwise: the dump file is (I'm pretty sure) just a text file
containing a series of SQL commands which create the tables and
relationships and all and then append the data (which is contained in
the script). So if you enjoy munging text it's not a very big deal to

1) extract the "create table" statements, convert them into Jet SQL and
run them in Access to create the tables.

2) create the indexes, relationships, etc in the Access UI

3) parse the data out of the relevant parts of the file into text files
that Access can import.

I had a quick look on the web. There's Access VBA code at
http://docman.sourceforge.net/home_html/projects/sql/exportSQL3.txt
which unfortunately exports an Access database to a SQL script.

 

[RobH]

John, Thankyou. Very helpful

Rob,

According to the PostgreSQL FAQ at
http://www.postgresql.org/docs/faqs/FAQ.html :


and there are ODBC drivers.

See also http://techdocs.postgresql.org/guides/MSAccess

Otherwise: the dump file is (I'm pretty sure) just a text file
containing a series of SQL commands which create the tables and
relationships and all and then append the data (which is contained in
the script). So if you enjoy munging text it's not a very big deal to

1) extract the "create table" statements, convert them into Jet SQL and
run them in Access to create the tables.

2) create the indexes, relationships, etc in the Access UI

3) parse the data out of the relevant parts of the file into text files
that Access can import.

I had a quick look on the web. There's Access VBA code at
http://docman.sourceforge.net/home_html/projects/sql/exportSQL3.txt
which unfortunately exports an Access database to a SQL script.