Thursday, February 14, 2008

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.

10 comments:

Anonymous said...

Adding to the above there if another idiotism with working national versions of MS softwares. In Hungary , fe example we use comma for decimal points. That's why Microsoft changes the csv format for Hungarian version of excel and uses semicolon separator, but call it yet csv (comma separated values). So if I want to export Excel data and import it ArcView, ArcGis or any kind of software (there are a couple freeware on the Internet built in java last years you can use to makke maps and statistical graphics) - they all use csv a main exchange type. But it is comma and not semicolon separated, and all uses decimal point and not decimal comma. So I every and each time have to change all commas to point first for numbers, and then all semicolons for comma.

Anonymous said...

Rio Hondo in comment 5 says that 'the 2007'(guess Excel 2007) will export to dbf, but it's 'hidden in the crazy menu's'.
Please can someone explain which crazy menu(s)?
I have been looking everywhere. Also the database connections are not giving any results...
Florisz

Anonymous said...

Rio Hondo is not quite right. There's a lot of crazy menus, but none of them allows to save a worksheet in DBF format. I tried to save using VBA (to avoid crazy menus) and it also doesn't work. After some googling I found this:

http://blogs.msdn.com/excel/archive/2006/08/24/718786.aspx

If I want to save sth to DBF, I save it as "old" (ie. 97/2000/2003) XLS, open it in OpenOffice and save it there as DBF. But easier is to import the sheets with ArcCatalog into a personal database or something like that.

GOwin said...

I'm running my GIS on Linux, but not an expert

My experience with OOcalc and spreadsheets are not very pleasant. It constantly chokes with large files (40k rows ++ and several 50+ columns)

For working with the large stuff, I'd rather use gnumeric which reads DBF files but doesn't save them. So, I have to save them in an intermediate format for OOcalc to open and save in DBF.

I don't do copy&paste. I run out of memory.

Meg said...

GOwin - I wasn't sure what OOcalc was so I'm adding it here. It's an OpenOffice spreadsheet program (I should have known that)
http://www.openoffice.org/product/calc.html

So thanks for adding some thoughts on this. This post is one that gets a lot of hits so adding to the thread is useful to many people. I don't use Linux or OpenOffice so can't comment on those topics.

Meg

Alex said...

In this situation recommend to use this software-corrupted repair Excel recovery tool,because when my friends were in same situation,program helped their,tool is free as far as I can see,it can be easily solved with Excel recovery software for recovery xls,will allow you to Excel files recovery and make sure, that it works and that your work results can now be recovered just in several clicks,allows to save many hours of your work for recovery for Excel download and recover everything, that was done before.

TheXLWiz said...

XLSX2DBF is an Excel 2007 add-in that helps convert and/or save a native DBF file that was edited with Excel 2007.

Two common scenarios are supported:

A) Open a native DBF file in Excel 2007 – Make changes – Save as a native DBF file with the changes.

B) Open/create an Excel file that looks like a database (headers/columns/rows) – Save as a native DBF file.

The software is free, but donations are appreciated.

http://thexlwiz.blogspot.com/

Gyula

TheXLWiz said...

Hi there,

just wanted to let you know that I released a new version of the add-
in that enables saving a DBF file in Excel 2007.

New features:

1. Now you can add/insert new fields, create calculated fields in
addition to adding new records or editing existing records in your
native DBF file!

2. If you start with an Excel file the software now have enhanced
capabilities to determine the field types (better than Microsoft's own
in earlier Excel versions).

3. The add-in checks DBase field naming conventions and also
identifies duplicate fields. All problem field names are visually
identified with a cell comment!

4. If you start out with a brand new file and forget to save it, the
add-in will ask before the conversion.

5. Large files are supported. I edited files over 500,000 records with
no problem.

See the post at http://thexlwiz.blogspot.com/.

Gyula

Alexis said...

At work with dbf files I usually use FoxPro,but some days ago all my dbf files were damaged and fortunately I found-dbf recover,tool solved this problem very easy and free of charge.In addition it showed how recovering data from damaged files of the *.dbf format (dBase IV, Clipper, FoxPro and others).

Unknown said...

Another issue about saving an excel file to dbf with these new methods (through ArcCatalog, MS Acess, xls2dbf add-ins) is that the resulting dbf file is huge, 10 times the size of the original excel file. This eats at your storage space quickly. Before, when using excel 2003 and simply 'saving as' to dbf the resulting file would remain about the same size. Does anyone know of an efficient way to convert or to compress the resulting dbf file. It annoying to lose so much unnecessary storage space.