Knowledge Base - Technical Articles


Technical Article   HowTo:  Connect to an Excel spreadsheet in ArcCatalog

Article ID: 15380
Software:  ArcGIS - ArcEditor 8.1.2, 8.2, 8.3, 9.0, 9.1, 9.2, 9.3, 9.3.1 ArcGIS - ArcInfo 8.1.2, 8.2, 8.3, 9.0, 9.1, 9.2, 9.3, 9.3.1 ArcGIS - ArcView 8.1.2, 8.2, 8.3, 9.0, 9.1, 9.2, 9.3, 9.3.1
Platforms:  Windows NT 4.0, 2000, XP

Summary

Instructions provided describe how to connect to an Excel spreadsheet from ArcGIS. ArcGIS can retrieve data from Microsoft Excel 5.0, 7.0, 97, 2000, 2003, or 2007 files.

Procedure

There are three procedures to create an OLE DB connection to an Excel spreadsheet.

A. Create an Open Database Connectivity (ODBC) data source. -show me-


1. Open the ODBC Data Source Administrator.

▪ In Windows NT: Click Start > Settings > Control Panel and double-click ODBC Data Sources.

▪ In Windows 2000 and XP: Click Start > Control Panel > Administrative Tools > Data Sources (ODBC).

2. Select the User DSN tab and click Add.

3. Select a Microsoft Excel Driver.

▪ For Excel 2003 and earlier, select Microsoft Excel Driver (*.xls).

▪ For Excel 2007, select Microsoft Excel Driver (*.xls, *.xslx, *.xslm, *.xslb).

4. Click Finish.

5. In the ODBC Microsoft Excel Setup dialog box, type a Data Source Name, such as test_xls, and a description for the data source.

6. Select the Excel version from the drop-down list, and click the Select Workbook button.

7. Navigate to the Excel data file.

8. Click OK three times to exit all the dialog boxes and finish creating the new data source.

B. Place the data in the OLE DB table format. -show me-


1. Open the .xls file in Microsoft Excel.

2. Select the data (cells) you want to display in ArcInfo.

3. Create a table name. This name appears in the tables list when the OLE DB connection is expanded in ArcCatalog or ArcMap.

▪ In Excel 2003 and earlier versions: Click Insert > Name > Define and type in a name for the selected cells.

▪ In Excel 2007: Click Insert > Table. In the Create Table dialog, verify the selected cells and click OK. Under Table Name, type a name for the table.

4. Save the file and quit Excel.

C. Connect to the Excel file from ArcCatalog. -show me-


1. Start ArcCatalog.

2. Expand Database Connections.

3. Double-click Add OLE DB Connection.

4. In the Data Link Properties dialog box, select Microsoft OLE DB Provider for ODBC Drivers, and click Next.

5. On the Connection tab, select Use data source name.

6. Select your Excel file from the drop-down list. Use the Refresh button if necessary.

7. Click the Test Connection button to verify the connection works. A message box should confirm the connection has succeeded.

8. Click OK to finish the process.

9. In ArcCatalog, type a name for the connection.


 ArcCatalog sets the column headings based on the values contained in the first row of the Excel spreadsheet. Make sure the first row of the spreadsheet contains column headings.



Related Information


Created: 10/15/2000
Last Modified: 5/3/2011

Article Rating: (6)
If you would like to post a comment, please login

Comments

By Anonymous - 03/18/2010 10:25 AM

The article needs to be updated.

This article needs to be updated with Office 2007 information and the new location for the 'Data Sources (ODBC)' connection, etc.

By Anonymous - 09/25/2007 6:58 AM

Other - See details below.

It would be nice to see this helpful article updated. It is currently written for "ArcGIS - ArcEditor 8.1 ArcGIS - ArcInfo 8.0.1, 8.0.2, 8.1 ArcGIS - ArcView 8.1" based on the following "Platforms: Windows NT 4.0, 2000" A new version of this should be written to include ArcGIS 9.1 or 9.2, and for the XP platform. Thank You for considering this suggestion.

By Anonymous - 08/02/2005 7:35 AM

does this apply to 9.1

Rating:

By Anonymous - 12/22/2004 8:18 AM

Needs to be updated to include 9.0, Excel 2002,2003 etc. Such a key concept - hard to believe it hasn't been updated since 11/27/2000!

Rating:

By Anonymous - 03/08/2003 10:04 AM

Rating:

By Anonymous - 03/06/2003 8:36 AM

Rating:

By Anonymous - 02/25/2003 3:39 PM

Could not get this to work

By Anonymous - 02/13/2003 8:19 PM

After following the instructions, I can still not open the table in ArcMap. How?

Rating:

By Anonymous - 01/31/2003 11:19 AM

I want to catalog .xls files just as I'd catalog shapefiles. That is, I want to use ArcCatalog to house and generate metadata for everything including non-GIS files. I carefully followed the steps in this article and I now have the .xls file as a database connection but I can't preview it and under the metadata tab it says it can't have metadata! Metadata is needed for lowly spread sheets too!

By Anonymous - 01/31/2003 8:43 AM

Extremely helpful. Great step by step.

Rating: