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.
Posted by at 4:47 PM
Labels: , , , , , , , , , , , , , , , , , , ,

17 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

Subscribe to: Post Comments (Atom)