Re: 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(dot)klaver(at)aklaver(dot)com