1

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?

enter image description here

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:

Expression:

testme(!ExcelTableFieldName!,!FeatureClassFieldName!)

Pre-logic script code:

def testme(x,y):
 if x is None:
  return y
 elif x == '':
  return y
 else:
  return x
  • You have no space between the '' signs in the elif statement? BTW you should add the code to your question instead of a screenshot of it. – BERA Oct 12 '16 at 17:15
  • Thanks Bjorn, I know in the calculator expression I can add multiple elif statements with ' ' , ' ', ' ', etc but I was hoping for a way to delete out all spaces entirely before the table is joined. I'm not sure what kind of script would be needed in this case though. – Dom B Oct 12 '16 at 17:28
  • @DomB Please edit your question to include your code snippet as text rather than as an image. – Midavalo Oct 12 '16 at 18:05
  • I believe this is the most elegant way of doing the same: stackoverflow.com/questions/2077897/… – fatih_dur Oct 13 '16 at 4:31
3

To remove spaces or tabs with Field Calculator using Python parser:

Expression:

removespaces(!FIELD!)

Code block:

import re    
def removespaces(x):
        return re.sub('[\s+]', '', x)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.