2

(I've edited this question to reflect some of the answers/insights from below...)

Original(ish) Question:
I'm wondering if someone can share a bit of insight (or actual code) on how exactly to export tables from ArcGIS 10.1 to an .xlsx spreadsheet (Excel 2010), specifically using the xlsxwriter python module.

(If you have a suggestion other than xlsxwriter, that's welcome, too, as long as it creates files with the .xlsx extension).

Some answers simplified from below:
If you have ArcGIS 10.2+ there are built-in Table To Excel tools.

If you aren't interested in the code, but just want an ArcToolbox script that works at the push of a button (if you aren't already using 10.2+), use this awesome Excel and CSV Conversion tool that includes a Toolbox in the .zip file.

Otherwise, if you're interested in the code itself, see below for some code specific to xlsxwriter that works. But note, "XlsxWriter is designed only as a file writer. It cannot read or modify an existing Excel file." (per the xlsxwriter FAQ's)

Check out some other Excel conversion modules for more options. Openpyxl seems like a good option for reading & writing.

  • Do you already have XlsxWriter as part of your python package? – GeoJohn Jun 5 '15 at 15:57
  • 2
    As a note, in 10.2 the excel tools inside ArcMap actually use those python modules. – KHibma Jun 5 '15 at 16:10
  • 1
    @GeoJohn Yes, I do. I added it to the lib folder. – Kristen G. Jun 5 '15 at 16:40
3

Here's a full version of the final code that Kristen posted, plus some editing... not a new answer, but a shorter one. It's untested so there could be some rogue variable names. It will work with tables that have up to 702 fields. I also removed the copy rows procedure, because it seemed unnecessary with the code that you have here. Just add that back in if there are other operations that you needed to copies of the tables for.

import arcpy, xlsxwriter, string
from arcpy import env

# Output directory 
outDir = r'I:\REMOVED_FOR_PRIVACY'

# ----------------------------------------------------------------------
# Look at MXD, make a list of query layers and temporarily save them in a GDB
# ----------------------------------------------------------------------
print "Opening MXD and looking at query layers..."

# Define the Map Doc
mxd = arcpy.mapping.MapDocument("I:\REMOVED_FOR_PRIVACY\MyMap.mxd")

# ----------------------------------------------------------------------
# Formulas for getting data from tables and posting it into Excel docs
# ----------------------------------------------------------------------
print "Preparing Excel docs..."

## here's a version that will work up to 702 fields (A->ZZ)
def getColumnList(fieldList):
    num_fields = len(fieldList)
    columnList = []

    ## this makes a dictionary of all letters, which looks like
    ## {0:"A",1:"B",etc.} so that letters[0] = "A"
    letters = dict(enumerate(list(string.ascii_uppercase)))

    ## iteration per multiples of 26 in num_fields
    ## i.e. if there are < 26 fields, this iteration will run once and i will = 0.
    ## read as: "for each number between 0 and the number of times 26 fits into the
    ## number of fields", + 1 so that it'll run if num_fields < 26
    for i in range(int(num_fields/26)+1):

        ## set the first position of the column name
        ## on the first iteration (which covers the first 26 fields) there is no prefix
        prefix = ""

        ## if we are on a second iteration (for fields 27 through 52), i = 1
        ## so we need to set prefix = "A".  use the letters dictionary, need to -1
        ## to correct for the +1 used in the range function.
        if i > 0:
            prefix = letters[i-1]

        ## now run through the numbers 0-25 and use them as keys for the letter
        ## dictionary.  actually, it would be better to use range(len(letters.keys()))
        ## to avoid hard coding the number 26 (just feels better)
        for l in range(26):

            ## at this point review what variables we have: if we're on the first
            ## multiple of 26, prefix = "", on the 2nd, prefix = "A", 3rd, prefix = "B", etc.
            ## l is an increasing integer between 0 and 25

            ## so if you just print prefix+letters[l] you'll get _A->_Z with the
            ## appropriate prefix, but it'll go through the whole alphabet each time

            ## so compare the increasing length of the columnList to the number of fields
            ## in order to stop adding column names at the appropriate time.
            if len(columnList)<num_fields:
                columnList.append(prefix+letters[l])
    return columnList 

# ----------------------------------------------------------------------
# Create Excel doc, format and write headers, write data from query tables
# ----------------------------------------------------------------------
print "Creating Excel docs..."

for table in arcpy.mapping.ListTableViews(mxd):

    ## actually, you should be able to get everything you need from the table view object
    ## (no need to use a Describe object at all)
    ## http://resources.arcgis.com/en/help/main/10.1/index.html#//00s300000017000000

    print "1: " + table.name
    print "2: " + table.datasetName
    ## if the tables will always have that period in the middle
    print "3: " + table.datasetName.split(".")[1]

    ## you can import os at the top of your script and then use those functions
    print "4: " + os.path.basename(table.dataSource)

    #desc = arcpy.Describe(table)
    #tableName = desc.baseName #just set this to whatever works best
    print tableName + '\n' 

    workbook = xlsxwriter.Workbook(outDir + '\\'+ tableName + '.xlsx')
    worksheet = workbook.add_worksheet(tableName)

    bold = workbook.add_format({'bold': True})

    ## no need for a special function, you can do it all in one line
    fieldList = [f.name for f in arcpy.ListFields(table)]

    columnList = getColumnList(fieldList)

    # Writing headers
    for index, field in enumerate(fieldList):
        worksheet.write(columnList[index] + "1", str(field), bold)

    # Writing data values
    with arcpy.da.SearchCursor(table, fieldList) as cursor:
        rowCount = 2
        for row in cursor:
            for index in range(len(fieldList)):
                column = columnList[index]
                worksheet.write(column + str(rowCount), str(row[index]))
            rowCount += 1

    workbook.close()

# ----------------------------------------------------------------------
# Finish the script
# ----------------------------------------------------------------------

print "Script Finished."
  • 1
    Holy crap, great! I tried so hard to figure out a way to do what you are doing in the getColumnList function! I would love a detailed explanation of how/why this works. I can't seem to wrap my head around it. I understand that you are essentially using the first "for" to get the "groups" i.e. groups of 26 columns, but after that I'm not seeing how the code returns the correct xlsx headings. Anyways, really excited about what you did here. If you can find time to comment on all the lines of that function, I would really appreciate it! – GeoJohn Jun 11 '15 at 16:33
  • 2
    Sure, I added a bunch of comments so you should be able to follow along... it was a good puzzle! Would be super easy if excel columns started as AA. – mr.adam Jun 11 '15 at 17:17
  • Another fantastic "answer"! I'm going line by line myself and have a bit to go, but something I am struggling with is not needing to make the table views permanent. Something strange happens with naming when I don't use CopyRows; it tries to name my Excel doc: "KGRADY".%Agency_Export_DFTA_2_11_1_2". When I use CopyRows, it gets it right and reduces the MXD table view name from "CC_Export_Agency_DFTA" to simply "DFTA." Any ideas? (And actually that's involving some code I added later on. If it would help to see it, I can post it)... – Kristen G. Jun 11 '15 at 17:43
  • ... cont'd. I was also getting some other errors regarding naming, such as "TypeError: argument of type 'TableView' is not iterable" and "TypeError: 'TableView' object is not subscriptable," all sort of making me think I just simply had to make those table views permanent... – Kristen G. Jun 11 '15 at 17:47
  • I guess it depends on where your original tables are and how they are named. Right now, you are getting the name from a describe object. resources.arcgis.com/en/help/main/10.1/index.html#/… I'd recommend adding a bunch of print statements. I'll put examples in the code. The errors in your second comment would come from A) a line like 'for row in table' (you can't iterate over a table like that) and B) 'ss = table[0]' (not subscriptable means you can't get use an index number on it) – mr.adam Jun 11 '15 at 18:18
6

I believe you want this toolset downloadable from the arcgis.com gallery, compatible (supposedly) with 10.0 and above (written by teampython):

Excel and CSV Conversion Tools

Also see the below esri blog (the internal link provided is broken; why I provided the link I think they're referring to above...in later versions, as you know, esri's packaged a similar toolset)...this is the blog link: Working with Microsoft Excel in ArcGIS Desktop

  • Ah, yes. I should have noted that I did find that tool earlier and have taken a look at it. I may end up using that after all. I'll update my question to reflect that find. – Kristen G. Jun 5 '15 at 16:48
  • Wow, I have to admit that I looked at the code for this tool and couldn't quite figure out how to do the specific thing I needed to do with it (loop through a bunch of query layer tables in an MXD and export them all at once), so I ended up writing code using xlsxwriter, which I felt I understood better (see answers/posts below). What I did not realize is that when you download this package it comes with a toolbox for ArcTooblox (!!), which I find pretty damn useful, especially if I use batch to run multiple tables at once. – Kristen G. Jun 11 '15 at 20:45
3

Here is a script tool that utilizes the xlsxwriter module to output a .xlsx of an attribute table. It is currently limited to 26 columns, but could easily be upgraded to do more with a little python knowledge. This was a fun one to write! Enjoy

import arcpy, os, sys
import xlsxwriter

def getFieldList(fc):
    fieldObjects = arcpy.ListFields(fc)
    fieldList = []
    for field in fieldObjects:
        fieldList.append(field.name)
    return fieldList

def returnFieldIndex(inFC, fieldName):
    fieldList = getFieldList(inFC)
    index = 0
    try:
        for fld in fieldList:
            if fieldName == fld:
                return int(index)
                index += 1
            else:
                index += 1
    except:
        arcpy.AddMessage("Error in returning field index.")

inTable = arcpy.GetParameterAsText(0)
outLoc = arcpy.GetParameterAsText(1)
outName = arcpy.GetParameterAsText(2)

column = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

fieldList = getFieldList(inTable)

fieldCount = len(fieldList)

arcpy.AddMessage(" ")
arcpy.AddMessage("Number of fields: " + str(fieldCount))
arcpy.AddMessage(" ")
arcpy.AddMessage(fieldList)
arcpy.AddMessage(" ")

workBook = xlsxwriter.Workbook(os.path.join(outLoc, outName + ".xlsx"))
workSheet = workBook.add_worksheet()

headingIndex = 0

for f in fieldList:
    workSheet.write(column[headingIndex] + "1", str(f))
    headingIndex += 1

with arcpy.da.SearchCursor(inTable, fieldList) as cursor:
    rowCount = 2
    for row in cursor:
        columnCount = 0
        for field in fieldList:
            fIndex = returnFieldIndex(inTable, fieldList[columnCount])
            workSheet.write(str(column[columnCount]) + str(rowCount), str(row[fIndex]))
            arcpy.AddMessage(str(column[columnCount]) + str(rowCount) + " -----> " + str(row[fIndex]))
            columnCount += 1
        rowCount += 1

workbook.close()

Another note worthy point... It's not the fastest way to do this...

  • 1
    @Kristen G. As I mentioned in the answer, this is going to be pretty slow if you have a large FC with many records. The Excel and CSV Conversion tools mentioned in the other answer might be the way to go if you're after speed, but if you were just wanting to see how it could be done then BAM! There it is. – GeoJohn Jun 5 '15 at 20:06
  • 1
    Wow, this is fantastic. Thank you so much. I'm going to take a good look at my data and at both of these scripts and then determine what's best to do. I'll report back! – Kristen G. Jun 8 '15 at 19:47
1

Here's what I came up with, and it works!! Thanks so much to everyone for posting. It was an enormous help.

I was writing this code specifically to export a bunch of query layer tables from an MXD into Excel documents. My ultimate goal, which I'll work on momentarily, is to turn this into a tool in ArcToolbox so that it works inside of an MXD at the push of a button.

If anyone has any ideas for how to pare down this code or for how to make something work more simply, I'd love to hear.

import arcpy, xlsxwriter, string
from arcpy import env

# Output directory 
outDir = r'I:\REMOVED_FOR_PRIVACY'

# Set workspace for temporary output tables
arcpy.env.workspace = r'I:\REMOVED_FOR_PRIVACY\DATA.gdb'
workspace = arcpy.env.workspace

# ----------------------------------------------------------------------
# Look at MXD, make a list of query layers and temporarily save them in a GDB
# ----------------------------------------------------------------------
print "Opening MXD and looking at query layers..."

# Define the Map Doc
mxd = arcpy.mapping.MapDocument("I:\REMOVED_FOR_PRIVACY\MyMap.mxd")

# Make a list of the query layers
tableViewsList = arcpy.mapping.ListTableViews(mxd)

# Make the query layers permanent to use in tools (they will be deleted later to avoid overwrite problems)
for table in tableViewsList:
    table = arcpy.CopyRows_management(table, table.name)

# ----------------------------------------------------------------------
# Formulas for getting data from tables and posting it into Excel docs
# ----------------------------------------------------------------------
print "Preparing Excel docs..."

# Get a list of fields from the table
def getFieldList(table):
    fieldObjects = arcpy.ListFields(table)    
    for field in fieldObjects:
        fieldList.append(field.name)
    return fieldList

# Creating a list of Excel columns (Note: this function is restricted to 26 columns (i.e. A->Z))
def getColumnList(fieldList):    
    n = 0
    alphaList = list(string.ascii_uppercase)
    fieldListLen = len(fieldList)
    while n <= fieldListLen:
        columnList.append(alphaList[n])    
        n += 1
    return columnList

# Generate field indexes (to be able to populate "rows" in Excel doc later on)
def returnFieldIndex(table, fieldName):    
    i = 0
    for fld in fieldList:
        if fieldName == fld:
            return int(i)
            i += 1
        else:
            i += 1

# ----------------------------------------------------------------------
# Create Excel doc, format and write headers, write data from query tables
# ----------------------------------------------------------------------
print "Creating Excel docs..."

tablesList = arcpy.ListTables()

for table in tablesList:

    fieldList = []
    columnList = []

    desc = arcpy.Describe(table)
    tableName = desc.baseName
    print tableName + '\n' 

    workbook = xlsxwriter.Workbook(outDir + '\\'+ tableName + '.xlsx')
    worksheet = workbook.add_worksheet(tableName)

    bold = workbook.add_format({'bold': True})

    getFieldList(table)

    getColumnList(fieldList)

    # Writing headers
    i = 0
    for field in fieldList:
        worksheet.write(columnList[i] + "1", str(field), bold)
        i += 1

    # Writing data values
    with arcpy.da.SearchCursor(table, fieldList) as cursor:
        rowCount = 2
        for row in cursor:
            columnList = []
            getColumnList(fieldList)
            columnCount = 0
            for field in fieldList:
                fIndex = returnFieldIndex(table, fieldList[columnCount])
                worksheet.write(str(columnList[columnCount]) + str(rowCount), str(row[fIndex]))
                columnCount += 1
            rowCount += 1

    workbook.close()

for table in tablesList:
    arcpy.Delete_management(table)

# ----------------------------------------------------------------------
# Finish the script
# ----------------------------------------------------------------------

print "Script Finished."
  • 1
    sure, I added some cleaner versions of the functions, and a version that will accommodate 702 fields (A->ZZ). I'd recommend learning more about Python list comprehension and slicing, dictionaries, and the enumerate() function. All of which are super helpful for condensing code. – mr.adam Jun 10 '15 at 19:02
  • 1
    @mr.adam Your edits are in the review queue at the moment. I choose to skip the review, because normally significant changes to someone else's code are rejected but I note you only inserted commented out lines. I would suggest the best approach in a situation like this would be to create a new answer that completely incorporates your proposed changes and eliminates the parts you want to replace. You can/would of course credit Kristen for the original and just call it out/describe it as an 'improved' version of her solution. – Chris W Jun 10 '15 at 19:21
  • 2
    yes, I wasn't trying to change the overall function, just pare down the code--really more of a code review anyway so this probably isn't the right place. Just got caught up in the excel column function... As per your suggestion, I'll post a new answer, so you can discard the edits. – mr.adam Jun 10 '15 at 19:34

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.