University of Minnesota

School of Statistics

Home
Ordering
Preface
Download Arc
Arc FAQ
Update Arc
Arc add-ons
Text extensions
More data
Regression Graphics
Xlisp-Stat

    Reading a Microsoft Excel Spreadsheet into Arc

    Installation (revised on April 27, 2009)

    Depending on your version of Excel, right-click on the appropriate link below, and save the file to your hard disk in any convenient place: The files above are slightly different versions of a Microsoft Excel add-in for Windows only, that provides support for Arc, version 1.04 or newer. Some browsers may change the name of the file you download, so may need to change the name back to the original name. Next, start Excel.  

    • If you have Office 2003 or earlier, then 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.
    • If you have Office 2007 or later, then push the round menu button in the upper-left corner of Excel's window, then push the Excel options button at the bottom of the menu; in the resulting dialog box, select Add-Ins in the left panel, and push the Go... button near the bottom (next to Manage: [Excel Add-ins]).  Then you will see a smaller add-ins dialog; push the Browse button and navigate to your copy of Arc.xlam and push OK.  You should now see "Arc export add-in" in the list; make sure it is checked, and push OK again.  You should now have an Add-Ins tab in Excel's ribbon interface.

    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 (Office 2003) or the Add-Ins tab (Office 2007), select the sub-menu Arc, and then the item Export data. This will give you a dialog in which you can do four things:

    1. Select a name for the dataset. The name will appear on the Arc menu. The default is to use the name of the spreadsheet.
    2. 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.
    3. 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.
    4. 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.

    Correcting problems with writing files

    Arc.xla uses DDE, an abbreviation for direct data exchange, to write a temporary file to the directory C:\TEMP. Make sure that this directory exists on this system and that you can write to it.. If you cannot write to this directory or otherwise want to use a different temporary directory,  you may modify the code for Arc.xla as follows: 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:\temp\arc_temp.lsp"
        Const ArcTempLisp As String = "c:\\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, possibly 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 25, 2010.