I'm operating ArcGIS.

I need to make a calculated field. The value of the field is a sub-string of one of the other fields. My problem is that the other field (PopUpInfo) is an HTML string:

enter image description here

An example of the field is below:

<tr><th colspan='2' align='center'><em>Attributes</em></th></tr>
<tr bgcolor="#E3E3F3"> <th>STATEFP</th> <td>04</td> </tr>
<tr bgcolor=""> <th>COUNTYFP</th> <td>013</td> </tr>
<tr bgcolor="#E3E3F3"> <th>TRACTCE</th> <td>061045</td> </tr>
<tr bgcolor=""> <th>AFFGEOID</th> <td>1400000US04013061045</td> </tr>
<tr bgcolor="#E3E3F3"> <th>GEOID</th> <td>04013061045</td> </tr>
<tr bgcolor=""> <th>NAME</th> <td>610.45</td> </tr>
<tr bgcolor="#E3E3F3"> <th>LSAD</th> <td>CT</td> </tr>
<tr bgcolor=""> <th>ALAND</th> <td>41022523</td> </tr>
<tr bgcolor="#E3E3F3"> <th>AWATER</th> <td>102732</td> </tr>

I tried using left(), mid(), Instr(), and I'm not getting any text out. What I need is the 10 digit code called GEOID; in this example: 04013061045

The original layer was converted by the ArcToolbox from a KML. Here's the original KML: https://1drv.ms/u/s!AkwOsxuyxzoVmgvHu2RdkixTu18M

How do I address this?

  • I'd use split Python function by r"<\td> followed by [3] and slice. What I'd use never is installing yet another software – FelixIP Dec 12 '16 at 8:50
  • When you do the conversion, are you able to control the output, for example could you add an id or class attribute to the td element holding the value of interest like: <td class="geoidvalue">04013061045</td>? – nmtoken Dec 12 '16 at 13:31
  • Sticking with the use Python theme, you could use an XPath expression to get at your value – nmtoken Dec 12 '16 at 13:33

You can use the python regex module. Something like:

import re

def find_geoid(txt):
    result = re.search('<th>GEOID<\/th>\s+<td>(.*?)<\/td>', txt)
        return result.group(1)
        return '' # or 'not found' if you prefer
  • perfect. please give me a few hours to test it out. – arcee123 Dec 12 '16 at 18:48
  • Am I missing something? I put exactly as you stated, and then followed by find_geoid(<<Field>>) and it failed. Then I replaced function with def and that failed too. help? thanks much. This is where I want to go. – arcee123 Dec 13 '16 at 14:23
  • @arcee123 you're right, it should be def. Sorry, been doing too much javascript :-). How are you using the function? Is it in a code block, like this? desktop.arcgis.com/en/arcmap/latest/manage-data/tables/…. What error message do you see? – jon_two Dec 13 '16 at 16:56
  • ok. looks like I wasn't using codeblock. Give me a few hours again (got two jobs) and I'll see if I can fix it. Thanks. – arcee123 Dec 13 '16 at 18:18

I will recommend using QGIS instead of ArcGIS, as QGIS can handle KML files properly in terms of parsing attribute table. I tested your file using both ArcGIS and QGIS. ArcGIS gives all the data as tags exactly as ou described in your question, while QGIS opened KML file and parsed the the fields properly as you can see below:

enter image description here

You can save the the file as shapefile if you want by right-click on the layer in the table of content -> save as -> choose shapefile format from the drop-down menu. Also you can select wich fields you want to export to the shapefile.

One thing you need to take care about is that your area is a special area as it is located at East/West logitude lines. Some parts are in western side while others at eastern side as you can see in the above image which is due to the wgs84 projection. If that is not an issue, and only your concern is to extract the GEOID field, the above solution should work with you.

I used QGIS 2.18.0 for your reference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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