Thread: Converting Access .mdb to postgres
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. 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. 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
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>. 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
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, 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
Attachment
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]; 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.
On Wed, 14 Aug 2019, Rich Shepard wrote: > Off the mail list. Sorry all. I thought it went to only Adrian. Rich