3

In the following Python Script, I would like to create a spreadsheet (Excel would be fine) with the Exception results instead of just printing "There was an error--->..." to be able to keep track of all of the errors that occurred (if any).

Any suggestions?

#import system modules 
import arcpy, os

#Set environment options 
arcpy.env.workspace = r'Z:\project\....gdb'

# Calculate field with arcpy.da.UpdateCursor
for fc in arcpy.ListFeatureClasses():
    try:
        with arcpy.da.UpdateCursor(fc, ["Depth", "OD"]) as cursor:
            for row in cursor:
                row[0] = row[0]*(-1)                    
                row[1] = row[1]*(-1)                    
                cursor.updateRow(row)
                print "Working on {0}".format(fc)

    except Exception as ex:
        print "There was an error --->    {0}".format(ex)

print ("Add Field Finished")

4 Answers 4

3

I like to use the openpyxl module to write excel files. It's super easy once you get the hang of it. Your code would look something like this -

    #import system modules 
    import arcpy, os
    from openpyxl import Workbook

    #Set environment options 
    arcpy.env.workspace = r'Z:\project\....gdb'

    #Create Workbook & worksheet
    wb = Workbook(optimized_write=True) # I always use the optimized_write version of the writer. Probably don't need it here, but I'm more familiar with this way. Doesn't make much of a difference.
    ws = wb.create_sheet() 

    #Excel header
    ws.append(["Feature_Class", "Processing_Result", "Error"]) # Once the worksheet is created, just throw lists at it, and it will write each value into a column, row after row

    # Calculate field with arcpy.da.UpdateCursor
    for fc in arcpy.ListFeatureClasses():
        try:
            with arcpy.da.UpdateCursor(fc, ["Depth", "OD"]) as cursor:
                for row in cursor:
                    row[0] = row[0]*(-1)                    
                    row[1] = row[1]*(-1)                    
                    cursor.updateRow(row)
            ws.append([fc, "Success"])
        except Exception as ex:
            ws.append([fc, "Failure", unicode(ex)])

    wb.save('File Name goes here')

The nice thing about writing your error message this way, is that you don't have to worry about writing unicode to the console, which is never fun. Excel will handle your encoding just fine.

5
  • Where does the file get saved to? Or "File name goes here" is the full path of where I want to save it?
    – Mat S
    May 8, 2015 at 19:54
  • 1
    Yeah, replace that string with the file path and file name of where you want it. So like wb.save('C:\\Users\\Mwrenn\\Desktop\\ErrorReport.xlsx')
    – MWrenn
    May 8, 2015 at 19:56
  • I tried the script and got a import error for openpyxl. I assume I have to install that module? @MWrenn
    – Mat S
    May 8, 2015 at 20:13
  • yes, easy_install or pip should do it.
    – MWrenn
    May 8, 2015 at 20:21
  • I finally had time to try your method and I got a SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated\UXXXXXXXXX escape. The left "(" in wb.save('File Name goes here') is highlighted.
    – Mat S
    May 13, 2015 at 20:09
2

My favorite way to get data into excel while running an arcpy script is to simply print out that which I wish to have in excel, with tabs separating my values. Then I can copy/paste what is created from the shell into excel. First I print a header, with mycolumn names separated by tabs, and then as I iterate through my for loops I print the lines of information.

Something like this:

#import system modules 
import arcpy

#Set environment options 
arcpy.env.workspace = r'Z:\project\....gdb'

#Excel header
print ("Feature_Class\tProcessing_Result\tError")

# Calculate field with arcpy.da.UpdateCursor
for fc in arcpy.ListFeatureClasses():
    try:
        with arcpy.da.UpdateCursor(fc, ["Depth", "OD"]) as cursor:
            for row in cursor:
                row[0] = row[0]*(-1)                    
                row[1] = row[1]*(-1)                    
                cursor.updateRow(row)
        print (fc + "\tSuccess")
    except Exception as ex:
        print (fc + "\tFailure\t" + str(ex))

You could also write this information to a text file and give the text file the .tab extension. This tells excel that your data is tab-delimited.

3
  • Everything runs perfect until I think I get a failure. I get this error when I run: "Traceback (most recent call last): File "G:\Scripts\WLS\Change Field Value Single Field in Multiple Feature Classes with Excel Output.py", line 19, in <module> print fc + "\tFailure\t" + ex TypeError: coercing to Unicode: need string or buffer, exceptions.TypeError found"
    – Mat S
    May 8, 2015 at 14:09
  • 1
    Whoops. Just need to convert the error to a string. I've updated the code. May 8, 2015 at 15:08
  • Thanks for the edit. That worked great. Don't know if it is necessary but I put all "print" in () as Spyder didn't like it without. The .tab trick worked great as well, just had to choose Excel as the default program to open. Inside, a simple sort keeping the headers gave me any errors I had. Very useful on large datasets!
    – Mat S
    May 8, 2015 at 19:37
1

Another option, not sure if it's practical for your application, but if you want to create formatted and fancy excel sheets you could invest a little time in learning the xlrd and xlwt python modules. You can pip install or easy_install them.

There is a bit of a learning curve, but they have worked well for me to create nice-looking report/summary spreadsheets from GIS data that others can open in Excel.

Otherwise, the .tab (or even just a .csv) are great suggestions.

8
  • Thanks @mr.adam, I did check out github.com/python-excel but that looked a little to involved for this current moment. I'll dig deeper when I get a chance.
    – Mat S
    May 8, 2015 at 18:25
  • 2
    Note: If working with ArcGIS 10.2 or greater, xlwt and xlrd are installed by default.
    – DWynne
    May 8, 2015 at 18:48
  • That's cool, I didn't know that. Thanks for pointing it out!
    – mr.adam
    May 8, 2015 at 19:19
  • 1
    Yes. I just tested on a machine that has 10.2 and no manually installed python packages, and like DWynne says, xlrd and xlwt are already on there, and importing them works just like arcpy or any other python module.
    – mr.adam
    May 8, 2015 at 20:10
  • 1
    @MatS. Yes. They were added to support the Excel to Table and Table To Excel geoprocessing tools, and so are there for anyone else to use as well.
    – DWynne
    May 8, 2015 at 21:00
0

One option would be to:

  1. Create a list and append exception record ID's to list (unique ID column/value)
  2. After the update cursor runs, make a feature layer of the fc
  3. Use a search cursor and iterate through each feature while looping over the list from step one and perform a select layer by attribute (add to selection method) all the exception features
  4. Once you have all exception features selected use the, this will only create an xls for those selected features
1
  • Thanks for that suggestion but that seems way to complicated for what I am trying to do.
    – Mat S
    May 8, 2015 at 20:15

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.