arcpy calculate field expression

2921
7
Jump to solution
03-13-2018 04:11 AM
RebeccaWatson1
New Contributor III

I am trying to create a file path and add it to a field by concatenating the path with a file name that is held in a field in my data.My path is a constant \Links\Sheets\Project\ and my Drawing_Name field has values like Drawing01.pdf, Drawing02.pdf etc. 

I keep getting the error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Failed to execute (CalculateField).

The function is written like this...

# Process: Calculate Field
arcpy.CalculateField_management(Centroids, "URL", expression, "PYTHON_9.3", "")‍‍‍‍

 And for my expression I want something like one of these...

expression = "\"\\\\Links\\\\Sheets\\\\Project\\\\\"+ !Drawing_Name!"

expression = "\"\\Links\\Sheets\\Project\\{0}\".format( !Drawing_Name! )"‍‍‍‍‍

I want to end up with \Links\Sheets\Project\Drawing01.pdf in the field.

The first expression used to work, but seems to throw the error above now - I believe it is my punctuation which is the problem - can anyone help?

Thanks

Rebecca

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

Although using the Field Calculator sounds like the easiest way of solving this, in many cases it is more complex due to double interpretation/parsing of the expression. I would seriously recommend you to use a cursor, like the one below:

import arcpy
import os

fc = r'your path to the fc or table'
fld_out = "URL"
fld_in = "Drawing_Name"
url = r"\Links\Sheets\Project"

with arcpy.da.UpdateCursor(fc, (fld_in, fld_out)) as curs:
    for row in curs:
        if row[0]:
            row[1] = os.path.join(url, row[0])
            curs.updateRow(row)

View solution in original post

7 Replies
NeilAyres
MVP Alum

Ever thought of using "/" instead. Too many back slashes... And those "" look a bit off.

RebeccaWatson1
New Contributor III

Thanks for your reply - I believe that I have to use \ for windows paths? Do you have any more pointers on how the " look off? I have tried various combinations of single and double quotes but with no luck.

0 Kudos
RebeccaStrauch__GISP
MVP Emeritus

try something like this

import os

myPath = r"\Links\Sheets\Project"
myPDFs = ['Drawing01', 'Drawing02', 'Drawing03']

for aPDF in myPDFs:
    pdfPath = os.path.join(myPath, "{0}.pdf".format(aPDF))
    print(pdfPath)

(I saw a flash that Dan just sent an answer...he might have a better way.  Haven't looked yet)

XanderBakker
Esri Esteemed Contributor

Although using the Field Calculator sounds like the easiest way of solving this, in many cases it is more complex due to double interpretation/parsing of the expression. I would seriously recommend you to use a cursor, like the one below:

import arcpy
import os

fc = r'your path to the fc or table'
fld_out = "URL"
fld_in = "Drawing_Name"
url = r"\Links\Sheets\Project"

with arcpy.da.UpdateCursor(fc, (fld_in, fld_out)) as curs:
    for row in curs:
        if row[0]:
            row[1] = os.path.join(url, row[0])
            curs.updateRow(row)
RebeccaWatson1
New Contributor III

Thank you - this has done the trick. You are right that the calculate field was just making the expression too complex. Stripping that out and using UpdateCursor instead has worked perfectly.

Thanks to everyone for their suggestions.

DanPatterson_Retired
MVP Emeritus

Use raw notation 'r'

Don't put a backslash at the end of a path you are concatenating

pth =r"c:\temp\somepath"
f = r"\my.pdf"
pth + r"\Links\Sheets\Project{0}".format(f)
'c:\\temp\\somepath\\Links\\Sheets\\Project\\my.pdf'

# ---- or....

r"{}\Links\Sheets\Project{}".format(pth, f)
'c:\\temp\\somepath\\Links\\Sheets\\Project\\my.pdf'

you don't need the 0, 1 etc in the {} if they are in order

DanPatterson_Retired
MVP Emeritus

for the future (ie PRO) 

f-strings do make some things look tidy

fld_in = "Drawing_Name"
url = r"\Links\Sheets\Project"

f"{url}\{fld_in}"  # ---- f-string, sort of like "stuff".format(*args)

'\\Links\\Sheets\\Project\\Drawing_Name'