I'm developing a Django based website with a Postgres DB. The problem I'm having is importing the data from an Access DB into it. To be specific, I'm not trying to translate/import the schema from Access into Postgres flavoured SQL — as far as I've gathered there are lots of tools to do that, and it seems more or less straightforward. I'm trying to import data from just a number of tables and, hopefully, their relationships.
Basically, my problem is that I'm not sure at all what would be the most painless way of doing this. The best idea I could come up with so far is dumping the database from Access into a Postgres compatible SQL, massaging the SQL to get it in line with the schema in the new DB and then loading that. But this sounds like too much yak shaving. Is there a better way to do this?
On the Postgres side of things everything is still in development stage, so the DB will be empty, I'm not at risk of loosing data from it. I should note for completeness sake that I'm using South for migrations in Django and there are a few migrations done (not sure if this can complicate things). The Access DB is Access 2003 and Postgres is 9.1.
I'll be happy to give more details if needed. Thanks!
Best Answer
I'd tend to export each table as CSV from Access, something it's quite good at. Then import it into PostgreSQL using
COPY, possibly via a preprocessor like a Python script with psycopg2 if there's lots of mangled / invalid data.If there are lots of tables, VBA (Visual Basic for Applications) or similar can be used on the Access side to automate the export, and your scripting language of choice can automate the import.
Alternately, you might be able to find an ETL tool that can connect to access and load into PostgreSQL directly. See if Pentaho Kettle, Talend Studio, CloverETL, etc can do it - possibly over ODBC if nothing else.