Thread: importing data automatically

importing data automatically

From
"Menke, Kurt"
Date:

I am running PostgreSQL 7.3.2 on RedHat 8.  I have many large census tables I need to import.  I access the Linux box from my Windows 2000 machine via Cygwin and can access PostgreSQL similarly via EMS PostgreSQL Manager.  I understand that a table must be created with the correct fields before INSERT or COPY can be used, or before the data import wizard in EMS can be used.  My problem is that I have some very large tables with hundreds of fields to import.  Is there a way to get PostgeSQL to honor the database schema from an MS Access database and create the appropriate fields on import automagically?

 

Kurt

 

~~~~~~~~~~~~~~~~~~~~~~~~~

Kurt A. Menke

GIS Analyst/Programmer

 

Mailing Address 

Earth Data Analysis Center

MSC01 1110

1 University of New Mexico

Albuquerque, New Mexico  87131-0001

 

Street Address

University of New Mexico

Bandelier West, Room 123

Albuquerque, NM 87131

 

Phone:  (505) 277-3622 (ext. 239)

FAX:  (505) 277-3614

email:  kmenke@edac.unm.edu

 

Re: importing data automatically

From
Josh Berkus
Date:
Kurt,

> I am running PostgreSQL 7.3.2 on RedHat 8.  I have many large census tables
> I need to import.  I access the Linux box from my Windows 2000 machine via
> Cygwin and can access PostgreSQL similarly via EMS PostgreSQL Manager.  I
> understand that a table must be created with the correct fields before
> INSERT or COPY can be used, or before the data import wizard in EMS can be
> used.  My problem is that I have some very large tables with hundreds of
> fields to import.  Is there a way to get PostgeSQL to honor the database
> schema from an MS Access database and create the appropriate fields on
> import automagically?

Sure.  Use pgODBC to connect to the PostgreSQL database from MS Access.   Use
that connection to "export" the table design from MS Access to PostgreSQL
(but not the data, just the design!).

Log into PostgreSQL and adjust the data types and of the fields, becuase the
first step will not have worked perfectly.

Create COPY files and use those to load the new tables.

Create your indexes and FKs *after* import.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: importing data automatically

From
"Menke, Kurt"
Date:

I received the following solution :

Sure.  Use pgODBC to connect to the PostgreSQL database from MS Access.   Use
that connection to "export" the table design from MS Access to PostgreSQL
(but not the data, just the design!).

Log into PostgreSQL and adjust the data types and of the fields, becuase the
first step will not have worked perfectly.

Create COPY files and use those to load the new tables.

Create your indexes and FKs *after* import.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

…and I tried to add the PostgreSQL ODBC driver.  However, when trying to add the driver I received the following error: "The setup routines for PostgreSQL ODBC driver could not be loaded due to system error code 126", followed by: "Could not load the setup or translator library", both libraries are exactly where the path in the registry says they should be. I also tried updating my version of MDAC.  Any ideas on this?  It seems the driver may not like Window 2k.

 

Kurt

~~~~~~~~~~~~~~~~~~~~~~~~~

Kurt A. Menke

GIS Analyst/Programmer

 

Mailing Address 

Earth Data Analysis Center

MSC01 1110

1 University of New Mexico

Albuquerque, New Mexico  87131-0001

 

Street Address

University of New Mexico

Bandelier West, Room 123

Albuquerque, NM 87131

 

Phone:  (505) 277-3622 (ext. 239)

FAX:  (505) 277-3614

email:  kmenke@edac.unm.edu

 

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Menke, Kurt
Sent: Friday, October 03, 2003 8:56 AM
To: 'pgsql-novice@postgresql.org'
Subject: [NOVICE] importing data automatically

 

I am running PostgreSQL 7.3.2 on RedHat 8.  I have many large census tables I need to import.  I access the Linux box from my Windows 2000 machine via Cygwin and can access PostgreSQL similarly via EMS PostgreSQL Manager.  I understand that a table must be created with the correct fields before INSERT or COPY can be used, or before the data import wizard in EMS can be used.  My problem is that I have some very large tables with hundreds of fields to import.  Is there a way to get PostgeSQL to honor the database schema from an MS Access database and create the appropriate fields on import automagically?

 

Kurt

 

~~~~~~~~~~~~~~~~~~~~~~~~~

Kurt A. Menke

GIS Analyst/Programmer

 

Mailing Address 

Earth Data Analysis Center

MSC01 1110

1 University of New Mexico

Albuquerque, New Mexico  87131-0001

 

Street Address

University of New Mexico

Bandelier West, Room 123

Albuquerque, NM 87131

 

Phone:  (505) 277-3622 (ext. 239)

FAX:  (505) 277-3614

email:  kmenke@edac.unm.edu