Appending Multiple Personal Geodatabase tables

796
10
08-20-2019 01:37 PM
SamuelHildebrand
New Contributor III

Hi everyone,

I am trying to append multiple personal geodatabase tables to a 2nd stand-alone personal geodatabase table using a python script.  The idea here is that the user would select a workspace folder that has multiple personal geodatabases (.mdb's) that each have tables named "Valve", "Hydrant", and "Cannot_Locate_Valve".  The script would then create a new file geodatabase ("CombinedData.gdb") and import an .xml file that has the following two tables: "Daily_Valve" and "Daily_Hydrant".  The script will then append all tables named "Valve" or "Cannot_Locate_Valve" into the just created "Daily_Valve" and append all tables named "Hydrant" into the "Daily_Hydrant" table.  The screenshot below shows the directory structure that I am referring to.  Can anyone lead me in the right direction on this?

import arcpy
from arcpy import env
import arcpy.da as da
import os

#Set Workspace
workspace = arcpy.GetParameterAsText(0)

#Create .mdb to Hold Combined Valve & Hydrant Data
target_geodatabase = arcpy.CreateFileGDB_management(workspace, "CombinedData.gdb")

#Load Schema
arcpy.ImportXMLWorkspaceDocument_management (target_geodatabase, r"W:\Projects\KansasCityMO\Valve And Hydrant\Data\Databases\KCMO_DAILY_FILE.xml", "SCHEMA_ONLY")

#Search for Valve & Hydrant Tables and Append to Combined Daily Tables
for dirpath, dirnames, filenames in da.Walk(workspace, datatype="Table"):
   for filename in filenames:
      if filename == "Valve" or "Cannot_Locate_Valve":
         arcpy.Append_management(os.path.join(dirpath, filename), target_geodatabase, "TEST", "", "")
      elif filename == "Hydrant":
         arcpy.Append_management(os.path.join(dirpath, filename), target_geodatabase, "TEST", "", "")

0 Kudos
10 Replies
AlbertoAloe
Occasional Contributor

Samuel,

after a quick look without trying it out:

  1. The second parameter of Append_management must be a dataset (one of your target tables and not a geodatabase workspace)
  2. Instead of using the xml  you may create a table inside your geodatabase with CreateTable_management. You have the option to pass a table template to match the desired schema or you can create it empty and then you start adding fields with AddField_management

Alberto

0 Kudos
SamuelHildebrand
New Contributor III

Thanks Alberto,

Do you know how to reference the target table that was created earlier in the script when the xml document was imported?  Without referencing the direct path (since a new empty table with a different path will be created every time the script is run).  I'm thinking something like:

target_geodatabase + "Daily_Valve"

or

os.path.join(target_geodatabase, "Daily_Valve")

0 Kudos
AlbertoAloe
Occasional Contributor

target_geodatabase + "\\Daily_Valve"

or

target_geodatabase + r"\Daily_Valve"

or

os.path.join(target_geodatabase, "Daily_Valve")

Alberto

0 Kudos
SamuelHildebrand
New Contributor III

Thanks Alberto,

Any thoughts as to why I get this error when using any of those?:

TypeError: unsupported operand type(s) for +: 'Result' and 'str'

import arcpy
from arcpy import env
import arcpy.da as da
import os

# Set Workspace
workspace = r"W:\Projects\KansasCityMO\Valve And Hydrant\Data\PFdata\Export\2019\0819\0813"

# Create .mdb to Hold Combined Valve & Hydrant Data
target_geodatabase = arcpy.CreateFileGDB_management(workspace, "CombinedData.gdb")

# Load Schema
arcpy.ImportXMLWorkspaceDocument_management (target_geodatabase, r"W:\Projects\KansasCityMO\Valve And Hydrant\Data\Databases\KCMO_DAILY_FILE.xml", "SCHEMA_ONLY")

# Set the current workspace
arcpy.env.workspace = workspace

# List all personal geodatabase tables in the current workspace and append to Daily_Valve table
workspaces = arcpy.ListWorkspaces("*", "ACCESS")
for workspace in workspaces:
   arcpy.env.workspace = workspace
   tables = set(arcpy.ListTables("Valve") + arcpy.ListTables("Cannot_Locate_Valve"))
   for table in tables:
      arcpy.Append_management(table, target_geodatabase + "\\Daily_Valve", "NO_TEST")

0 Kudos
AlbertoAloe
Occasional Contributor

Because if you write...

target_geodatabase = arcpy.CreateFileGDB_management(workspace, "CombinedData.gdb")

you are assigning to the variable target_geodatabase what arcpy.CreateFileGDB_management is returning: an object of type workspace that cannot be concatenated with a string.

You have to concatenate the full path to your geodb with the table name

Alberto

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Geoprocessing tools return Result objects, so arcpy.CreateFileGDB_management returns a Result object containing all the various information that Result objects contain. 

0 Kudos
BlakeTerhune
MVP Regular Contributor
0 Kudos
SamuelHildebrand
New Contributor III

Thanks Alberto for your help.  I was able to create a variable that concatenates the full path to the newly created geodatabase name

import arcpy
from arcpy import env
import arcpy.da as da
import os

# Process: Delete Features From Daily File
arcpy.TruncateTable_management(r"W:\Projects\KansasCityMO\Valve And Hydrant\Data\Databases\KCMO_Daily_File.gdb\Valve")
arcpy.TruncateTable_management(r"W:\Projects\KansasCityMO\Valve And Hydrant\Data\Databases\KCMO_Daily_File.gdb\Hydrant")

# Script arguments
workspace = arcpy.GetParameterAsText(0) # workspace folder containing exported .mdb's
outValveFC = arcpy.GetParameterAsText(1) # output valve feature class
outHydrantFC = arcpy.GetParameterAsText(2) # output hydrant feature class
spRef = arcpy.GetParameterAsText(3) # output coordinate system

# Set the current workspace
arcpy.env.workspace = workspace

# Create .gdb to Hold Combined Valve & Hydrant Data
arcpy.CreateFileGDB_management(workspace, "KCMO_Workspace_2019.gdb")
fileGDB = os.path.join(workspace, "KCMO_Workspace_2019.gdb")

# List all personal geodatabase tables in the current workspace and export to filegdb
workspaces = arcpy.ListWorkspaces("*", "ACCESS")
for dailyFolder in workspaces:
arcpy.env.workspace = dailyFolder
tables = set(arcpy.ListTables("Valve") + arcpy.ListTables("Cannot_Locate_Valve") + arcpy.ListTables("Shutdown_Valve") + arcpy.ListTables("Hydrant") + arcpy.ListTables("CNL_Hydrant"))
for table in tables:
arcpy.TableToGeodatabase_conversion(table, fileGDB)

0 Kudos
BlakeTerhune
MVP Regular Contributor

Just an observation of your script. You are importing all of arcpy, which includes arcpy.env and arcpy.da so you don't need to import those explicitly. Especially since you are still calling it as arcpy.env and don't appear to be using arcpy.da. This will do:

import arcpy
import os
0 Kudos