ArcGIS 10 - export to Excel

StacyRendall1 · ‎07-20-2011

The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcgisdesktop/archive/2010/08/10/Working-with-Microsoft-Excel-in-Arc... and available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638... doesn't work with ArcGIS 10, as it relies on the old arcgisscripting library. I have fixed it up to use the new arcpy package, attached.

Remember you will need to download and install the xlwt package, available http://pypi.python.org/pypi/xlwt, for Python to be able to export to Xls. If your Xlwt installation fails, check out my post in this thread: http://forums.arcgis.com/threads/34418-Is-it-possible-to-install-other-Python-distributions-on-top-o....

I have only tested it on one file, and haven't tested the CSV output, so let me know if you have any issues.

ChrisBrannin · ‎05-16-2013

The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcgisdesktop/archive/2010/08/10/Working-with-Microsoft-Excel-in-Arc... and available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638... doesn't work with ArcGIS 10, as it relies on the old arcgisscripting library. I have fixed it up to use the new arcpy package, attached.

Remember you will need to download and install the xlwt package, available http://pypi.python.org/pypi/xlwt, for Python to be able to export to Xls. If your Xlwt installation fails, check out my post in this thread: http://forums.arcgis.com/threads/34418-Is-it-possible-to-install-other-Python-distributions-on-top-o....

I have only tested it on one file, and haven't tested the CSV output, so let me know if you have any issues.

Is it possible to append multiple attribute tables into a single worksheet? Or will this re-write the worksheet each time it's called?

KristianBarrett · ‎05-22-2013

This script has saved me hours of trying to figure it out on my own.

Thank you!

RobertStevens · ‎05-29-2013

The tool did not immediately work for me.
I get:

Traceback (most recent call last):
File "C:\Users\rob\Documents\ArcGIS\RobToolBoxes\Scripts\tabletoexcel.py", line 85, in <module>
    export_to_xls(dataset_name, output_file)
File "C:\Users\rob\Documents\ArcGIS\RobToolBoxes\Scripts\tabletoexcel.py", line 58, in export_to_xls
    worksheet = workbook.add_sheet(os.path.split(dataset)[1])
File "C:\Python26\ArcGIS10.0\lib\site-packages\xlwt\Workbook.py", line 345, in add_sheet
    raise Exception("invalid worksheet name %r" % sheetname)
Exception: invalid worksheet name u'ACS_TransportGreaterPortland_byCBG'

Completed script TableToExcel...
Failed to execute (TableToExcel).
Failed at Wed May 29 12:02:05 2013 (Elapsed Time: 9.00 seconds)

PhilLarkin · ‎11-04-2013

For those running 10.1 this might be a good solution for you:

[INDENT]The export to excel tool in the Water Utility Network Tools addin:
http://www.arcgis.com/home/item.html?id=243e9945668c49cb8400b908b229acbb

Help Link:
http://resources.arcgis.com/en/help/localgovernment/10.2/index.html#/Water_Utility_Network_Reporting...
[/INDENT]

For those running 10.2 this functionality is offered in the conversion toolbox:

[INDENT]http://resources.arcgis.com/en/help/main/10.2/index.html#/Table_To_Excel/001200000054000000/[/INDENT]

Big thanks to StacyRendall for providing an alternative.

StacyRendall1 · ‎11-05-2013

The tool did not immediately work for me.
I get:

Traceback (most recent call last):
File "C:\Users\rob\Documents\ArcGIS\RobToolBoxes\Scripts\tabletoexcel.py", line 85, in <module>
    export_to_xls(dataset_name, output_file)
File "C:\Users\rob\Documents\ArcGIS\RobToolBoxes\Scripts\tabletoexcel.py", line 58, in export_to_xls
    worksheet = workbook.add_sheet(os.path.split(dataset)[1])
File "C:\Python26\ArcGIS10.0\lib\site-packages\xlwt\Workbook.py", line 345, in add_sheet
    raise Exception("invalid worksheet name %r" % sheetname)
Exception: invalid worksheet name u'ACS_TransportGreaterPortland_byCBG'

Completed script TableToExcel...
Failed to execute (TableToExcel).
Failed at Wed May 29 12:02:05 2013 (Elapsed Time: 9.00 seconds)

Seems like you can't have a sheet name longer than 31 characters, the name you used is 34 characters. The sheet name is derived from the dataset name, as you can see here in the Traceback:

worksheet = workbook.add_sheet(os.path.split(dataset)[1])

You could either change the name of your input dataset or change above line to:

worksheet = workbook.add_sheet(os.path.split(dataset)[1][:31])

which will truncate the name to 31 characters (by removing characters after the 31st).

StacyRendall1 · ‎11-05-2013

Does anyone have suggestions for tweaking the export feature to allow creation of multiple sheets within a single workbook? I

I am looking for a solution to this issue as well.

Is it possible to append multiple attribute tables into a single worksheet? Or will this re-write the worksheet each time it's called?

This is not a trivial change. Xlwt lets you write multiple sheets at one time. However to write sheets to the same workbook at different times you need to do the following:

read in the existing workbook (using xlrd)

add the new sheet

delete the old workbook

save the updated workbook with the old workbook name.

This is a known issue, and may not be worth the effort of the above workaround.

Your other option is to pass multiple tables to the tool in the first place, which lets it write all the tables at once to the same workbook before saving it. I am just not sure how to pass a bunch of tables to the tool; I think I remember seeing an option somewhere to have a list of tables/feature classes as a parameter input, but I cannot find it now. Alternatively you could define (say) 10 inputs, and have the tool ignore additional tables if no value is entered. This would be a pain if you wanted to do a larger number of tables, but may cover most bases. Let me know if you are interested in this, and I can code it up.

StacyRendall1 · ‎11-05-2013

The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcgisdesktop/archive/2010/08/10/Working-with-Microsoft-Excel-in-Arc... and available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638... doesn't work with ArcGIS 10, as it relies on the old arcgisscripting library. I have fixed it up to use the new arcpy package, attached.

Remember you will need to download and install the xlwt package, available http://pypi.python.org/pypi/xlwt, for Python to be able to export to Xls. If your Xlwt installation fails, check out my post in this thread: http://forums.arcgis.com/threads/34418-Is-it-possible-to-install-other-Python-distributions-on-top-o....

I have only tested it on one file, and haven't tested the CSV output, so let me know if you have any issues.

There is an issue with this tool if you have 64 bit background geoprocessing installed. The tool may not allow you to select XLS output, forcing CSV. This is because the Tool Validator class does not use the same Python as the geoprocessor, it will use the old 32 bit version which may not have xlwt installed and thus the tool thinks it cannot use xlwt. The simplest workaround would be to install xlwt to the 32 bit Python also...

dietmarjochum · ‎05-18-2015

My original dbf table has ca. 2mio. points. Excel loads just ca. 1.04mio. What can i do?

RebeccaStrauch__GISP · ‎05-18-2015

Most likely you are hitting the limit of Excel. Can you split your dbf in half and read in each half separately? Once you get them into a data set (assuming a file GDB) append them back together.

dietmarjochum · ‎05-21-2015

I figured out that Editor or Notepad etc. is able to load them all at once .... quick and easy to edit a txt file, which i needed for building a dtm in cad.