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", "", "")
Samuel,
after a quick look without trying it out:
Alberto
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")
target_geodatabase + "\\Daily_Valve"
or
target_geodatabase + r"\Daily_Valve"
or
os.path.join(target_geodatabase, "Daily_Valve")
Alberto
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")
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
Geoprocessing tools return Result objects, so arcpy.CreateFileGDB_management returns a Result object containing all the various information that Result objects contain.
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)
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