topic Re: Appending Multiple Personal Geodatabase tables in Python Questions

Appending Multiple Personal Geodatabase tables

shildebrand — Tue, 20 Aug 2019 20:37:49 GMT

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", "", "")

Re: Appending Multiple Personal Geodatabase tables

AlbertoAloe — Wed, 21 Aug 2019 13:17:41 GMT

Samuel,

after a quick look without trying it out:

The second parameter of Append_management must be a dataset (one of your target tables and not a geodatabase workspace)
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

Re: Appending Multiple Personal Geodatabase tables

shildebrand — Wed, 21 Aug 2019 20:44:58 GMT

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"

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

Re: Appending Multiple Personal Geodatabase tables

AlbertoAloe — Thu, 22 Aug 2019 10:20:43 GMT

target_geodatabase + "\\Daily_Valve"

target_geodatabase + r"\Daily_Valve"

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

Alberto

Re: Appending Multiple Personal Geodatabase tables

shildebrand — Thu, 22 Aug 2019 15:51:33 GMT

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")

Re: Appending Multiple Personal Geodatabase tables

AlbertoAloe — Sat, 24 Aug 2019 15:28:22 GMT

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

Re: Appending Multiple Personal Geodatabase tables

JoshuaBixby — Sun, 25 Aug 2019 03:29:25 GMT

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

Re: Appending Multiple Personal Geodatabase tables

BlakeTerhune — Tue, 27 Aug 2019 15:16:13 GMT

Result—Help | ArcGIS Desktop

Re: Appending Multiple Personal Geodatabase tables

shildebrand — Fri, 30 Aug 2019 19:27:55 GMT

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)

Re: Appending Multiple Personal Geodatabase tables

BlakeTerhune — Sat, 11 Dec 2021 23:44:29 GMT

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‍‍

Re: Appending Multiple Personal Geodatabase tables

shildebrand — Tue, 03 Sep 2019 15:49:39 GMT

Thanks Blake, I appreciate the feedback!