Thread: 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
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
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