2

I am very new in Python. And I guess I need to use lots of Python here.

I have MS Access 2003-2007 database (mdb format) and personal geodatabase (mdb). I created a tool in Access that imports data from personal geodatabase to access mdb, checks it and updates changes.

Now I am working on implementing backwards functionality (transfer data from access mdb to personal geodatabase). For now, I can implement that manually using OLE connection.

How I do it:

1.in ArcCatalog create OLE connection,

2.Connect to my access database backend,

3.Choose a Table I want to use from this access database backend,

4.Open ArcMap, add Feature class from personal geodatabase to the map,

5.Perform join of Feature class and Table,

6.Then using Field calculator update all necessary fields.

For now in my vision I can create custom tool for join Feature class and Table.

Do you have any suggestions how I can automate this process, at least half of this process.

Here is a code to join Table to Feature Class

import arcpy
from arcpy import env
env.overwriteOutput = 1
env.workspace = r"D:\Working\Tools\ToolShare\Scripts"

arcpy.MakeFeatureLayer_management("Crossings", "tempLayer")
arcpy.AddJoin_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")

When I open the feature class, there are no changes... When I tried to use arcpy.JoinField_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")instead of arcpy.AddJoin_managementfields were added to the feature class, but permanently (I don't need that).

After this part I guess I need to use UpdateCursor (Field Calculator point). But I don't know how correctly it can be used here.

Here are my thoughts, please help me to organize them.

Join and calculate field script

import arcpy
import sys
import os
from arcpy import env

env.overwriteOutput = 1
scriptPath = sys.path[0]
arcpy.AddMessage("Script folder: " + scriptPath)
toolSharePath = os.path.dirname(scriptPath)
arcpy.AddMessage("ToolShare folder: " + toolSharePath)

inFeatureClass = arcpy.GetParameterAsText(0) 
inTable = arcpy.GetParameterAsText(1)

arcpy.MakeFeatureLayer_management("Crossings", "tempLayer")

arcpy.AddJoin_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")

arcpy.CalculateField_management("tempLayer", "Status_", "!Status!", "PYTHON_9.3", "")

arcpy.RemoveJoin_management("tempLayer", "")

Add feature class script

import arcpy
from arcpy import env

inFeatureClass = arcpy.GetParameterAsText(0)
mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd,"*")[0]
newlayer = arcpy.mapping.Layer(inFeatureClass)
arcpy.mapping.AddLayer(df, newlayer,"BOTTOM") 

Add feature class, join and calculate together

import arcpy
import sys
import os
from arcpy import env

env.overwriteOutput = 1

scriptPath = sys.path[0]
arcpy.AddMessage("Script folder: " + scriptPath)
toolSharePath = os.path.dirname(scriptPath)
arcpy.AddMessage("ToolShare folder: " + toolSharePath)

inFeatureClass = arcpy.GetParameterAsText(0)  # Feature class from personal geodatabase
inTable = arcpy.GetParameterAsText(1)  # Table from access database (mdb)

mxd = arcpy.mapping.MapDocument("CURRENT")
df = arcpy.mapping.ListDataFrames(mxd,"*")[0]
newlayer = arcpy.mapping.Layer(inFeatureClass)
arcpy.mapping.AddLayer(df, newlayer,"BOTTOM")

arcpy.MakeFeatureLayer_management("Crossings", "tempLayer")
arcpy.AddJoin_management("tempLayer", "UNIQUE_NAME", "tblCrossings", "UNIQUE_NAM")
arcpy.CalculateField_management("tempLayer", "Status_", "!Status!", "PYTHON_9.3", "")
arcpy.RemoveJoin_management("tempLayer", "")
1

You are almost there. You have completed up to step 5, however arcpy.AddJoin_management just joins the two tables in memory (as per your manual step 5) - you'll still need to calculate the fields you want updated.

For step six you'll want to make use of arcpy.CalculateField_management for each field you need to have updated, just as you would if you used field calculator manually in your attribute table.

Something like

arcpy.CalculateField_management("layer", "fieldToUpdate", "!fieldToUpdateFrom!", "PYTHON_9.3", "")

for each field you want to calculate, where fieldToUpdate is your Feature Class field that needs updating, and !fieldToUpdateFrom! is the Table field you're getting the value from.

To be tidy you may want to include a arcpy.RemoveJoin_management("layer", "joinName") to remove your join.

  • What is the joinName here? – O.Baklanova Jan 18 '16 at 15:35
  • and do you think that's everything that might be automated in this process? – O.Baklanova Jan 18 '16 at 15:43
  • @O.Baklanova I believe joinName is usually the name of your input layer, although it is labelled as Optional in the tool, so can be left blank - arcpy.RemoveJoin_management("layer","") – Midavalo Jan 18 '16 at 18:18
  • the items you have listed 1-6 are covered if you include the Calculate Field and the Remove Join. If you had a list of fields you might be able to loop through them to update, although if there's not many of them it may just be easier to repeat the CalculateField and RemoveJoin for each field – Midavalo Jan 18 '16 at 18:21
  • I want to automate this process a little bit more. I have to scripts. One is for joining feature class and table and updating one field using arcpy.CalculateField_management. And another one is for adding feature class to the map. Separately they work fine. When I put them together - the script doesn't work. I add the scripts in the question – O.Baklanova Jan 18 '16 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.