CSISS - GIS Cookbook: Recipe - Importing an Excel table to your GIS project

GIS Cookbook: Recipe - Importing an Excel table to your GIS project

Keywords: Tables, Excel, linking, joining, data management
Category: Data Analysis
Software: ArcGIS 8/9.x

Problem: I have data in Excel format, how do I use it within my ArcGIS project?

Description: Often the data we work with does not come nicely packaged in a GIS data file. Instead we work with a variety of different data sources. In this recipe we will convert data from an Excel spreadsheet into a GIS-friendly format and then import them so that they can be used within your ArcMap map document.

Methodology:
1) Open Excel to the data file that you want to use in ArcMap. Bring up the exact data spreadsheet you would like to import.

2) Go to the File -> Save As menu option

3) Set the Save in: folder to a familiar directory and remember where you saved it.

4)Set the Save as type: to DBASE IV.

The Save As window

5) Set your file name appropriately and click Save.

6) A message will appear about multiple worksheets. If your data are in a worksheet other than Sheet1, be sure to select that sheet here. Otherwise, just accept the default Sheet1. (Note: If you have multiple worksheets in your Excel workbook, you will need to repeat steps 1-6 for each that you would like to import to ArcMap.)

7) Then Click Yes to the message regarding formats. The Data you selected is then written to disk as a dBase file.

8) Close Excel.

9) Open ArcMap. You can either import it to a new map document, or to a existing map document.

10) Use the Add Data button to add the dBase files to the map. If you do not know how to do this yet see the recipe How to Open a Map Document, then Add Data (ArcGIS 8.x/9.x).

11) Navigate to the folder where you saved the .dbf files. Select the file or files you would like to add, and click the Add Data button. If you receive an error message See Pitfall 1, or See Pitfall 2.

12) The DBase tables are displayed in your active data frame.
Pitfalls:

If your error message says that the file is busy or already in use, it means that you have left the .dbf file you are trying to import open in Excel. Make sure the file you are importing and preferably Excel are closed before you import the data.
If your error message says that the format is not accepted or ArcMap cannot read the format, there is an error either with your file type (e.g., wrong format) or there is an error within your data that prevents it from being imported (this may include extra spaces within columns, empty cells, and many other consistency issues) Try to make your data as neat as possible and try again.

Authored by: Benjamin N. Sprague Modified: 2/4/05

Copyright © 2002-2015 by Regents of University of California, Santa Barbara Cookbook: Ben Sprague, Ethan Sundilson, Carlin Wong, Sam Ying

Copyright © 2002-2015 by Regents of University of California, Santa Barbara
Cookbook: Ben Sprague, Ethan Sundilson, Carlin Wong, Sam Ying