Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.