5

I'm using ArcDesktop 10.2. I have 30 x,y locations that have 20 fields of numeric data, stored in an Excel spreadsheet. Some cells have no data, and I want those cells to be represented as null values when I convert it from a spreadsheet to a feature. I keep getting 0's instead of < NULL > when I convert the Excel spreadsheet to a shapefile. The ArcGIS help website says that in version 10.2, they no longer support null values in shapefiles. When I try to change the field properties regarding "Allow Null Values", it will not let me change the setting from No to Yes.

It's critical for a statistical analysis later on that the null values remain and are not converted to 0's. Is there a way to create a feature class that allows null values? There are some true 0's in the dataset, so I cannot simply delete all the 0 values using the Editing Toolbar. I could compare the shapefile and the original spreadhsheet cell-by-cell and manually remove the 0's that are actually nulls, but I figure there must be a better way.

Improve this question
6
  • 5
    Shapefiles do not support NULL, only file/personal geodatabase (and Enterprise GeoDatabase). If you want NULL values you must make it a personal geodatabase feature class - the table is then accessible via Microsoft Access for other database operations (open read-only or a copy, it's too easy to break a geodatabase in Access). Also, you can import your excel spreadsheet into the geodatabase and it will show up as a table in Catalog - more robust for joining than the Excel links.
    – Michael Stimson
    Commented Nov 19, 2014 at 22:40
  • 3
    See this page regarding what GP tools writing to a shapefile will do with a null value, as it varies depending on data type. The short answer is pick a representative value for nulls that isn't in the data or is well outside the normal range, like 9999 or something. As Michael mentions you could also use a geodatabase where nulls are supported.
    – Chris W
    Commented Nov 20, 2014 at 1:34
  • That's another excellent tactic @ChrisW. Then find&replace in outputs and/or write your code to overlook the special value. I think that's good enough to be an answer that I could confidently vote for. I might add that NIMA products use 999 for other and 9999 for null values so it's got a precedent.
    – Michael Stimson
    Commented Nov 20, 2014 at 1:54
  • @MichaelMiles-Stimson Sometimes we get spoiled by the way things work (or can) now and forget how they used to work. I run into 'coded' nodata values all the time in distributed data, and rasters in particular. There's most definitely precedent for it.
    – Chris W
    Commented Nov 20, 2014 at 2:14
  • 1
    ArcGIS allows you to specify what a no data value is. That way when tools run they will ignore those cells or treat the value as no data, not the actual value or 0. See the help files. Note it may also be necessary to specify this value in either environment settings or tool parameters as well. Creating a geodatabase is pretty simple - in Catalog (program or ArcMap window), right-click a folder to create the gdb. Then just make sure you export your XY event layer to that gdb to create a feature class out of it.
    – Chris W
    Commented Nov 20, 2014 at 20:36

1 Answer 1

Reset to default
8

Shapefiles, which use the older dBase specs, do not support null values. If you must maintain null values and you have to keep the file format to shapefile, you'll need to use a representative or 'nodata' value for it. This can be any value you wouldn't normally encounter or expect to encounter in the data, or that even falls within valid data's range, such as 999, -9999, or -32678. Again, really any value that could only mean one thing (ie, a coded value).

Improve this answer

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.