Re: Excel 97 Export to Postgres

On Tuesday 26 August 2003 09:10 am, Raymond wrote:
> Need to export an existing M$ Access 97 database to Postgres.
>
> Tuples must be filtered as I am using sequences and other constraints.
>
> Help from anyone that has experience with this or know of 3rd party
> automation tools would be greatly appreciated.
>
> Raymond
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

My suggestion:

If you're migrating to an empty PostgreSQL database:
1. Create the constraints in MS Access. If the process errors out, find and
fix the violations.
2. Link the target tables in PostgreSQL into the Access database via ODBC.
5. Run an append query to copy the Access data to PostgreSQL.

If you're moving data into an existing PostgreSQL database:
1. Link the target tables in PostgreSQL into the Access database via ODBC.
2. Create a "conflict" char field in the Access database tables.
3. Run an update query in Access to update the conflict fields with a "1"
where the record in Access matches a PostgreSQL record in a way that violates
the existing constraints.
4. Fix the conflicts in the Access tables.
5. Run an append query to copy the Access data to PostgreSQL.

I don't think you need anymore automation than this. Unless you are willing
to lose data in the migration, there is no way to get around finding and
fixing constraint violations.

Best of luck,

Andrew Gould