Some formats are more legacy than others

Having promised to myself (and to whomever listens on Twitter) that I would not think about GIS over this long Labor Day weekend, I did nothing but. Why? A nagging issue that stems from my earlier effort to abandon ArcMap in favor of ArcGIS Pro. While most of my workflows port nicely to Pro, one of my workflows does not work at all. That workflow includes periodic MS Access downloads from a proprietary third party application and joining Access tables to “the GIS”.

It took the cumulative advice of a Twitter village over the Labor Day weekend to convince me that the weakest link in my workflow is MS Access, and that I should focus on liberating my data from Access tables instead of blaming Esri for dropping Access support in ArcGIS Pro.

In the next few days I will experiment with a number of Access-liberating workflow remedies, and will document my experience by adding to this post. Suggestions I have received so far:

  • Use FME
  • Install driver for Excel 2019 and copy/paste data from Access tables to Excel, then join the Excel table
  • Export the Access table to CSV or TXT, then create table(s) from the text file(s)
    • Either via the Access GUI or write a Powershell script to export the Access data to CSV
  • Use Airtable
  • Write Python script to export Access data

Many thanks to (in order of appearance): Simon Jackson, Craig Williams, Bill Dollins, Heather S, Jason Birch, James Fee.

***

[UPDATE September 4, 2019]: What I did today:

  1. Downloaded a 30-day FME trial. Looks great, but procuring $2,250 might be a challenge. Possible long-term solution.
  2. Downloaded the Excel driver, on install got this error. Graig Williams promptly offered a workaround, which worked. Copied Access data into Excel sheet, added Excel sheet to ArcGIS Pro, and joined the resulting table to my shapefile. Everything worked as desired.
  3. Exported the Access table via the Access GUI to a text file (named .CSV), added CSV file to ArcGIS Pro, and joined the resulting table to my shapefile. Everything worked fine, except the table truncated my leading zeros in fields such as ZipCode. I’m sure there is a simple fix.
  4. Looked at Airtable. Looks promising, but both the cost and the “data in the cloud” aspects make it a non-starter for this task.
  5. Did not write Python script. 

Going forward I will follow the steps in item 2 above, using the tools I already have. I will continue to explore ways for liberating Access data, up to and including writing my own scripts. I no longer blame Esri for dropping Access support from ArcGIS Pro.