+ Reply to Thread
Results 1 to 27 of 27

Thread: ArcGIS 10 - export to Excel

  1. #1
    Stacy Rendall
    Join Date
    Jun 2011
    Posts
    321
    Points
    167
    Answers Provided
    16


    5

    Default ArcGIS 10 - export to Excel

    The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcg...S-Desktop.aspx and available here: http://resources.arcgis.com/gallery/...5-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/344...-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.
    Attached Files
    Last edited by StacyRendall; 07-20-2011 at 02:53 PM. Reason: formatting

  2. #2
    Meredith Trapp
    Join Date
    Jul 2011
    Posts
    5
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    thank you, very helpful

  3. #3
    Robert Thomson
    Join Date
    Oct 2010
    Posts
    17
    Points
    0
    Answers Provided
    1


    0

    Default Re: ArcGIS 10 - export to Excel

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

  4. #4
    Karin Henrikson
    Join Date
    May 2010
    Posts
    16
    Points
    1
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    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

  5. #5
    Dave Donley
    Join Date
    Apr 2010
    Posts
    2
    Points
    0
    Answers Provided
    0


    0

    Thumbs up Re: ArcGIS 10 - export to Excel

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version

Name:	Original Dates.jpg‎
Views:	961
Size:	75.8 KB
ID:	9766   Click image for larger version

Name:	Converted Dates.jpg‎
Views:	832
Size:	80.8 KB
ID:	9767  
    Last edited by djdonley; 10-27-2011 at 05:45 PM. Reason: modify attachments

  6. #6
    Stacy Rendall
    Join Date
    Jun 2011
    Posts
    321
    Points
    167
    Answers Provided
    16


    0

    Default Re: ArcGIS 10 - export to Excel

    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.
    Use the tools on the right hand side:
    if a reply answers your question
    ^ if a reply contains useful information (even if you didn't ask the original question)


    pythongisandstuff.wordpress.com

  7. #7
    Dave Donley
    Join Date
    Apr 2010
    Posts
    2
    Points
    0
    Answers Provided
    0


    0

    Cool Re: ArcGIS 10 - export to Excel

    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!

    Dave D.

  8. #8
    Clancy Black
    Join Date
    Aug 2011
    Posts
    6
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    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.

  9. #9
    Stacy Rendall
    Join Date
    Jun 2011
    Posts
    321
    Points
    167
    Answers Provided
    16


    0

    Default Re: ArcGIS 10 - export to Excel

    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.
    Use the tools on the right hand side:
    if a reply answers your question
    ^ if a reply contains useful information (even if you didn't ask the original question)


    pythongisandstuff.wordpress.com

  10. #10
    Clancy Black
    Join Date
    Aug 2011
    Posts
    6
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

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

  11. #11
    Clancy Black
    Join Date
    Aug 2011
    Posts
    6
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    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

  12. #12
    Kristen Milburn
    Join Date
    Apr 2011
    Posts
    3
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

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

  13. #13
    Stacy Rendall
    Join Date
    Jun 2011
    Posts
    321
    Points
    167
    Answers Provided
    16


    0

    Default Re: ArcGIS 10 - export to Excel

    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...?
    Use the tools on the right hand side:
    if a reply answers your question
    ^ if a reply contains useful information (even if you didn't ask the original question)


    pythongisandstuff.wordpress.com

  14. #14
    Geert Overmars
    Join Date
    Aug 2012
    Posts
    6
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    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!

  15. #15
    John Spurlock
    Join Date
    Sep 2012
    Posts
    4
    Points
    1
    Answers Provided
    0


    1

    Default Re: ArcGIS 10 - export to Excel

    Assuming you are using windows...
    Open a command prompt and browse to the folder where you unzipped the tar.gz
    For example:
    Code:
    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.
    Code:
    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.

  16. #16
    Antonino Monterrosa
    Join Date
    Feb 2011
    Posts
    2
    Points
    1
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    Quote Originally Posted by StacyRendall View Post
    The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcg...S-Desktop.aspx and available here: http://resources.arcgis.com/gallery/...5-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/344...-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.

  17. #17
    Nils Landmeyer
    Join Date
    May 2011
    Posts
    13
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    Hello,

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

    regards


    nils-demian

  18. #18
    Ryan McNeil
    Join Date
    Mar 2012
    Posts
    1
    Points
    1
    Answers Provided
    0


    1

    Default Re: ArcGIS 10 - export to Excel

    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:
    If no, create the XLS file with the exported sheet named for the input dataset and save the file;
    If yes, skip the creation step and go directly to adding the dataset sheet, and save the file.
    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!

  19. #19
    Andrew Johnson
    Join Date
    Mar 2012
    Posts
    2
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    Quote Originally Posted by ryan.a.mcneil View Post
    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:
    If no, create the XLS file with the exported sheet named for the input dataset and save the file;
    If yes, skip the creation step and go directly to adding the dataset sheet, and save the file.
    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

  20. #20
    N.L. Sriram
    Join Date
    May 2013
    Posts
    1
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

    Quote Originally Posted by StacyRendall View Post
    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.

  21. #21
    Chris Brannin
    Join Date
    Sep 2010
    Posts
    83
    Points
    0
    Answers Provided
    3


    0

    Default Re: ArcGIS 10 - export to Excel

    Quote Originally Posted by StacyRendall View Post
    The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcg...S-Desktop.aspx and available here: http://resources.arcgis.com/gallery/...5-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/344...-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?

  22. #22
    Kristian Barrett
    Join Date
    Jan 2010
    Posts
    1
    Points
    0
    Answers Provided
    0


    0

    Default Re: ArcGIS 10 - export to Excel

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

    Thank you!

  23. #23
    Robert Stevens
    Join Date
    May 2011
    Posts
    26
    Points
    0
    Answers Provided
    0


    0

    Unhappy Re: ArcGIS 10 - export to Excel

    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)

  24. #24
    Phil Larkin
    Join Date
    Nov 2012
    Posts
    6
    Points
    1
    Answers Provided
    1


    0

    Default Re: ArcGIS 10 - export to Excel

    For those running 10.1 this might be a good solution for you:
    The export to excel tool in the Water Utility Network Tools addin:
    http://www.arcgis.com/home/item.html...00b908b229acbb

    Help Link:
    http://resources.arcgis.com/en/help/...00000p0000000/

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

    Big thanks to StacyRendall for providing an alternative.

  25. #25
    Stacy Rendall
    Join Date
    Jun 2011
    Posts
    321
    Points
    167
    Answers Provided
    16


    0

    Default Re: ArcGIS 10 - export to Excel

    Quote Originally Posted by joincto View Post
    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:
    Code:
    worksheet = workbook.add_sheet(os.path.split(dataset)[1])
    You could either change the name of your input dataset or change above line to:
    Code:
    worksheet = workbook.add_sheet(os.path.split(dataset)[1][:31])
    which will truncate the name to 31 characters (by removing characters after the 31st).
    Last edited by StacyRendall; 11-05-2013 at 12:37 PM. Reason: Clarification
    Use the tools on the right hand side:
    if a reply answers your question
    ^ if a reply contains useful information (even if you didn't ask the original question)


    pythongisandstuff.wordpress.com

  26. #26
    Stacy Rendall
    Join Date
    Jun 2011
    Posts
    321
    Points
    167
    Answers Provided
    16


    0

    Default Re: ArcGIS 10 - export to Excel

    Does anyone have suggestions for tweaking the export feature to allow creation of multiple sheets within a single workbook? I
    Quote Originally Posted by werdna774 View Post
    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:
    1. read in the existing workbook (using xlrd)
    2. add the new sheet
    3. delete the old workbook
    4. 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.
    Use the tools on the right hand side:
    if a reply answers your question
    ^ if a reply contains useful information (even if you didn't ask the original question)


    pythongisandstuff.wordpress.com

  27. #27
    Stacy Rendall
    Join Date
    Jun 2011
    Posts
    321
    Points
    167
    Answers Provided
    16


    0

    Exclamation Re: ArcGIS 10 - export to Excel

    Quote Originally Posted by StacyRendall View Post
    The 'Export to Excel' model tool mentioned here: http://blogs.esri.com/Dev/blogs/arcg...S-Desktop.aspx and available here: http://resources.arcgis.com/gallery/...5-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/344...-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...
    Use the tools on the right hand side:
    if a reply answers your question
    ^ if a reply contains useful information (even if you didn't ask the original question)


    pythongisandstuff.wordpress.com

+ Reply to Thread

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts