ArcGIS PRO 2.2: How to change spaces to Nulls?

1423
5
11-02-2018 07:04 AM
BuckySwider
Occasional Contributor

ArcGIS PRO 2.2:  How to change spaces to Nulls?   

Hi all, running a canned project "Fire ServiceMaps" in which a task wants me to append my Street Centerlines file into the LocalGovernment model.  The target field is defined as a long integer that accepts null values.  However, it looks like my input file has some spaces in it so the append fails with ERROR 001156.  

I searched around trying to find a way to convert the spaces in my source to NULL values, but nothing I could get to work.  What is the best/easiest ways to search a column for blanks and replace with NULL in Pro 2.2?

Thanks.  

0 Kudos
5 Replies
JoshuaBixby
MVP Esteemed Contributor

Since you are talking about replacing blanks with NULL, I am assuming you are looking for columns with empty strings.  Do you just want to replace empty strings, "", or are you interested in replacing strings that only contain white space, e.g., "   "?

If this is just a once and done, and you don't care to script it, you can use Select By Attribute to select the rows, and then use the Field Calculator and set the Expression type to Python and value to None.  The Python None singleton gets converted to NULL when updating data in a table.

BuckySwider
Occasional Contributor

I believe I just want to change whitespace " " to nulls.  Will Select By Attribute allow me to select by column instead of rows?  There are other columns that have legitimate space characters. 

As Michael knows from my "main" thread for my task, I'm an ArcGIS newbie with some limited python knowledge, so I'm slowly feeling my way through to get run books created for my fire company.

Also as Michael points out, I may have a data type mismatch- not sure which situation is occurring.  I guess I should try one "fix" at a time so I can figure out what exactly the culprit is, as this will be an annual/biennial process....

0 Kudos
MichaelVolz
Esteemed Contributor

You say the target of the LGIM feature class is integer, but what is the field type of your input Street Centerline feature class?  What is the name of the target field?  This might not be the only field that you are going to have this issue with as the error MIGHT occur on the first instance it hits and then it exits the geoprocessing task without moving on to additional fields.

Did you get this data from the county that you are also trying to get address points from?  If so, then is does not look like they are using the LGIM for street centerlines, so they probably don't have address points in the LGIM either.

BuckySwider
Occasional Contributor

Hmmm, good point- the source table lists as TEXT.  So I wonder if the failure is on a Space character (as the 001156 text says " ") or on the datatype.  Or maybe both.  So maybe I have to somehow convert the entire column to long integer and any spaces will take care of themselves during that operation??? 

0 Kudos
LorneDmitruk2
New Contributor III

Try adding a new field as a long integer type and then calculate the value by using the int() function in the calculate field tool.

For example: int(!OldField!)

I would then select the rows  with spaces in old column and calculate them to None just to be sure.

Cheers 

0 Kudos