- Select a name for the dataset. The name will appear on the Arc menu. The default is to use the name of the spreadsheet.
- Add a short description on the data set. The description is put in the data file to document the data, and it is frequently printed by Arc. The default is shown in the text window for the dialog.
- Select the range of the spreadsheet to be included in the data set. The easy way to set this is to click left mouse button on the upper left-corner of the part of the spreadsheet containing the data, and drag across the data with the mouse button down. If this doesn't work, click the left mouse button in the text area of the dialog for the Data range, and try again.
- Decide if you want to create a data file or if you want the data sent directly to Arc. If you want the data to go directly to Arc, you must start Arc first. If you choose to save the data into a file, you will get a standard file dialog to select a file name.
Reading a Microsoft Excel Spreadsheet into Arc
Installation
Arc.xla is a Microsoft Excel add-in for Windows only (revised on March 7, 2002), that provides support for Arc, version 1.04 or newer. (If clicking on the above link does not automatically download the file, hold the right mouse button down on the link and, depending on the browser you use, select either Save this link as, or Download to disk, from the popup menu.)To use Arc.xla, move it to any convenient directory (other add-ins are in {office install directory}\Office\Library). Next, start Excel, and from the Tools menu select Add-ins, and then push the Browse button to find Arc.xla, and then push OK. A new submenu called "Arc..." will be added to the Tools menu. It can be removed using the Add-ins menu item.
See the movie
Dave Olwell has produced a short video on the usage of the Excel-to-Arc add on. You can see the movie here.Usage
Create your data file in an Excel spreadsheet, with (1) the variable names in one row of the spreadsheet, and with (2) the values of the variables immediately below the variable names. For example, if you have 4 variables, the first row of the spreadsheet might give the names of the four variables in columns C to F, and the next 100 rows might give the values of the variables for the 100 data points in columns C to F. Any blank value will be converted to a missing value. Any cell consisting of a single period (the usual SAS missing data indicator) will be converted to the Arc missing value indicator. Text values are permitted; text may include spaces or special characters.Next, from the Tools menu in Excel, select the sub-menu Arc, and then the item Export data. This will give you a dialog in which you can do four things:
Correcting problems with writing files
Arc.xla uses DDE, an abbreviation for direct data exchange, to write a temporary file to the directory c:\windows\temp. Using recent versions of Windows, not all users have permission to write to this directory, and so you might get an error message. Your only recourse is to modify the code for Arc.xla: With the add-in installed and enabled, start Excel, press Alt-F11, and locate the module "ArcExportCode" under Arc.xla. Near the beginning of that code are two lines that look like this:
Const ArcTempFile As String = "c:\windows\temp\arc_temp.lsp"
Const ArcTempLisp As String = "c:\\windows\\temp\\arc_temp.lsp"
These define the location of the temporary file. Modify these
lines to point to a file that you can write in, probably in your ``My
documents'' directory,
and then do File/Save to save the changes. The two lines
must give the same path and file name, one of them with "\"s,
and the other with "\\"s between directory elements.
Arc.xla was written by Russell V. Lenth of the Department of Statistics & Actuarial Science, The University of Iowa. Here is his Web page.
Contact us! arc@stat.umn.edu
Revised, March 28, 2005.