nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

ArcGIS 10 - export to Excel

StacyRendall1

The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcgisdesktop/archive/2010/08/10/Working-with-Microsoft-Excel-in-ArcGIS-Desktop.aspx and available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638ECB2FA9 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-of-the-ArcGIS.

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

ArcGIS10_tableToExcel.zip

Vind meer berichten getagd met

Accepted answers

All comments

dietmarjochum

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.

RebeccaStrauch__GISP

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

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

StacyRendall1

The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcgisdesktop/archive/2010/08/10/Working-with-Microsoft-Excel-in-ArcGIS-Desktop.aspx and available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638ECB2FA9 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-of-the-ArcGIS.

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...

StacyRendall1

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

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).

PhilLarkin

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_toolbar/028s000000p0000000/
[/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.

RobertStevens

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)

KristianBarrett

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

Thank you!

ChrisBrannin

The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcgisdesktop/archive/2010/08/10/Working-with-Microsoft-Excel-in-ArcGIS-Desktop.aspx and available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638ECB2FA9 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-of-the-ArcGIS.

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?

NLSriram

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.

AndrewJohnson

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

RyanMcNeil

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!

NilsLandmeyer

Hello,

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

regards

nils-demian

AntoninoMonterrosa

The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcgisdesktop/archive/2010/08/10/Working-with-Microsoft-Excel-in-ArcGIS-Desktop.aspx and available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638ECB2FA9 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-of-the-ArcGIS.

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.

JohnSpurlock1

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.

GeertOvermars

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! 🙂

StacyRendall1

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...?

KristenMilburn

Thank you! This tool is exactly what I was looking for.

ClancyBlack

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

ClancyBlack

Thanks for your reply. It worked just fine, not sure why I didn't try that before.

StacyRendall1

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.

ClancyBlack

Can anyone help me get this working in Arcmap? I know just enough programming to be dangerous but do not have a lot of experience with Python. I load the tool but it says there are no parameters, I assume when I load it I need to identify what the variables are? I tried running it directly in the python window and replaced all the "dataset" inputs with my dataset name. It still didn't work so I assume there is more I need to edit. I would prefer to be able to load it into a toolbox tool rather than running script in the python window.

Thanks for any help.

DaveDonley

Stacy,

Thank you for a prompt reply. I will certainly give your suggestions a try, as soon as the weekend is over.

Keep up the great work!:cool:

Dave D.

StacyRendall1

Dave,

dates are actually stored in computers as days since 1900 (or sometimes 1901, I think), this is the number you are seeing. To show them as dates again you need to tell the program opening them that they are dates. So, if you open the output file in Excel, select the column and set format to date it should show in excel, and possibly when you open it in Arc again also. If that doesn't work, and you actually want to use the Excel file within Arc, you will probably need to make a new field of type date, and set it to the old field value, and it ought to convert.

DaveDonley

I downloaded and added the 'Export to Excel' model tool to a custom modelbuilder script. Great tool! Thank you!! I am not certain when or where I made the error, however; the XLS 'Date' column came across formatted as 'double', with unintelligible results (see attached). Any suggestions as to how I can have the original dates convert without alteration?

Thank you for your time and effort!

Dave Donley
CAL FIRE - RRU

Converted Dates.jpg

Original Dates.jpg

KarinHenrikson

Oh, I love this tool! And it also works with selected fetaures and keeps our swedish letters �? �? �? as they should look!
Thanks!:)
/Karin Henrikson
WSP Sweden

RobertThomson

THANKS! I've been needing something like this for a longtime.

MeredithTrapp

thank you, very helpful

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Alle Gemeenschappen
Recente Berichten
Gevolgde Inhoud
Community Hulp

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.