I am trying to transfer over row data from a table to columns in a shapefile table. There is a common "SA" variable between the two (i.e. the study area number) but there is more than one land use type, hence why I must create two new columns for each landuse, in order to capture the area and percentage value for each type. A join won't work because it only captures the values for one land use as opposed to all of them, as the first table doesn't have as many entries as the second.

The reason I need to migrate the data is because I'm trying to use this in dynamic text on data driven pages.

I wrote a SQL script with the help of my friend but it won't execute through field calculator, giving the generic 99999 error:

UPDATE SApeelfinal
SET LUp1 = (SELECT perc FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1)
WHERE EXISTS (SELECT 1 FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1)

Though he said that I have to change the LANDUSE value for each time I run the script and as well change SUM_AREA and perc. I tried putting it in a toolbox but it wouldn't run that way either. Can someone enlighten me as to a better way to do this?

Also I was wondering if I had to manually create the new fields for this to work.

Here is a screenshot of the two tables: enter image description here

You can also find a bigger version here: https://i.sstatic.net/hfeQv.jpg

The red is the common variable between the two tables, the blue is the field I want the percentage to go to (and there would obviously be a new one for area), and the green are the two fields I want to migrate over based on the SATXT and FID_text common fields.

I've tried using the following python script in the codeblock:

    def getNewVal(newVal,landuse):
  if landuse == 1:
   return newVal

With this in the calculate field:


But it returns the same error. Is there something I'm doing wrong?

To clarify, there are no joins because joins make dynamic text not work.


5 Answers 5


Your need to convert your SQL into VBA or Python for the Field Calculator in ArcMap


ArcGIS 10 Users can also use Python scripts in ArcGIS Field Calculator

enter image description here

This is a cheaper option than buying ArcSDE and a database that supports SQL.

  • I'm just not certain how to translate it, any tips? edit: I see what you answered before... I'll give it a try.
    – Emily
    Aug 3, 2011 at 15:32

Unfortunately, your problem cannot be easily solved by Calcluate Field. Since you are trying to extract several fields of information from another table for each of your Study Areas, I will have to recommend the unthinkable-- cursors. First you will want to create columns for each of the attributes you want (Area and Percentage coverage for each land use type per study area). Then, you will want to use an Update cursor to loop through each row, creating a search cursor afterwards to query the statistics table and fetch the data you are looking for based on your join field (Study Area ID). Here is some sample code.

#Create the variable which stores the path of the table to be updated
infile = r"path"

#Create the variable which stores the path to the stats table we will be searching
intable = r"path"

# Create update cursor for feature class
rows = arcpy.UpdateCursor(infile)
print "Update Cursor created"
#For Loop goes through every row in the file provided and does what is listed below!
for row in rows:
    row.lup0 = 0
    row.lua0 = 0 #If you want default values, set them here
    where = "SATXT = '"+row.FID_Text+"'" #You need single quotes for a text field. This is one way to do it.
    rowsearch= arcpy.SearchCursor(intable, where)
    for x in rowsearch:
        if x.LANDUSE == 0:
            row.lup0 = x.perc
            row.lua0 = x.SUM_AREA #Create a check for each land use type so it will fetch the data

    rows.updateRow(row) #This should close the for loop so it updates the row every time through

# Delete cursor and row objects to remove locks on the data
del row
del rows # after this the script stops
print "All done!"

It is a rather brutish method for setting and getting data, but it will work for a small variable set such as what you have. Simply repeat the land use number check for each one so you can set the appropriate values. I believe you could probably use dictionaries to maintain references to the field names and iterate through that, but this should do for now.


Do you have any familiarity with R? (I recommend using R-Studio as I think it is more intuitive than the default interface). It's possible to read the .dbf file of a shapefile in R using the foreign package. You can then do the operations you want and write out a dbf with the data you need and join it to the shapefile. Here is an example of this can be done:

# install a package to read dbf files (only needs to be done once)

# import package

# read in the dbf associate with the shapefile 
boundaries <- read.dbf('G:/US_data/Census2000.dbf', as.is = TRUE)

# boundaries is now a dataframe object

# do something to file and create a new dbf called 'new_boundaries'

# write this data out to dbf file 
write.dbf(new_boundaries , 'G:/example.dbf', factor2char = TRUE, max_nchar = 254)
  • I'm not familiar with R, could you link me to a helpfile description? help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//… Is it this? R-tree?
    – Emily
    Aug 3, 2011 at 19:30
  • 1
    No, it is a different program used for statistical analysis. It may not be that useful to start using it just for this; mainly mentioning it as you can deal with dbfs quite easily. You could just as easily export your data into another format and manipulate it there also.
    – djq
    Aug 3, 2011 at 19:46
  • I tried exporting to open office but had some difficulties getting it to work also. Thank you for your advice :)
    – Emily
    Aug 3, 2011 at 19:48
  • 1
    You can always export the attribuite table as .csv and open it in openoffice.
    – djq
    Aug 3, 2011 at 20:36

My solution to this problem has been to divide my table out into single tables.

In other words take the first record of each match and make a table called match 1 (for instance only).

Then do the same with each 2nd match, on and on until all of the table records are in a numbered table.

Then by joining each one consecutively and calculating into an lu1p, lu2p ... you get all data merged onto one row.
Then you can cacluate any aggregates you need to complete.

Not the programmers method but my best hack.

  • I'm not sure if it will work as I'm trying to transfer rows into columns... Would it do the same for that?
    – Emily
    Aug 3, 2011 at 16:32
  • 2
    I would write a Python script to read the needed values from the table into a dictionary, and then use an UpdateCursor to populate the columns in the shapefile. It would help if the shapefile already had the correct schema.
    – blah238
    Aug 3, 2011 at 18:15
  • 2
    my mistake. Another method which is only different in process. Would be summarize your table on your satxt field with sums of the area and perc.
    – Brad Nesom
    Aug 3, 2011 at 18:15
  • I'm really unfamiliar with scripting... I will try to find it in the helpfiles as well.
    – Emily
    Aug 3, 2011 at 18:21
  • 1
    simply open your table and right click on the satxt field. Select summarize and choose a new table name. set the sum fields (option) and create your summarization.
    – Brad Nesom
    Aug 3, 2011 at 18:32

If you have access to MS Access, why not load these into a geodatabase, open it in Access, and run your SQL there?

I recently did the opposite - going from columns to rows - on a set of Excel data in Access (although I used VBA to make it work).

  • I only have Open Office but I'll see if I can run it on my laptop when I get home, though I'm not certain if I have Access. edit: Will open office Base work? I'm really new to it...
    – Emily
    Aug 3, 2011 at 19:57
  • 1
    I'm not sure - I haven't used Base. Another question (stackoverflow.com/questions/1946234/…) reminded me of something. If you can get stat_fin into Calc, you may be able to use the Data Pilot tool to create your columns. Then you'd be able to link the new table to your shapefile.
    – Patrick
    Aug 3, 2011 at 20:07
  • trying to execute the code in Open office resulted in this: "The query can not be executed. It is too complex."
    – Emily
    Aug 3, 2011 at 20:08
  • 1
    Export the table to text or Excel format, open in Calc, and see if Data Pilot will work.
    – Patrick
    Aug 3, 2011 at 20:11
  • Open Office Base will work external full-featured SQL databases such as MySQL, PostgreSQL and Oracle through ODBC or JDBC drivers. (using HSQLDB 2.x) en.wikipedia.org/wiki/OpenOffice.org_Base
    – Mapperz
    Aug 3, 2011 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.