Saturday, August 8, 2009

Batch Edit String Fields - Free Tools for Calculating String Fields

If you’ve ever tried to make batch edits on all or a selection of string values in an attribute table, you’ve probably spent a significant amount of time searching for the correct VBScript to plug into the Field Calculator dialogue box to get the desired results. Well search no more! A few years back I wrote the following Python scripts to simplify common batch edits made to string fields:

  • Capitalize: Capitalizes the first letter of a string. Ex. Changes "hello world" to "Hello world"
  • Capitalize Words: Capitalizes the first letter of every word in a string. Ex. Changes "hello world" to "Hello World"
  • Lower Case to Upper Case: Converts lower case letters of a string to upper case. Ex. Changes "hello world" to "HELLO WORLD"
  • LStrip: Strips a specified number of preceding characters of a string.
  • LTrim: Removes preceding white spaces from the end of a string.
  • Replace: Replaces current string with a new string.
  • RStrip: Strips a specified number of trailing characters of a string.
  • RTrim: Removes trailing white spaces from the end of a string.
  • Upper Case to Lower Case: Converts upper case letters to lower case. Ex. Changes "HELLO WORLD" to "hello world"

Download the toolbox with all nine Python scripts for free! The link below retrieves a zip file containing a toolbox (.tbx), nine Python scripts (.py) listed above, and a readme.txt from the geospatial database. Right click the link and select Save Target As... Unzip the folder and be sure to store the toolbox in the same location as all of the Python scripts – relative paths are used to locate the scripts.

Batch Edit String Fields

Minimum Requirements:
ArcView license of ArcGIS 9.2
Python 2.4

The scripts can be run in either ArcMap or ArcCatalog. Edits can be made on all records in a feature layer, feature class, or shapefile. If a selection is used, the scripts will only run on the selected records. A SQL expression can be applied to the script to run on only those features that meet the conditions of the SQL expression.

*Important Note*
All changes made using these scripts are permanent unless they are used in an Edit session (ArcEditor or ArcInfo license required).





In this example, the Capitalize Words tool converts the first letter in each word in the ADDRESS field from lower to upper case.

31 comments:

  1. Brooke, how hard would it be for you to write a command that will automatically populate the XYZM values from a 3D feature into the fields? I use it frequently at work when working with Terrains and would love to be able to use a simple command rather than entering the VB all the time.

    Thanks!

    ReplyDelete
  2. Thanks for your comment. See the following posts that address this:

    http://www.geospatialanalyst.com/2009/08/easy-calculoator-free-tools-for.html

    http://www.geospatialanalyst.com/2009/08/get-xyzm-populate-x-y-z-and-m.html

    ReplyDelete
  3. Brooke,
    I downloaded the tools but cannot see how to "run" them. In the field calculator tool for AV9.3 edit session, the only files I can load
    are *.cal files, not *.py files.

    ReplyDelete
  4. Thanks for your question. The .py files are run as tools in ArcToolbox rather than as a field calculation. Once you have unzipped the toolbox (.tbx) and python (.py) files and stored all of them in the same folder, open the ArcToolbox window in ArcMap. Right click in the white space in AcrToolbox and select Add Toolbox... and navigate to the folder where you saved the BatchEditStringFields.tbx. Select the toolbox and click Open. You should then see the toolbox along with its corresponding nine .py files in ArcToolbox. Double-click any of the scripts to open its dialogue box.

    Let me know if you have any problems. Thanks for visiting my blog!

    ReplyDelete
  5. Brooke, I'm looking for a script that deletes or replaces some characters from string values. I have one field where the string values will be used as names for feature classes when I split the feature class into many, one for each feature. Example: I would like to replace the text "1630089 - 1630" with "16300891630" or "1630089_1630" (I'm not sure which one will work). It is the " - " part of the string that is the problem. I see that I might find a sollution by using several of the scripts posted by you, but if there is one script that will do the job, I'm grateful for directions.

    Thanks!
    Ivar

    ReplyDelete
  6. Ivar,
    Thanks so much for visiting my blog and posting your question. Please see my latest post (http://www.geospatialanalyst.com/2009/10/find-and-replace.html) for a solution. If you have any other questions, please feel free to ask.

    Thanks,
    Brooke

    ReplyDelete
  7. I'd like to add the Find and Replace tool to my model. I dragged it to my model window, and the "box" got added, but there is no "output" even when I've populated the values. So I guess my question is 2-part: 1 - is this possible, and 2 - how do I do it.
    Thanks.

    ReplyDelete
  8. Thanks so much for visiting my blog and posting your question. Ah! I forgot to add the output to the scripts. Thanks for catching this. I just corrected the problem and re-uploaded the zipped file containing the updated scripts. If you go back and download them now, you should be able to use them with Model Builder. Let me know if you have any problems.

    Thanks again,
    Brooke

    ReplyDelete
  9. Dear Brooke,

    Thank you very much for modifying your script. It now has an output "bubble".

    I realize these are provided as-is and you have other things to do, but maybe you could address a couple of issues:

    -While the output bubble appears, I cannot connect the output to a following task.

    -I am trying to use the Find & Replace function. A field in my shapefile has the text (PROPOSED) - yes, with the parentheses - and I am trying to remove that text and just keep the rest of the text in the field. Here is some example text:

    RORY (PROPOSED) AVE SW

    I want to make this into:

    RORY AVE SW

    So in the Find box I typed:
    (PROPOSED) <-- space at beginning and end

    In the replace box I typed:
    <-- a single space

    And the result was:
    RORY lAVE SW

    Any suggestions? Thanks for your time.

    ReplyDelete
  10. Brilliant, saved me an absolute heap of time. Thank you for all your efforts in creating these scripts. absolute godsend!

    ReplyDelete
  11. This is exactly what I was looking for!

    Your efforts are greatly appreciated!!

    I'll be nominating you for the "Geospatial Medal of Freedom" :-)

    Cheers,


    Jamie Whitters
    Sydney, NS, Canada

    ReplyDelete
  12. Just used your capitalize tool.....works fantastic!!!!

    ReplyDelete
  13. Brooke,

    Do you have any suggestions for how to strip zero's just from the left side of a number within the SQL calculator box?

    I'm thinking "if - then" statements.



    For Ex:

    010

    000

    00A

    300

    I am trying to get the result to look like this:

    10



    A

    300

    Chris
    chrisc@hiltonheadislandsc.gov
    843-341-4797

    ReplyDelete
  14. updated:

    Hi Brooke,

    Do you have any suggestions for how to strip zero's just from the left side of a number within the SQL calculator box?

    This thread has me stumped a little bit but perhaps you can explain it a little clearer to me. http://forums.esri.com/Thread.asp?c=93&f=1729&t=250864

    As a side note, I want to do this within model builder to dovetail with a number of other processeses i'm stringing together.

    For Ex:
    010
    000
    00A
    300

    I am trying to get the result to look like this:
    10

    A
    300

    Thanks so much for the time.
    Chris
    chrisc@hiltonheadislandsc.gov
    843-341-4797

    ReplyDelete
  15. Chris,

    Thanks for your question. You inspired my most recent post:

    http://www.geospatialanalyst.com/2011/03/remove-leading-zeros-from-string.html

    I hope that helps.

    Brooke

    ReplyDelete
  16. Hi
    How to format a sting values in ArcGIS10 by using filed calculator

    Ex: The particular field is like

    1
    2
    3
    3.1
    40
    2.1
    Her i need to format like this
    001.00
    002.00
    003.00
    003.01
    040.00
    002.01

    ReplyDelete
  17. Thank you for the tools, I have been trying to use the field calculator and python to edit file path name stored in a field. I could not get my attempts past it seeing the / as a break. Your toolbox replace worked perfect and now I can move forward, thanks again.

    ReplyDelete
  18. Hi Brooke,
    Thank's for your batch, but it's didn't working with the 10 version...is it true???

    ReplyDelete
  19. Hi Norra,

    The tools do work in 10. What are you trying to run?

    Brooke

    ReplyDelete
  20. Hi Brooke,

    I'm trying to run the capitalize words tools, i juts receive the message end the end of running " successful" but in my attribute tables nothing is changed. i'm following all steps. double click to the batch, select the file, select the field who need to be changed and run the tool. nothing in the end.
    for info, i'm using the 10 version with arcinfo licence.

    ReplyDelete
  21. Hi Norra,

    I'm not sure why they aren't working for you. Though it shouldn't matter, what is the format of your data (file gdb, shp, sde)? Could you send me a sample of your data?

    Brooke

    ReplyDelete
  22. Hi Brooke,

    Thank's for your message, i using the file gdb and i don't know how to send it to you,
    there's no way for joining file.

    ReplyDelete
  23. I need to pad some of my values in my attribute table with zeros to the sum of 11 string characters. Help?

    ReplyDelete
    Replies
    1. Hello,

      I wrote a .cal file that does what you're asking to do. You can download it from the link below and load it into the Field Calculator. Once it's loaded be sure to update the field to the name of field you are adding the zeros to. Hope that helps!

      Brooke

      http://www.geospatialdatabase.com/tools/padzeros.cal

      Delete
  24. Thanks for posting these tools! I am able to execute FindReplace from the toolbox GUI, but can't call in the Python window in ArcMap. I get the following error:

    AttributeError: 'module' object has no attribute 'FindReplace'

    Any thoughts?

    Thanks!

    ReplyDelete
    Replies
    1. Take a look at this topic for importing a custom toolbox:
      http://resources.arcgis.com/en/help/main/10.2/index.html#//018v0000005z000000

      Delete
  25. Brooke,

    I am trying to strip a varying number of characters from the front of a string. For example, I want to change "SH144-Midway-HHNW" to "Midway-HHNW" and "SH72-Rosedale-HHS1" to "Rosedale-HHS1".What would you recommend I do to strip everything up to and including the first "-".

    Thanks,
    Carol

    ReplyDelete
  26. Hi,
    I'm trying to use the Find and Replace script but I do not know where putting within the script the character to find and the one to replace... Thank you!

    ReplyDelete
  27. hi,
    I need to remove spaces for several fields in a stand-alone geodatabases table. Is it possible to run these scripts on a stand-alone table? In addition, run it on the entire table?
    Thanks!

    ReplyDelete
  28. How to get rid of street number of 35 in a street name field? For example "35 maple st" to "maple st"?

    ReplyDelete