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
Commenting is not enabled for this article.