I have developed a model that imports an excel sheet and converts it into a table. It then joins values within this table to a particular feature class. I then use the calculate field tool to copy over the values in the table to the corresponding field in the Feature class. Only excel rows with values in each column of the excel table are copied over, if there is no value in the excel cell it does not copy over and leaves the original value that was in the feature class field (I'll attach image below). The issue is, many of the excel tables I've received are from multiple users and there are many instances of cells that are "blank" but there are spaces inserted in these areas in the sheet. The GIS see's these "blank" spaces with spaces as string values and will copy them over into the feature classes field it is joining. I need a front end process that will look at each excel spreadsheet first and delete out the spaces from cells that contain only spaces with no other actual value.
Is there any quick python script I can add into the model first to do this or to run after the excel is converted to a table?
This is a snippet of the model itself. The excel to table imports the particular sheet in the excel workbook and then joins it to the resulting feature class. The calc fields then copy over updated values from the spreadsheet to each of the feature classes corresponding field. At the end of the process, the imported fields from the excel sheet that were joined are then deleted. This is basically just a way to regularly update the feature classes as updates are made in the excel worksheets.
Here is the code I have so far for the calculator expression:
Pre-logic script code:
def testme(x,y): if x is None: return y elif x == '': return y else: return x