Uploading Null Values into Double-Type Field

Question

I have a dataset that I need to update into a feature layer that has several numerical fields (double-type). Some rows of my dataset are missing values for some fields and are blank, and this seems to be preventing all of the data from being uploaded to those Double-type fields. If I replace all the missing values with a number, I can upload the dataset just fine. But if the values are just blank, they can't be mapped to the numerical fields anymore. All the numerical fields say "Yes" for Allows Null Values.

So my question is, what does ArcGIS recognize as a numerical 'null' value? It seems strange to me that a blank field would be read as 'not a number'. I'd like to just manipulate the dataset in Excel prior to upload so that ArcGIS reads those values as 'null'.

My workaround so far has been to upload a particular number that would never appear in the dataset (for example, 123,456,789) to 'represent' Null, and then use a Calculation to replace all instances of that number with null values after uploading. But this is time consuming and has to be done for every field every time I upload new data. It'd be a lot easier if I could just know what ArcGIS recognizes as 'null' and replace all the blanks in the original dataset before I upload it.

Anyone know the answer to my question, or have a suggestion of how else to deal with this?

DanPatterson · Answer

Excel To Table (Conversion)—ArcGIS Pro | Documentation

is usually pretty good at producing a proper geodatabase table

zamerigian · Answer

Thanks for your response. I tried replacing all values with both #N/A and also tried using the NULL() function in Excel - this time it allowed me to map the column to the proper field, but gave me an error during the actual upload step.

I also tried explicitly formatting the columns Number instead of General, and it gave the same result.

This seems very strange, because why would they allow you to output a null value using a Calculation, but not upload a null value directly?

DanPatterson · Answer

Do you explicitly format your columns in Excel to numeric format?  Because the default is General which allows for a mix of numbers and text.

Your workaround is a good one since it attempts to ensure that null values are properly represented and any calculations that rely on that field are treated properly prior to use.

You can try the advice offered for Pro in this link

Excel To Table (Conversion)—ArcGIS Pro | Documentation

A value of "#N/A" in an input Excel cell will be converted to null. If the output table format (such as a dBASE table) does not support null, another value (0 for integer fields or an empty string for text fields) will be used.