We sometimes find datasets containing attribute data that we can’t use effectively because the data is not stored correctly. A common example is a field containing dates where the field type is defined as ‘Text’. This results in difficulties filtering the dataset by date, e.g. “display data older than X”. In this petroleum GIS tip we take the common example of an oil well dataset that has dates stored as text, and we’ll fix it by using ArcMap’s field calculator.
To begin with make sure you have write access to the dataset. If the dataset is read-only you can create a new copy by exporting right mouse clicking on the dataset name in the table of contents and selecting Data > Export Data.
Firstly a new Date field needs to be created for the spud date data in the dataset’s attribute table. To do this have the dataset as a layer in your ArcMap session and open the dataset’s attribute table by right mouse clicking on the datasets name in the table of contents and select Open Attribute Table. Click on the Options button at the bottom of the attribute table window and select Add Field.
In the dialog specify a name for the field which must be unique in the dataset (and if the dataset is a shapefile the field name length must be ten characters or les ). From the Type drop down list select Date.
To find the newly created field in the attribute table simply scroll to the end of the table (to the right). To reformat the original text date data we need to use the Field Calculator, which allows you to perform arithmetic calculations and string manipulation functions on attribute data. This tool uses functions that you may be familiar with from Microsoft Excel. It also has an option to enter VBA code for more powerful functions (with Python to be included in ArcGIS 10), but don’t worry – we’re not going to do any VBA here!
In the attribute table right mouse click on the field name (header) of the new field column that was created above and select Field Calculator in the context menu.
Select Type: String and double mouse click on the function Format( ) which will insert the reformatting function into the text box. Click inside the parantheses and then select the field from the fields list which contains the text formatted date values, which in my case is “SPUDDATE”. Double click this to move it into the expression.
Next, click in the text box so that your cursor is between the field name and the closing bracket. Type a comma and a double quote. Next specify the format of the text string in date code (e.g. 1975-03-31 will be YYYY-MM-DD, 02/12/1982 will be DD/MM/YYYY or MM/DD/YYYY if US style). Finish with a double quote so that it is similar to the text below (your field name and date style may well be different to mine):
Format ([SPUDDATE], “YYYY-MM-DD”)
Click OK and the Field Calculator will iterate through the records in the table translating the date data from text into the format you have specified.
You can now filter the dataset for features with particular dates. In my example I’m only interested in wells that were spudded after 1980, so I can build a Definition Query to do this, as shown below.
The dataset will now be filtered in both the map and the attribute table. Note I can easily remove this filter by deleting the query text in the Definition Query tab in the Layer Properties dialog.
Data management tasks often need to be repeated and in ArcGIS they can be automated so that users don’t have to do repetitive manual work. Exprodat has experience in creating customs scripts to alleviate the manual work for users so that the users can spend more time using the data instead of managing it. If you’re interested in our spatial data management services please contact us using the ‘More Information’ link at the bottom of this page.
Posted by Rob Clark, GIS Consultant, Exprodat.
