Change text values to NULL using update cursor

859
2
Jump to solution
02-23-2022 04:15 PM
TigerWoulds
Occasional Contributor III

I am trying to write a script that will take different permutations of the word Null and convert them to true database NULLs. Sometimes I have users populate 'Null', 'NULL' or similar, and I need to convert these to NULLs.

I have a list of these different permutations and I want to loop through a GDB and compare all text values to the list, if the current value matches an item in the list, convert it to a true NULL.

But I am getting an invalid syntax error on line 26.

 

 

row = [None if i in nullList else pass for i in row]

 

 

If I replace pass with print ('Test') it changes every value to NULL. Any ideas?

import arcpy
import os
import time

inputGDB = r'C:\Users\neel.kumar\OneDrive - Syncadd Systems Inc\GIS\ArcGIS_Pro\AIA_Tools\Test_null.gdb'

# Set workspace to input GDB
arcpy.env.workspace = inputGDB

nullList = ['', ' ', '  ', '   ', '-', '--', '---', '<Null>', '<NULL>', 'Null', 'NULL', '”Null”', '”NULL”']

datasets = arcpy.ListDatasets(feature_type='feature')
datasets = [''] + datasets if datasets is not None else []

# Loop through all feature classes
print('Reading Feature Classes...')
for ds in datasets:
    for fc in arcpy.ListFeatureClasses(feature_dataset=ds):
        # Build a list of all TEXT/String Fields where NULL values are allowed
        fieldList = [i.name for i in arcpy.ListFields(fc) if i.type == 'String' and i.isNullable is True]

        # Iterate through each row and execute the clean
        with arcpy.da.UpdateCursor(fc, fieldList) as cursor:
            for row in cursor:
                row = [None if i in nullList else pass for i in row]
                cursor.updateRow(row)

print('Done.')

 

 

 

0 Kudos
1 Solution

Accepted Solutions
DanPatterson
MVP Esteemed Contributor
row = ['', 'a', ' ', '  ', '   ', '-', '--', '---', '<Null>', '<NULL>', 'Null', 'NULL', '”Null”', '”NULL”']

nullList = ['', ' ', '  ', '   ', '-', '--', '---', '<Null>', '<NULL>', 'Null', 'NULL', '”Null”', '”NULL”']

[None if i in nullList else pass for i in row]
  File "<ipython-input-7-9b1bf3f7cd3e>", line 1
    [None if i in nullList else pass for i in row]
                                   ^
SyntaxError: invalid syntax

# -- no pass

[None if i in nullList else i for i in row]

[None,
 'a',
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None]

... sort of retired...

View solution in original post

2 Replies
DanPatterson
MVP Esteemed Contributor
row = ['', 'a', ' ', '  ', '   ', '-', '--', '---', '<Null>', '<NULL>', 'Null', 'NULL', '”Null”', '”NULL”']

nullList = ['', ' ', '  ', '   ', '-', '--', '---', '<Null>', '<NULL>', 'Null', 'NULL', '”Null”', '”NULL”']

[None if i in nullList else pass for i in row]
  File "<ipython-input-7-9b1bf3f7cd3e>", line 1
    [None if i in nullList else pass for i in row]
                                   ^
SyntaxError: invalid syntax

# -- no pass

[None if i in nullList else i for i in row]

[None,
 'a',
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None,
 None]

... sort of retired...
TigerWoulds
Occasional Contributor III

Thanks! Replacing pass with i did the trick. 

0 Kudos