Working with Microsoft Excel in ArcGIS Desktop

There are several questions that people often ask me about working with Excel data in ArcGIS: Can I export a table directly to Excel? Why are the attributes in my Excel table all null after creating a join? Why can’t I see my .xlsx file in the Catalog window? For those of you who have used Microsoft Excel data in ArcGIS, you know that there are some special conditions to do so. Those conditions include understanding the limitations and guidelines for working with Excel data. By the way, the answer to the first question is yes, of course you can export to Excel, and I will tell you how.

Working with Excel Spreadsheets

Excel acts like containers for worksheets and named ranges, which can be added to ArcMap like other tabular datasets. For those of you who don’t already know, a named range is when you select a subset of cells in an Excel worksheet and label it. In ArcMap, worksheets will have a $ at the end of their name, while named ranges will not. For example, the QuadrantOne range of cells can be added to ArcMap as a table:

 

Field naming guidelines for Excel worksheets

You can see from the named range graphic that I’ve made my column headings (field names) one-word names, like PercentContaminated and RemediationDate. There is a very good reason for this. Using a space in your field names in Excel can cause you problems when working with the data in ArcMap. What usually happens when a space is left in a field name is that the values for these fields can show up as Null in ArcMap (this will happen when you’re trying to join to an Excel table). There are several other field naming guidelines that everyone needs to know:

-Field names need to start with a letter.

-Field names should only include alphanumeric characters or underscores.

     – None of these: `~@#$%^&*()-+=|\,<>?/{}.!’[]:;

     – No spaces (That includes before the field name, in the middle, or after it.

-Field names will be cut off after 64 characters

-Do not used reserved words as field names.

 If you follow these guidelines, you will have a lot more success when using Excel data in ArcMap.

Supporting XLS and XLSX files

Have you ever shared your Excel data with colleagues and had them come back to you saying they can’t see it in ArcCatalog or the Catalog window? ArcGIS can read both XLS and XLSX files, but you may need to install an additional driver on your machine, depending on what version of Microsoft Excel you are using.

  • If you have Office 2003 installed, you can read XLS files, but you will need to install 2007 Office System Driver to read XLSX files.
  • If you have Office 2007 installed, you can read XLS and XLSX files.
  • If you have Office 2010 installed, you can read XLS files, but you will need to install 2007 Office System Driver to read XLSX files.

Exporting directly to Excel

I know what you are thinking: it can’t be done. Let me further clarify. Exporting to Excel is not a core feature of the software; it is not something you can do out of the box. However, you can use Python to write to Excel, and there is a tool for you to do just that available from the Geoprocessing Script & Tool Gallery in the ArcGIS Resource Center. All you have to do is download the tool and unzip the toolbox, and just like that, you will be able to export directly to Excel using the Table to Excel script tool.

Kent Marten 

This entry was posted in Uncategorized and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

11 Comments

  1. tromito says:

    Hi, until I can have many records in my table to display in Excel

  2. tromito says:

    Hi, until I can have many records in my table to display in Excel??

  3. johns says:

    Why does ArcCatalog with an Office 2010 system need the 2007 driver to see xlsx files which are Excel 2010′s default extension?

  4. johnmtaylor says:

    [quote]If you have Office 2010 installed, you can read XLS files, but you will need to install 2007 Office System Driver to read XLSX files.[/quote]

    This is incorrect. xlsx is natively supported in 2010.

  5. esrikmarten says:

    [quote]This is incorrect. xlsx is natively supported in 2010.[/quote]

    This is correct because I’m talking about ArcGIS 10, not Office 2010.

  6. stsnider says:

    I installed the 2007 Office System Driver, but Excel 2010 files will still not work with ArcGIS 10 tools. In ArcGIS 9.3.1, I can use Excel 2010 files just fine….even without the driver installed.

  7. stsnider says:

    Actually, it is only the Make XY Event tool that doesn’t work with Excel 2010 (for me at least). The Add XY data function in ArcMap does work. The only problem is that the Make XY Event tool is a process I can call in an ArcGIS Model or python script.

  8. zuogis says:

    Yes, I downloaded and installed 2007 Office System Driver as what you said, but I dont find out any XLSX file when I click “Add xy data…”. what is the problem?

  9. StacyRendall says:

    Updated version of the Table to Excel tool for ArcGIS 10 is located here:
    http://forums.arcgis.com/threads/35206-ArcGIS-10-export-to-Excel

  10. atmonterrosa says:

    Is there a similar tool that works for ArcGIS 10? It does not work with my version and the only option that i get is to save it as a csv format. Thanks.

  11. matthias1983 says:

    Hallo,
    I have found this tool: GISconnector for Excel
    With it you can transfer data between Excel and ArcGIS; also export attribute data to Excel and Excel data to tables.
    http://www.gis-connector.com