Batch Append records from multiple tables to a master geodatabase?

[StevenWorkman]

I'm working on a project where we are collecting data out in the field and downloading it from ArcGIS online. We have 4 different apps from where we download data and put them into 4 different geodatabases. The four databases are: Elec, Site, Mech, and Stru. I need to do weekly updates and join the data that's collected in the field to the tables we have in our master database. There are 40 different tables that need to be updated. I'm kind of a beginner in python but what I need to know is how to batch append records from multiple tables in multiple databases to a single database. I've tried to use python and model builder to do this, but the model is massive and there are too many parameters. I also have to run the model 4 times for each database we download the data from.

I've started the script but am running into an error where the schema's aren't matching. I think what is happening is I'm not able to match the names of the items in two different lists. The lists (inputTablesJoin and outputTables) are identical and each matching table name has the same schema too. Here's my code:

import arcpy
import os

workspace = "C:\data\Pump_Station_Inspections"
inputTables = []
## Walk through each folder containing the downloaded data and find tables (Elec, Site, Mech, and Stru)
walk = arcpy.da.Walk(workspace, datatype = "Table")
inputTablesJoin = []

## Append tables to list
for dirpath, dirnames, filenames in walk:
    for filename in filenames:
        inputTables.append(filename)
inputTables.sort()

## Create a list of tables from the master geodatabase
arcpy.env.workspace = "C:\data\Pump_Station_Inspections\InspectionsGISData.gdb"
outputTables = arcpy.ListTables()
outputTables.sort()


for inputTable in inputTables:
    if inputTable in outputTables:
        inputTablesJoin.append(inputTable)
for table in inputTablesJoin:
        arcpy.Append_management(table, outputTables)

Any help will be much appreciated.  Thanks!!

[WesMiller]

Have a look at Mapping input fields to output fields—Help | ArcGIS for Desktop for mapping fields where the schema doesn't match. Your code could get very cumbersome if you a lot of different schema to deal with.

[StevenWorkman]

Thanks Wes,  I'll look into it.  I haven't worked with field mappings before.  There are about 40 tables with about 30 fields each, so yea it'll probably be pretty cumbersome to go that route.

[JakeSkinner]

Hi Steven,

When you download the data from ArcGIS Online, are you downloading as a File Geodatabase?

It looks like the issue is with your append.  You are trying to append a single table (table) to a list of tables (outputTables).  You will need to have the second parameter in this function a single table rather than a list of tables.

[StevenWorkman]

I am downloading it from ArcGIS online.  Can I do a for loop inside that parameter to loop through each matching table? Since I sorted both lists, the items and the indexes are identical.

[JakeSkinner · Accepted solution]

I recently worked with a customer that needed to append all tables from one geodatabase to another.  If you downloaded the data from ArcGIS Online as a File Geodatabase, you could use the below code:

import arcpy, os
from arcpy import env

GDB1 = r"C:\temp\fileGDB1.gdb"
GDB2 = r"C:\temp\fileGDB2.gdb"

env.workspace = GDB1

#Append tables
for table in arcpy.ListTables("*"):        
    env.workspace = GDB2
    for tableGDB in arcpy.ListTables(table):
        try:
                if tableGDB.lower() == table.lower():
                    arcpy.AddMessage("\tAppending " + table)
                    try:                            
                            arcpy.Append_management(GDB1 + os.sep + table, GDB2 + os.sep + tableGDB, "NO_TEST")                                                                
                    except:
                            arcpy.AddMessage("Could not append " + table)
                            pass
        except:
                pass
    env.workspace = GDB1

[StevenWorkman]

Thank you Jake.  That worked!