program to move data from MS Access to Postgresql

[dakota81]

I used to use pgadmin2 with their very helpful data migration wizard. Well, server's been upgraded and pgadmin2 no longer works with the newest version of postgresql. Pgadmin3 still does not have the equivalent function.

I've searched all around trying many different software, the only one that seems to work well is Navicat, but it's $130.

Are there any free or cheap easy to use software to copy an Access table into postgresql?

Thanks.

 

[gwinn7]

We routinely use Navicat here. Its well worth the money spent.

I don't know if any worth-while free tools for this.

One of the few "gotcha" frustrations is if you need to export a series of tables back to Access, there are two problems...

1. It only does ONE table per database (no multiples,no appends).
2. It only exports to ONE version of Access. If you have an older version, your are screwed.

Gary
gwinn7

 

[gwinn7]

RE: #2 Navicat Acces Export

Of course, you can use ODBC as an export option to Access instead.

Gary
gwinn7

 

[jgrogan]

Dakota81

Have a look at Talend Open Studio - it is a free, nice to use ETL tool (based on the Eclipse Platform) that runs on Windows and Linux (with GTK). You will need a Java Development Kit installed.

Jobs can be built to copy from Access to Postgres (and back again) with data transformations (if you need them). Many other DBs are also supported.

Created jobs can be run manually, under 'at' or 'cron', or as web services.

BRgds
JG

 

[dakota81]

Thanks. I had found Navicat, used the trial version as long as I could. Then I ended up paying for the full version, it works very well, and it suits me a lot better than the pgadmin plugin did.

 

[gwinn7]

Good news! Yeah, sometimes we have to just fork over some money to get what we need.

Gary
gwinn7

 

[stefanwagner]

You should be able to copy a msaccess-db to postgresql by binding your postgresql database to access - shouldn't you?

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[gwinn7]

If you mean linking Access on the front-end to the Postgres backend tables, then yeah that could work.

However, I have had problems when performing inserts exceeding 20,000 records at a time. For small scale stuff, that solution works good. Of course, you have to go through the trouble to build your own INSERT statements, which can be a pain with a lot of tables.

But, if you can't segment your data uploads to less than approx 20,000 records, you may have the same problem as I had. May be there is a newer version the ODBC driver that fixes this?

Navicat is, by far, way easier to do the import though.

Gary
gwinn7

 

[stefanwagner]

Well, I don't have access at all - I just remember, when I had to deal with it in 2001.

Sql in sql like this:

SELECT "INSERT INTO foo (a, b) VALUES (", a, ", ", b FROM foo;

should work for more than 20 000 rows pretty well too, and isn't of much fun too.

Importing from postgresqls psql interface with copy

\h copy

is another option.

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung