Converting Access .mdb to postgres

On 8/14/19 10:09 AM, Rich Shepard wrote:

I have the need to convert a flat-file Access database to postgres. I've
exported the .mdb tables using mdbtools. There's an accompanying metadata
PDF with column names and data types for each of the 84 tables, but no
description of the tables or column headings. I've asked the agency to
provide that information ... if they have it. No table has primary or
referential keys and, of course, there's no E-R diagram of the schema.

So you have the tables in Postgres, correct?

If anyone's done this I'd appreciate learning from your experiences. And I
assume there are no tools to automate all or part of the process so it must
be done manually.

I did something similar with the USDA Nutrient database(with notion of
making it a test dataset):
https://ndb.nal.usda.gov/ndb/doc/index

Their PDF:
https://www.ars.usda.gov/ARSUserFiles/80400525/Data/SR-Legacy/SR-Legacy_Doc.pdf

contained table and field descriptions as well as relationships.
Unfortunately the relationship information was not really accurate.
Access allows you to define 'virtual' relationships that are not really
enforced as I found out when looking at the data. Good luck on getting
information, my experience is agencies(I have also dealt with USFWS) do
not respond to requests about their data. If you do get the information
I guessing you will have to create your own method of dealing with it.
Don't count on the data being consistent.

Among the tables are many lookup tables. I don't know whether to leave them
as tables or apply a different structure to them.

Advice, suggestions, and recommendations are all welcome.

TIA,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 14 Aug 2019, Adrian Klaver wrote:

So you have the tables in Postgres, correct?

Adrian,

Not yet. I have the schema extracted using mdb-schema.

I did something similar with the USDA Nutrient database(with notion of
making it a test dataset): https://ndb.nal.usda.gov/ndb/doc/index

This is from streamnet.org. They're part of the Pacific States Marine
Fisheries Commission and it's a database of all fish-related information
within the Columbia River basin.

Access allows you to define 'virtual' relationships that are not really
enforced as I found out when looking at the data. Good luck on getting
information, my experience is agencies(I have also dealt with USFWS) do
not respond to requests about their data. If you do get the information I
guessing you will have to create your own method of dealing with it. Don't
count on the data being consistent.

At the federal level cooperation is spotty. Several years ago I downloaded
the International Taxonomic Identification System (ITIS) database (the
internationally accepted list of names -- scientific and common -- for all
animals (perhaps plants, too) in mysql format (I think that was the format)
and my contact there asked for the postgres database when done. I sent him
the dump_all output and he was happy. State level is more open.

Regards,

Rich

On 8/14/19 10:45 AM, Rich Shepard wrote:

On Wed, 14 Aug 2019, Adrian Klaver wrote:

So you have the tables in Postgres, correct?

Adrian,

Not yet. I have the schema extracted using mdb-schema.

I did something similar with the USDA Nutrient database(with notion of
making it a test dataset): https://ndb.nal.usda.gov/ndb/doc/index

This is from streamnet.org. They're part of the Pacific States Marine
Fisheries Commission and it's a database of all fish-related information
within the Columbia River basin.

Have you looked at:

https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc

Access allows you to define 'virtual' relationships that are not really
enforced as I found out when looking at the data. Good luck on getting
information, my experience is agencies(I have also dealt with USFWS) do
not respond to requests about their data. If you do get the information I
guessing you will have to create your own method of dealing with it.
Don't
count on the data being consistent.

At the federal level cooperation is spotty. Several years ago I downloaded
the International Taxonomic Identification System (ITIS) database (the
internationally accepted list of names -- scientific and common -- for all
animals (perhaps plants, too) in mysql format (I think that was the format)
and my contact there asked for the postgres database when done. I sent him
the dump_all output and he was happy. State level is more open.

Regards,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 14 Aug 2019, Roger De Four wrote:

The easy way - just send it to me.

The more challenging way requires using several tools like excel or Open
Office.

Roger,

I could post the .mdb on a cloud web site for download; it's 565M
uncompressed; the xz-compressed tarball is a mere 42M.

When I ran mdb-export I seleted postgres output. The tables are all insert
into statements of the data. I don't know that LibreOffice would handle
files of 112M (the size of one table as downloaded from their web site), but
emacs had no issues with it. 'Excel' or any other proprietary application is
not an option for me. I defenestrated early in 1997 and run only linux.

Will post the URL for the tarball Real Soon Now.

Thanks,

Rich

On Wed, 14 Aug 2019, Adrian Klaver wrote:

Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc

Adrian,

Not yet, but I will. Didn't see it when I went to the data pages.

Thanks,

Rich

On Wed, 14 Aug 2019, Rich Shepard wrote:

I could post the .mdb on a cloud web site for download; it's 565M
uncompressed; the xz-compressed tarball is a mere 42M.

Will post the URL for the tarball Real Soon Now.

Here it is for anyone interested: <https://tinyurl.com/yyzuhrcg&gt;.

Rich

A few years ago I tried to take an app someone had written for us in
Access years ago and convert it to Postgres.

It seemed like for every rule I tried there were a handful of exceptions.

We wound up just rewriting the app and not trying to export the data from
the previous one.

I hope your project is more successful at extracting the data than ours was.
--
Mike Nolan

On Wed, 14 Aug 2019, Adrian Klaver wrote:

Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc

Thanks, Adrian. This looks like it has all the information I need.

Under which menu did you find this? I had looked in the Data and Resources
menus and searched for 'data exchange' without finding it.

Regards,

Rich

On Wed, 14 Aug 2019, Michael Nolan wrote:

It seemed like for every rule I tried there were a handful of exceptions.
We wound up just rewriting the app and not trying to export the data from
the previous one.

Mike,

This is not surprising. My interest is in the data, not the application.
Much of my consulting practice involves fish distributions, abundances, and
habits. The streamnet database has always been a comprehensive resource for
this information within the Columbia River basin.

Regards,

Rich

[image: image.png]
Rich - This is the ERD for the Access db you posted

On Wed, 14 Aug 2019 at 14:14, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 14 Aug 2019, Roger De Four wrote:

Rich - This is the ERD for the Access db you posted

Thanks, Roger. The exchange format document Adrian found also has an E-R
diagram.

Much appreciated,

Rich

On 8/14/19 11:11 AM, Rich Shepard wrote:

On Wed, 14 Aug 2019, Adrian Klaver wrote:

Have you looked at:
https://www.streamnet.org/wp-content/uploads/2018/06/StreamNetExchangeStandard2018-1.doc

Thanks, Adrian. This looks like it has all the information I need.

Under which menu did you find this? I had looked in the Data and Resources
menus and searched for 'data exchange' without finding it.

Here:

https://www.streamnet.org/data/downloadable-data/
Link: Data Exchange Standard (DES).

Regards,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 14 Aug 2019, Adrian Klaver wrote:

Here:
https://www.streamnet.org/data/downloadable-data/
Link: Data Exchange Standard (DES).

Adrian,

Then I missed it when I was in that subdirectory.

Thanks,

Rich

On Wed, 14 Aug 2019, Rich Shepard wrote:

Thanks, Adrian. This looks like it has all the information I need.

Adrian,

Off the mail list.

I'm reading that metadata document and it seems to me that it's not well
constructed. Perhaps this is an Access thing[1]I tried searching the database tables on their web site but could not find the information I need. As far as I know Access is a flat-file database, not relational.; perhaps the DBA's have
limited knowledge. For example there are a lot of entity1_X_entity2 tables
which I think don't need to exist as one can get the same information from a
query. And, there are a bunch of lookup tables I think could be better
handled the postgres way.

Would you help me with this migration off the mail list?

Regards,

Rich

[1]: I tried searching the database tables on their web site but could not find the information I need. As far as I know Access is a flat-file database, not relational.
find the information I need. As far as I know Access is a flat-file
database, not relational.

On Wed, 14 Aug 2019, Rich Shepard wrote:

Off the mail list.

Sorry all. I thought it went to only Adrian.

Rich