Change text values to NULL using update cursor

1297
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. I have users populate 'Null', 'NULL' or similar, and I need to convert these to true Nulls.

I have a list of the different permutations and a geodatabase with several feature classes. I'm trying to loop through my list and compare against all string values in all feature class. If there is a match, convert the string to the true Null. 

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:\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