Excel, DBF files, and ArcGIS 9.2

I sent out an email to a GIS listserve asking for some help with a problem. Very quickly, I got back some great and helpful responses. All of that geospatial advice shouldn’t sit in my email archive, right? Here is a post that might help others who have the same question I had. To maintain anonymity, I signed their posts with their school affiliation just in case they didn’t expect their answers to be put out on the web.

Here’s was my question:
It seems that Microsoft Excel 2007 does not support the use of dbf files, either opening or "saving as". Is there a workaround that anyone knows of? On Microsoft's web site: "We have determined that a number of these older formats are seldom, if ever used. "...except for by GIS users!

Many thanks,
Vassar College

Here were the answers:

1) About working with Excel files in ArcGIS 9.2 - I found that by using Excel files in 9.2, it solved a lot of the problems that we would encounter with .csv, .txt, and .dbf files in earlier releases of ArcGIS. I wrote up a tip sheet to help my students out when working with Excel files, which some folks might find useful. I've certainly learned some new things following the discussion on this list. The Excel tip sheet is on the Tuft’s web page in the Working with Tables section.

Also go to ESRI’s page on using Microsoft Excel with ArcGIS 9.2.
From: Tufts University

2) If you have ArcGIS 9.2, it will accept excel files now. If not I would use a txt file.
From: California University of Pennsylvania

3) Fortunately, with Arc 9.2 we can use .xls directly (just make sure in Excel 2007 that you use that “compatibility” function that saves as an .xls 1997-2003 version), but when I had to revert to Arc 9.1 once last month (but still with Excel 2007) I saved as .txt and .csv and brought in to Arc without issues.
From: University of Redlands

4) It looks like that's only when Excel saves output; on input it will still read DBF files.

So with 9.2, the thing to do might be to join the Excel file (or a CSV file with 9.2 or 9.1) and then export the data as a new shapefile, which should produce the DBF for you.

Coincidentally I just stumbled across this trick in 9.2 (perhaps earlier, too): in ArcCatalog, right-click on a CSV file, and in its menu select "Export" and then "To dBase (single)...".

You can also open up an XLS file (in 9.2), point at a sheet or named region, and then do the same thing.
From: Amherst College

5) However if you use 2007 you need to save to the .xls 2003. But the 2007 does export to dbf too ... just hidden in their crazy menus.
From: Rio Hondo

6) And it seems the shape file must begin with a letter and not a number. We found that, for example, joining an Excel file to 1950_census.shp results in an SQL error. After renaming the file to census_1950.shp, everything behaves properly.
From: Wheaton College

7) I’ve heard that as well. I’m not using Excel 2007 yet, but the only workaround I can think of is to use ArcGIS to save your dbf as a text (CSV) file and then read it into Excel.
From: Middlebury College

8) Excel is still supposed to read dBase formats. A work-around, for the time it stops doing this, is to import the data into Excel through an ODBC connection or other database connection. It's possible you could also export changes to the data back out through the connection, but I haven't tried this.

There are subtle but serious problems using CSV files for data interchange, because they do not contain field type information. For example, string fields containing numeric strings (like zip codes, social security numbers, or US Census state/county/tract/block identifiers) will lose their leading zeros because Excel will assume these fields are of numeric type.
From: Haverford College


Meg here: I found that if I right click on the Excel file in ArcCatalogue (shown circled in red above), I will see the worksheets and then I can drag the table over to ArcMap.