Update Table with values from Second Table

[MikeMacRae]

Hey everyone,

I am trying to update a table with values from a second table. Basically, I have a table with field types that are all wrong (they have DOUBLES and TEXT types that are not what my client wants) and I want to fix that using python. Here are my steps:

1. Create new table using arcpy.CreateTable_management


2. Get a count of the rows from the original table and use arcpy.insertcursor on the second table to insert the same amount of rows.


3. List the fields from the original table and add them as new fields to the new table using arcpy.AddField


4. Set a search cursor to read through the original table and use arcpy.updateCursor to update these values into the second table

Here is where I am hung up. The first 4 fields will be static. They will always be in every 'original' table I use. The next fields will be dynamic. The amount of fields and the field names may change from one 'original' table to the next. My script can handle creating the new table and adding the fields dynamically, but I'm kind of hung up on the update cursor part. I want the search cursor/update cursor to be able to read through the fields dynamically and set the values to each appropriate field from the original to the new. I'm hoping this is the right approach. Please see the last code block of my script. I've put a couple comments in there. I keep getting errors on:

update.getValue(names) = row.getValue(names)

import arcpy, sys, traceback from arcpy import env  env.overwriteOutput = True  env.workspace = r"Z:\Test.gdb"  arcpy.CreateTable_management(r"Z:\Test.gdb", "TABLE_E2_1_new") count =  int(arcpy.GetCount_management("TABLE_E2_1_original").getOutput(0))  rows = arcpy.InsertCursor("TABLE_E2_1_new") x = 1  while x <= count:     print x     row = rows.newRow()     rows.insertRow(row)     x+=1 del row, rows, x  tableList = arcpy.ListTables()  for table in tableList:     if table == "TABLE_E2_1_original":         fieldList = arcpy.ListFields(table)                      for field in fieldList:             if field.name not in ["OBJECTID", "TYPE", "Common_Name", "Scientific_Name", "Species"]:                 print field.name                 arcpy.AddField_management("TABLE_E2_1_new", field.name, "SHORT")             elif field.name != "OBJECTID":                 print "Remaining " + field.name                 arcpy.AddField_management("TABLE_E2_1_new", field.name, "TEXT", "", "", 100)  del table, tableList, fieldList, field  searchRows = arcpy.SearchCursor("TABLE_E2_1_original") updateRows = arcpy.UpdateCursor("TABLE_E2_1_new")  update = updateRows.next()  fieldList = arcpy.ListFields("TABLE_E2_1_new")  for row in searchRows:             # List fields to get field name and pass into variable for calling into update cursor/search cursor below             for field in fieldList:                  names = field.name                   update.getValue(names) = row.getValue(names) # Keep getting error "***can't assign to function call.                  update = updateRows.next()  del update, updateRows, row, searchRows, names, field, fieldList

[StacyRendall1 · Accepted solution]

Hi Mike,

That is definitely possible. Just a little confused though; from reading a few times (and looking at your code) I think you want those four 'static' fields to always be text, and any other field (the dynamic ones) to be a short integer... Is that correct? It is possible to do all kinds of things with field types with your input and output fields as long as you know clearly what it is that you are trying to do (i.e. if you have a rule or set of rules). For example: every float, double or long could be cast to a short (as long as the input values don't contain too many digits, of course). Let me know if you need more information on this kind of stuff.

Your code is having the error because you are setting a getValue(field) = another getValue(field); you need setValue (field, getValue(field)).

I also think you could order you code a little better. Insert cursor lets you add rows and assign values at the same time - so you may as well just use that;

1. Create output table


2. Add fields in input table to output table




1. some stuff with type casting...?




4. for input table rows (search cursor)




1. new row for output table


2. for each field in input table




1. get row/field value


2. set row/field value in output table


3. write new row to output table

Here is my re-implementation of your code (which works fine as long as all the input LONGs don't contain too many values):

import arcpy  baseWorkspace = r"Z:\Test.gdb" inputTable = "TABLE_E2_1_original" outputTable = "TABLE_E2_1_new"  if arcpy.Exists(baseWorkspace + "\\" + outputTable):     arcpy.Delete_management(baseWorkspace + "\\" + outputTable)     print 'Deleted: %s' % baseWorkspace + "\\" + outputTable  staticFields = ["OBJECTID", "TYPE", "Common_Name", "Scientific_Name", "Species"]  arcpy.CreateTable_management(baseWorkspace, outputTable) print 'Output table created...'  fieldList = arcpy.ListFields(inputTable) for field in fieldList:     if field.name not in staticFields:         print field.name         arcpy.AddField_management(outputTable, field.name, "SHORT")     elif field.name != "OBJECTID":         print "Remaining " + field.name         arcpy.AddField_management(outputTable, field.name, "TEXT", "", "", 100) del field print 'Added fields to new table...'  sC = arcpy.SearchCursor(inputTable) iC = arcpy.InsertCursor(outputTable) for row in sC:     row_new = iC.newRow()     for field in fieldList:         if field.name != "OBJECTID":             value = row.getValue(field.name)             row_new.setValue(field.name, value)     iC.insertRow(row_new)     del row_new del row, iC, sC, field, fieldList  print 'Copied values to new table...' print 'Script complete.'

Cheers,
Stacy

p.s. env.overwriteOutput never does anything for me - I usually explicitly search for and delete.

[MikeMacRae]

Stacy, thanks so much for your contribution. You got the idea I was getting at and nailed it. Much appreciated.

Mike