topic Re: arcpy.da.Walk to not read excel worksheets in Python Questions

arcpy.da.Walk to not read excel worksheets

AmyKlug — Sat, 11 Dec 2021 12:32:10 GMT

My walk code will not skip reading the worksheets inside excel files. Staff have excel files with a huge amount of worksheets that is slowing my walk down so that it is basically unusable. I think it is still reading the excel worksheets in the else statement.

import arcpy, os, traceback, sys
arcpy.env.overwriteOutput = True
workspace = r"C:\Users\Documents\GisData"
arcpy.env.workspace = workspace
try:
    walk = arcpy.da.Walk(workspace)
    txt = open(r"C:\Users\Documents\StaffGISLibrary.txt", 'w')
    for dirpath, dirnames, filenames in walk:
        if arcpy.Exists(dirpath):
            #describe = arcpy.Describe(dirpath)
            if dirpath.endswith(('.xls', '.xlsx', '.txt')):
                print "skipping excel file"
                pass
            else:
                for filename in filenames:
                    fullpath = os.path.join(dirpath, filename)
                    describe = arcpy.Describe(fullpath)
                    print "writing " + fullpath
                    txt.write(fullpath + "," + filename + "," + describe.dataType + "\n")
        else:
            print "DOES NOT EXIST"
            pass
    del filename, dirpath, dirnames, filenames
    txt.close() 
except Exception, e:
    pass
    # If an error occurred, print line number and error message
    import traceback, sys
    tb = sys.exc_info()[2]
    print "Line %i" % tb.tb_lineno
    print e.message
finally:
    raw_input("Finished!")

Re: arcpy.da.Walk to not read excel worksheets

DanPatterson_Retired — Wed, 15 Jun 2016 21:22:25 GMT

just a hunch, shouldn't it be the filenames that you are looking for the xls extensions not the dirpath?

Walk—Help | ArcGIS for Desktop

Re: arcpy.da.Walk to not read excel worksheets

AmyKlug — Wed, 15 Jun 2016 21:24:56 GMT

filenames goes into the worksheets themselves, was trying to avoid that

Re: arcpy.da.Walk to not read excel worksheets

DanPatterson_Retired — Wed, 15 Jun 2016 21:26:15 GMT

I would throw in some print statements then, or use os.path to exclude excel files, although they should essentially work the same

Re: arcpy.da.Walk to not read excel worksheets

AmyKlug — Wed, 15 Jun 2016 21:35:38 GMT

I got to where I am by using print statements. the code works perfectly until I add in one of the large excel files (large amount of worksheets and records). it's not printing the excel files but it's still reading them in the else statement because my cursor stalls. Hope that makes sense. It's like dirpath or filenames is being pulled from the first part of the code before I excluded the excel files in the else statement.

Re: arcpy.da.Walk to not read excel worksheets

DanPatterson_Retired — Wed, 15 Jun 2016 22:31:05 GMT

dirpath isn't that the directory path

I would have put the excel check in the filename section or exclude it as a data type in the datatype section or provide an inclusion list of files you wish to examine

Re: arcpy.da.Walk to not read excel worksheets

AmyKlug — Wed, 15 Jun 2016 22:34:21 GMT

Sorry for wasting everyone's time. it was just slow and I assumed it was stalling. guess I need to be more patient.

Re: arcpy.da.Walk to not read excel worksheets

AmyKlug — Wed, 15 Jun 2016 22:41:46 GMT

I originally had it in the filename section but it had to go in and read all the worksheets. when I changed that it worked much faster. I like the inclusion idea but not sure how to do that with file geodatabases and the feature classes within.....

I suppose I would need to run a "list" this or that................

Re: arcpy.da.Walk to not read excel worksheets

AlexanderWinz — Thu, 16 Jun 2016 09:12:37 GMT

Hey Amy,

have you tried os.path.exists() instead of arpcy.exists(). This should work way faster 🙂

Re: arcpy.da.Walk to not read excel worksheets

AmyKlug — Thu, 16 Jun 2016 15:23:51 GMT

I think I am going to move over to os.walk too, arcpy.da.walk errors "does not exist" for too many files, over 50 percent.

thanks for the tip for the os.path.exists()

Re: arcpy.da.Walk to not read excel worksheets

JoshuaBixby — Thu, 16 Jun 2016 16:01:02 GMT

The simplest fix is changing line 13 from pass to continue. You are using the wrong control flow statement. See More Control Flow Tools.

Re: arcpy.da.Walk to not read excel worksheets

AmyKlug — Thu, 16 Jun 2016 16:08:12 GMT

I tried it with continue too, same result

Re: arcpy.da.Walk to not read excel worksheets

AmyKlug — Sat, 11 Dec 2021 12:32:13 GMT

Why would this print statement not work? (EOF error when using "\")

    for dirpath, dirnames, filenames in walk:
        for d in dirnames:
            for f in filenames:
                print dirpath + d + "\" + f
    del d, f, dirpath, dirnames, filenames

Re: arcpy.da.Walk to not read excel worksheets

JoshuaBixby — Sat, 11 Dec 2021 12:32:15 GMT

I was hoping the "simplest" fix would work. After seeing your reply and thinking about it, I realized why it doesn't work, or at least why your code is still slowing down when coming across Excel files.

As the ArcPy Data Access Walk documentation states, the standard method of forgoing a subworkspace is to modify the directory names list in place before the function starts stepping down into them.

When topdown is True, the dirnames list can be modified in-place, and Walk() will only recurse into the subworkspaces whose names remain in dirnames. This can be used to limit the search, impose a specific order of visiting, or even to inform Walk() about directories the caller creates or renames before it resumes Walk() again. Modifying dirnames when topdown is Falseis ineffective, because in bottom-up mode the workspaces in dirnames are generated before dirpath itself is generated.

Something along the lines of:

>>> walk = arcpy.da.Walk(workspace)
>>> for dirpath, dirnames, filenames in walk:
...    for dir in dirnames[:]:
...        if dir.endswith(('.xls', '.xlsx', '.txt')):
...            dirnames.remove(dir)
...    for filename in filenames:
....

Make sure to iterate over a copy of dirnames (as done by dirnames[:] ) or modifying the list in place won't work.

Re: arcpy.da.Walk to not read excel worksheets

JoshuaBixby — Thu, 16 Jun 2016 17:13:46 GMT

Backslashes are escape characters in Python. Since you aren't escaping the escape character, you are likely creating a special character with one of your file names and causing an issue. The safer approach when building file system paths is to use Python's os.path functionality.