Batch Append records from multiple tables to a master geodatabase?

4368
6
Jump to solution
03-16-2016 05:30 AM
Highlighted
New Contributor III

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

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Esri Esteemed Contributor

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

View solution in original post

6 Replies
Highlighted
Regular Contributor III

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.

Reply
0 Kudos
Highlighted
New Contributor III

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.

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

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.

Reply
0 Kudos
Highlighted
New Contributor III

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.

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

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

View solution in original post

Highlighted
New Contributor III

Thank you Jake.  That worked!

Reply
0 Kudos