Excel Spreadsheets and ArcGIS

Source  by Tripp Corbin in ArcGIS Desktop

Microsoft Excel is an amazing tool that allows users to store information, perform calculations, link data, and a more. This tool is used by multiple professions and practices in support of day to day tasks to store critical and useful information. As a result GIS users often need to make use of data stored in Excel Spreadsheets to perform their daily duties.

While an Excel spreadsheet can act similar to a traditional database, it is not. A spreadsheet allows users to perform functions within, such as assign equations to individual cells or records, which are not allowed in a database. This additional functionality often causes problems when a GIS User tries to integrate spreadsheets into their workflows and analysis within Esri’s ArcGIS platform. To be successful, they must be aware of the limitations.

ArcGIS Desktop can read data contained in an Excel Spreadsheet if it is properly formatted. ArcGIS tries to treat a spreadsheet like a traditional database table. This means the spreadsheet must be formatted similar to a database table. If it is not, you will experience problems.

With a properly formatted spreadsheet, you are able to do just about anything you can do with a traditional database table in ArcGIS. This includes queries, geocoding, displaying x,y data, joins and relates. One thing you cannot is edit a spreadsheet from ArcGIS.

Excel files are shown in ArcCatalog and the Catalog Window in ArcMap. Each XLS or XLSX file acts like a database. You can expand them to see the individual worksheets and named ranges they contain as if they are individual database tables. Excel worksheets are always shown with a dollar ($) sign at the end of their name in the Catalog tree.

So how do I properly format an Excel Spreadsheet so it works in ArcGIS? Here are some pointers:

1. Remove all columns and rows that are not needed.

2. Make sure each column has a name

3. Ensure each column name is short

        A. Less than 10 characters is what I recommend

        B. 64 characters is the maximum

4. Make sure column names don’t have spaces or special characters (underscores are acceptable)

5. Make sure each column name starts with a letter

6. Values stored in the cells should not exceed 255 Characters

7. Remove title information from the top of the worksheets

8. Keep it simple. Less is better when working with spreadsheets in ArcGIS

 

So how does ArcGIS work with spreadsheets? As I mentioned ArcGIS assumes a spreadsheet is like any other table. It has rules it follows when you access a spreadsheet from within ArcMap or ArcCatalog. The first thing it does determine what will be used for field names. The first non-empty row in an Excel worksheet or named range of cells is used for the field names. In the example shown below, the third row is the first non-empty row and would used as the field names.

So what happens if the first non-empty row does not contain the field names? This is common with most spreadsheets. The first few rows are often used for title information. So what can you do?

The easiest thing to do is delete those rows if possible. If that is not possible, you can define a named range in Excel that references the data you want to treat as the rows and columns of a table. To define a named range, select the area containing the field names and data, and enter a name for this area in the box at the left side of the Excel Formula Bar as shown in the example below.

Named ranges make it easy to access specific data in Excel. You’ll frequently find that Excel files created by financial departments already have named ranges referencing the pertinent tabular data they contain.

So the basic concept to take away from this is that the simpler the spreadsheet the easier it will be to use in ArcGIS. Once you have it in ArcGIS, you can do pretty much anything with it that you can do with a normal database table.

Here is a link to even more information about this topic from Esri-http://www.esri.com/news/arcuser/0312/importing-data-from-excel-spreadsheets.html.