SearchCursor from source TEXT field set as <Null> being interpreted as ' ' (writing to DOUBLE)

Question

I am Curious about the behaviour of the SearchCursor on a server Database feature class. the data for some of the the fields I want to read is set as TEXT, with some of those fields values set as <Null>

I am using an insert cursor to write those fields to another database of which the equivalent field is set as DOUBLE.

The Source field names and target field names are variable and could be one field, could be 10+ fields (but always 1 to 1 mapping from source to target fields) depending on the DS/FC combo

the basis of the code that is doing the read from source and then write to target is this:

        TargetCursor = arcpy.da.InsertCursor(TargetFC,TargetFields) 
        
        #grab the row information from the source feature class and the equivalent mapped source fields (that should line up to the target fields)
        with arcpy.da.SearchCursor(SourceFC,SourceFields) as cursor:
            for row in cursor:
                print row
                TargetCursor.insertRow(row)
        del TargetCursor

The TargetFC is iterated from all feature classes within all Datasets. And the SourceFields and TargetFields are built for each specific target Featureclass so that they map when the insert cursor happens.

Mostly the source field types (however many there might be) are the same as the target field types, but there are a few instances where one of the source fields is a type TEXT and the Target is a type DOUBLE (Allows Null) But when the script runs the search cursor interprets Nulls in the source data as ' ' so when the insert cursor runs, it produces an incompatible field type error.

TargetCursor.insertRow(row) RuntimeError: The value type is incompatible with the field type. [CONFDATE]

Example:

SourceFields = [u'Shape@', u'DATA_SOURC', u'DESCRIPTIO', u'DATE_SOURC',
       u'DATE_DATA_', u'AMENDMENT_']

TargetFields = [u'Shape@', u'DATASRCE', u'DESCRIPT', u'SRCEDATE', u'CONFDATE',
       u'AMENDATE'

(note these Source and Target Field maps are specific for this loop of the overall DS/FC loop)

The last line that prints from the SearchCursor is this:

(<Polygon object at 0x46a9f10[0x104fd8c0]>, u'SP', u'SP172165ROADWAY', u'20170329', u' ', u'20170403')

So I can see that the second to last field in the source data (DATE_DATA_) is ' '

but if I look in the database, it is Null

Is there a way to read that Null from the source field and insert it as Null also into the target for where that scenario happens? Or alternatively insert the 0 into the target for that situation where a TEXT is going to a DOUBLE and the Source field is Null (or interpreted as ' ')?

Answer 1

I don't know if this is helpful or not, I tried to take on what @BERA and @Vince mentioned and highlighted but I didn't really know how to iterate within the fields of a row based on the field types at each section in the row.

I know my mapping spreadsheet is one to one. So I utilised that bit of knowledge and used the zip feature to compare the Source Field Mapping name, Target field mapping name and the specific field within the row.

I could determine the field type for the source and target fields because i knew the field names. So iterated through each of those FeatureClasses to get their respective field types

Then after that, could work out if a particular source field that was of type 'Text' with a entry when looking at the database (reported as a ' ' in the search cursor) was going to be inserted into the new database as target of 'Double'

I could then change that one particular field to a zero (0) and learnt along the way that I couldn't directly modify the row so had to create a list of the row, modify the particular field I was looking at (through way of a counter within the row) and then recreate the row again ready for the InsertCursor.

The Code looks like it is working but the loops have drastically slowed down the process from something that took 15 minutes to something that now takes many hours. But...it is doing things :)

#iterate through the target featureclass in the fields recognised from the mapping xlsspreadsheet
CursorTarget = arcpy.da.InsertCursor(TargetFC,TargetFieldsFromMap) # note arcmap 10.6 doesn't allow trspatial coordinate referencing in this step, arcgis pro does seem to allow it though

#grab the row information from the source feature class and the equivalent mapped source field (that should line up to the target fields)
with arcpy.da.SearchCursor(SourceFC,SourceFieldsFromMap) as rows:
    for row in rows:
       Counter = 0 #counter for iterating through the specific fields in a particular row so that i know what field index to modify if required
       for (a,b,c) in zip (row,SourceFieldsFromMap,TargetFieldsFromMap): #comparison of the row, and the two lists from the mapping sheet iterate through each part of the row
          SourceFCFields = arcpy.ListFields(SourceFC,b) #theListfields should only have the one field object in there based on the source field from the mapping table
          TargetFCFields = arcpy.ListFields(TargetFC,c) #theListfields should only have the one field object in there based on the target field from the mapping table
          SourceFCFieldType =''
          TargetFCFieldType =''
          for SourceFCField in SourceFCFields:
              SourceFCFieldType = SourceFCField.type #should only loop through onece to record the field type of the specific sourcefield recorded in regards to the mapping sheet)
          for TargetFCField in TargetFCFields:
              TargetFCFieldType = TargetFCField.type #should only loop through onece to record the field type of the specific targetfield recorded in regards to the mapping sheet)
          #print (a,b,SourceFCFieldType,c,TargetFCFieldType)
          if SourceFCFieldType == 'String' and TargetFCFieldType == 'Double' : #only come in here if there is a potential mismatch of Field Types
             if a ==' ' or a == '' or a is None:
                #change the value of the Field that is a string entry from an incompatible target vallue to one that the system will accept
                CopyOfTheRow = list(row)
                CopyOfTheRow[Counter] = 0
                row  = tuple(CopyOfTheRow)
                    
          Counter = Counter + 1 #iterate the counter within the specific row to determine which field index is being interrogated 
                 
                 
            
       print row
       CursorTarget.insertRow(row)
del CursorTarget

Don't know if this is useful for anyone or not or if there is a drastically more efficient way but it is something for someone potentially.