How to open an Ms Access database adn read a table with arcpy or Python

1535
6
12-22-2020 11:38 AM
JoseSanchez
Occasional Contributor III

Hello everyone,

 

I need to read a table located in a Ms Access database and process it with layers.

 

What is the best way to transfer or copy a MS Access table into a FGDB using python or arcpy?

Thanks

 

0 Kudos
6 Replies
curtvprice
MVP Esteemed Contributor

Which flavor of Access? ArcMap or Pro?

ArcMap, set up an ODBC connection

https://support.esri.com/en/technical-article/000009382

I don't believe accdb or mdb are supported in ArcGIS Pro.

Does ArcGIS Pro support MS Access 2007 (accdb)? 

You could use pyodbc but look out for the 32/64 bit issue:

https://datatofish.com/how-to-connect-python-to-ms-access-database-using-pyodbc/

 

JoseSanchez
Occasional Contributor III

ArcGIS 10.7

0 Kudos
curtvprice
MVP Esteemed Contributor

ArcGIS "ArcMap" 10.7 can read .mdb directly (though they don't recommend doing this, see the support article I posted). ArcMap is 32-bit so that solves some of the hassle. But accdb you need to set up an odbc connection. I would do what works, what works best may depend on your database format, the data involved, how often you need to do this, etc.

0 Kudos
JoeBorgione
MVP Emeritus

I think I'd got the route of using python to extract the access tables to a csv and then to a table to table with arcpy.  see https://datatofish.com/how-to-connect-python-to-ms-access-database-using-pyodbc/  for pyodbc and https://stackoverflow.com/questions/37289951/how-to-write-to-a-csv-line-by-line/37290105#37290105 to write to a csv...

That should just about do it....
0 Kudos
JoseSanchez
Occasional Contributor III

Thank you.

 

What about arcpy.utilsol.ImportAccessTables?

In ArcGIS 10.7  with Python 2.7 I am getting the following error message:

arcpy.utilsol.ImportAccessTables(database,
AttributeError: 'module' object has no attribute 'utilsol'

 

 

0 Kudos
curtvprice
MVP Esteemed Contributor

"utilsol" seems to indicate this may be part of a "solutions" add-on you don't have installed.

I would point back to the Esri support article which was updated earlier this year so I bet it has good guidance:

https://support.esri.com/en/technical-article/000009382