ArcGIS 10 - export to Excel

25161
29
07-20-2011 02:52 PM
StacyRendall1
Occasional Contributor III
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.
Tags (2)
29 Replies
ClancyBlack
New Contributor II
Stacy,

I sometimes will get an empty table when I use this tool. The column headings are there but no data. It seems to happen when I export a table that has a join to it, but even then it is not consistent. Any thoughts? I am exporting to csv, but I imagine the results would be the same for excel. Thanks.

Clancy
0 Kudos
KristenMilburn
New Contributor II
Thank you! This tool is exactly what I was looking for.
0 Kudos
StacyRendall1
Occasional Contributor III
Clancy,

no idea. I didn't write the tool - just updated it for Arc 10 (see first post)... You could try exporting the joined data to another feature class, loading that and exporting it...?
0 Kudos
GeertOvermars
New Contributor
I have difficulties installing the xlwt package. I'm a beginner with no knowlegde of Python, I have never used the Python Window.
According to the documentation I should do this to install the package:

Installation:
�?� Any OS: Unzip the .zip file into a suitable directory, chdir to that directory, then do "python setup.py install".
�?� If PYDIR is your Python installation directory: the main files are in PYDIR/Lib/site-packages/xlwt, docs are in the doc subdirectory.
�?� If os.sep != "/": make the appropriate adjustments.


To be honest, I don't understand this. Can someone explain this with a few more words, like a step-by-step guide?

Thanks in advance! 🙂
0 Kudos
JohnSpurlock1
New Contributor
Assuming you are using windows...
Open a command prompt and browse to the folder where you unzipped the tar.gz
For example:
cd c:\python26\xlwt-0.7.4\

Or if you are in Windows 7, browse to that folder, hold down shift and right click the folder, then select "Open command window here"

In the command prompt, type "setup.py install" without quotes and hit Enter.
C:\Python26\xlwt-0.7.4>setup.py install

The script should then install the libraries.

Now you can open the toolbox provided in ArcGIS and run the script like you would any other.
0 Kudos
AntoninoMonterrosa
New Contributor
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.


Hi Thanks so much, I followed the step but I 'm only option that I have is the csv output format and even when I tried it fails. Any suggestions?  Thanks.
0 Kudos
NilsLandmeyer
New Contributor
Hello,

in my excel table are all attribute columns displayed in one column. How can I keep the attribute columns separately?

regards


nils-demian
0 Kudos
RyanMcNeil
New Contributor
Does anyone have suggestions for tweaking the export feature to allow creation of multiple sheets within a single workbook? I'm building a model to export records that fall within a series of distance buffers and would like them to appear as individual sheets within a single XLS file for analysis, rather than having to work across multiple XLS files.

I've altered the Table to Excel code to set the sheet name to that of the input dataset to uniquely identify each sheet, but when I try to run the tool as a batch, the process returns an overwrite error and terminates. If I set the geoprocessing options to allow overwrite, the batch runs, but the resulting XLS file only has the sheet from the last dataset processed.

Given the alterations to the original input parameters I've described, could the code be modified to follow some variation of the process below?
Inspect the output directory for a file with the same name as the output filename parameter:
[INDENT]If no, create the XLS file with the exported sheet named for the input dataset and save the file;[/INDENT]
[INDENT]If yes, skip the creation step and go directly to adding the dataset sheet, and save the file.[/INDENT]

I'd imagine that with this modification, the script could be run once through all the datasets by incorporating some sort of "for...in..." iteration through the list of datasets, but I'd also like to incorporate something to prevent the overwrite problem if I come back and run the model with some new variables (i.e. I'd like them to be added as new sheets to the same "master" XLS file).

I've seen scripts that can collect multiple CSV files as sheets within a single XLS document, but it seems like a somewhat clunky workaround.

Any tips/tricks/pointers are appreciated!
0 Kudos
AndrewJohnson
New Contributor II
Does anyone have suggestions for tweaking the export feature to allow creation of multiple sheets within a single workbook? I'm building a model to export records that fall within a series of distance buffers and would like them to appear as individual sheets within a single XLS file for analysis, rather than having to work across multiple XLS files.

I've altered the Table to Excel code to set the sheet name to that of the input dataset to uniquely identify each sheet, but when I try to run the tool as a batch, the process returns an overwrite error and terminates. If I set the geoprocessing options to allow overwrite, the batch runs, but the resulting XLS file only has the sheet from the last dataset processed.

Given the alterations to the original input parameters I've described, could the code be modified to follow some variation of the process below?
Inspect the output directory for a file with the same name as the output filename parameter:
[INDENT]If no, create the XLS file with the exported sheet named for the input dataset and save the file;[/INDENT]
[INDENT]If yes, skip the creation step and go directly to adding the dataset sheet, and save the file.[/INDENT]

I'd imagine that with this modification, the script could be run once through all the datasets by incorporating some sort of "for...in..." iteration through the list of datasets, but I'd also like to incorporate something to prevent the overwrite problem if I come back and run the model with some new variables (i.e. I'd like them to be added as new sheets to the same "master" XLS file).

I've seen scripts that can collect multiple CSV files as sheets within a single XLS document, but it seems like a somewhat clunky workaround.

Any tips/tricks/pointers are appreciated!


I am looking for a solution to this issue as well. Ryan could you possibly post your code?

Thank you,

Andrew
0 Kudos
NLSriram
New Contributor
You shouldn't have to program anything. Extract the contents of the zip file to a location. Navigate the ArcCatalog window (in ArcMap) to that location, expand the toolbox - called "Additional Conversion - Generic Tools" - then double click on the "Table to Excel" function in there. The parameters are already set up.


Just started learning how to use ArcMap, could not figure out how to add this tool to the list of standard tools, or how to "navigate" to that location, would be grateful for help.  TIA.
0 Kudos