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.

Mapping History

I learn everything new about geospatial technologies from two sources. The first is Ogle Earth.

Stephen Geens blogged about the Time Space Map. It is billed as a geographic wiki. I think of the Google Earth Community BBS as that, so I think the Time Space Map is more elaborate. What a great concept and what potential this could have for academics.

Stephen writes: "Wouldn’t it be nice if all of Wikipedia’s historical events could be mapped to Time Space Map via some automated process? (That would avoid a lot of duplicated effort.) Wouldn’t it be nice if Google Earth’s timeline could be put to use exploring the history of any place you care to zoom in on, via a KML network link served by Time Space Map?"

I would take it further. Wouldn't it be great to share all places discussed in Wikipedia as KML files? Where is Poughkeepsie, NY? How about where is Poughkeepsie REALLY. James Baldwin grew up where? On the lower East Side? Battle of Normandy occured in the middle of France? You can zoom to locations in Wikipedia but it's not elegant at the moment and it is highly inaccurate. When you click on 'location' in Wikipedia, you should be able to launch Google Earth and fly there.

We have an ethnobotany professor working on a project in class this semester where the students will go out and map the locations of ethnic markets in the Poughkeepsie community. She wants the students to take photographs and upload them, with a description, to Panoramio and, of course, geolocate them, so that others can see these markets and what you can get there. Further, she wants the students to add content to the Wikipedia page for Poughkeepsie that will highlight the ethnic market community. If the two, Google Earth photos and Wikipedia entries, could be combined...that what be powerful. I'll post about this class later in the semester.

I mentioned two great geospatial sages. The other most-informed person I know about all stuff geospatial is Diana Sinton. She let me on to the fact (via a list) that the new book Placing History: How Maps, Spatial Data, and GIS are Changing Historical Scholarship edited by Anne Knowles (Middlebury College) and Amy Hillier (Univ of Pennsylvania) is out through ESRI press. Highly useful for those of us who support mapping, this book will be great to use for ideas for mapping in the humanities.

So, these two, seperate, unrelated topics are in the same post. Why? Because how cool it would be if Placing History could be designed to incorportate new tools like Time Space Map, right? That's the next book. Or I guess it would be an e-Book?

Maps on the Web

When I'm asked to help people (read: college professors) make maps in classes, increasingly I refer to web mapping sites. I've mentioned some of these before in this blog, and if you want to know of some of the great ones, look on this blog's links under Web Mapping Tools. I was asked again for demo-ing the use of GIS software to show some world demographic data, but I have to say that the best way to show these data, in my mind, is with some of the slick web mapping sites out there.
Remember the days when web maps were very clunky because they drew so slowly? Web maps have alwasy been pretty cool because you could see mapped data and sometimes you could download those data, but the effect was lost with the slowness and unattractive visual display. Maybe it’s the advent of virtual globes or map mash-ups, but I'm finding more and more web mapping sites that are fast, have loads of data to play with interactively and look very pleasing when you are in the map interface. Here are some I like.


GapMinder is a web mapping site that Diana Sinton mentioned months ago, but I keep coming back to it. Easy to use and visually striking, it really makes the world of encouraging academics to use maps in classes a lot easier (than teaching them GIS software or finding the data myself and making the maps for them in class). As shown above, click on the Map tab to look at global maps or use the Chart tab to plot variables. For more ideas, you can view the video below.

Use the Chart feature (circled in red above) and you can do things like this map which shows the life expectancy per percent women in the workforce. Press the Play button and you'll see the how the variable for the countries changes through time; GapMinder makes trails of the country. I've highlighted the U.S., Bangladesh and Nigeria which shows that life expectancy trends upward for all countries, but the biggest gain in life expectancy of these three is Bangladesh.

Again, see the video below of GapMinder in action at a 2006 TED conference.


"Hans Rosling: Debunking third-world myths with the best stats you've ever seen"
posted June 2006 (thanks Beth Feingold for pointing this out to me.)

“You've never seen data presented like this. With the drama and urgency of a sportscaster, Hans Rosling debunks myths about the so-called "developing world" using extraordinary animation software developed by his Gapminder Foundation. The Trendalyzer software (recently acquired by Google) turns complex global trends into lively animations, making decades of data pop. Asian countries, as colorful bubbles, float across the grid -- toward better national health and wealth …"

I couldn't figure out how to embed the video, sorry.


DataPlace has a wealth of US Census data. When you first see the page, it isn't all that attractive. But search on a city and press go and then click on Maps (circled in red above) and you have so much data at your finger tips. Seems you can plot 2000 or 1990 Cenus data for the whole country and for what seems like any variable you might want. The data sources and coarseness (no blockgroup data) are listed.

The World Freedom Atlas "is a geovisualization tool for world statistics. It was designed for social scientists, journalists, NGO/IGO workers and others who wish to have a better understanding of issues of freedom, democracy, human rights and good governance. It covers the years 1990 to 2006." Click on Take me to the maps. On the left side you'll find loads of data and the sources, and an explanation of what the numbers mean. If the data for that variable exist for past years, you can change the map.

Cool Google Earth Video

Came across this nice Google Earth video by EarthOutreach. When you mouse over the bottom of the video, you'll see there are different "chapters" on the different and most interesting Global Awareness Layers in Google Earth.

Geospatial Technologies in K-20 Education

In eSchool News I noticed that one of the literacies being promoted or supported, along with Reading and Math and others, is GIS and Geographic Inquiry. It seems like ESRI has a bit of an influence on the page, but there may be some useful resources here for promoting and supported geospatial technologies at all schools. From the GIS and Geographic Inquiry page:

"The growing use of these tools in an array of social studies and STEM subjects supports authentic, problem-based instruction, helping students tackle real social and environmental research projects in their communities."

U.S. Politics and Campaiging: The Game

My nine year old showed this game to me. (Actually, he was playing it and I looked over his shoulder and was pleased to see that my interest in maps and in politics has rubbed off on him.) It's on the on-line game site MiniClip and it's called Campaign.

The game is about the current U.S. presidential campaign; you choose players who are currently campaigning for President. Then you choose your staff--Spinmeister, Operative, Hatchet Man, or Fundraiser. When you play, you need to choose your moves wisely because each one (attack ads, rally, new hire) costs you money.

Skidmore Regional GIS Conference for College Educators

As mentioned before, Skidmore College is holding a regional GIS conference geared towards colleges and universities on January 11. The agenda looks terrific. Get in touch with Alex Chaucer, if you're interested in attending. He can send you directions.

Skidmore Regional GIS Conference for College Educators

Friday, January 11th, 2008
Tentative Program

9:30 AM Welcome (Dana Atrium)

10:00 AM Todd Fabozzi: The Challenges of Sprawl and Smart Growth in the Capitol Region

11:15 AM Jonathan Cobb: GPS in the 21st Century - An Overview of Current GPS Capabilities, Modernization Efforts, and Complementary Networks

12:00 Noon Lunch Break

1:00 PM Robert Jones: GIS and the Time Dimension

1:30 PM Sharron Macklin and Jenni Lund: ArcGIS and Google Earth: Rules of Engagement

2:15 PM Discussion: Fostering a GIS Community: On Your Campus and Among Liberal Arts Colleges

3:30 PM Session Ends

Replacing the Tablet PCs of our Mobile Mapping Lab with Newer Tablet PCs

We received our HP tablet PC grant in summer of 2004. What we got were the early addition of the ‘convertible’ tablet PC, the TC1100 (shown above). To get an idea of how we use tablet PCs with GIS software and GPS receivers (shown below), please check out our project web page. We also had a paper in Journal of Geoscience Education on our use of tablets in a geomorphology class.

For the past seven months or so, I’ve tested out new tablet PC options for replacement of our original HP/Compaq tablet PCs. The following are my thoughts on the tablet PCs we tested and what we decided to get, so if you don’t care to ever use a tablet PC for GIS mapping, you can skip this entry.

What we were looking for in a replacement tablet PC were these key improvements: 1) a brighter screen, 2) lighter weight, and 3) longer battery life. Ease of use of the pen and screen conversion (when you flip the screen around and write on it) is a plus. If we could also get a rugged option, that would be great, as we use these tablets in the field and, we all know, “stuff happens.” Also, with each choice, we wanted the most RAM available. Our original TC1100 tablets are 1.876 kg or 4.13 lbs. and came with 512 MB RAM that we never upgraded.

On each of the tablets listed below, I loaded ArcGIS 9.2 plus the GPS software that we’ve been using with our little Rayming receivers (these are no longer available under that name). This is what the GPS receiver looks like. All loaner tablets were the standard units and did not have extra RAM installed.

HP TC4400 (daylight viewable model) with Windows XP (I forgot to weigh it but the web page says 4.6 lbs. 0r 2.08 kg, which seems heavier than I remember) I wanted to be loyal to HP, since they so kindly gave us the original grant and I feel forever indebted to those guys, we tried the TC4400 (shown above). This model tablet PC is like a ‘real’ laptop, very business-y. It’s black and sleek, it looks nothing like our funny little TC1100 (overheard by an anonymous Vassar computing support person, “these are just novelty items” when referring to our new tablet PCs. Ha!) The price for the TC4400 I was quoted was around $1300 (including extra RAM). These seemed pretty good to me and to my colleagues in Earth Science and Geography.

Lenovo ThinkPad X41 (and X61) with Windows XP (X41 - weight 1.889 kg or 4.16 lbs.; X61 - weight 1.765 kg or 3.89 lbs.) When Vassar replaces Intel machines, we use Lenovo/IBMs, so I figured that a ThinkPad tablet (shown above) should be tested because our in-house support might be better. In addition there are ten ThinkPad X41 tablet PCs already on campus in Biology (used for field work also). But I was not sold on the ThinkPad. The weight, the laptop-like quality and our past lack of conviction that ArcGIS plays nice with the ThinkPad, turns me away from this option. Plus the price was around $450 more (similar specs to the HP). There seems to be no daylight viewable model available but I could be wrong about that. Panasonic Toughbook CF-19 with Windows XP (weight 2.341 kg or 5.16 lbs.). Sure these are monsters, but wow, they’re beautiful (see above). These are the Hummer of tablet PCs. If you want a piece of field equipment that won’t sneeze at inclement weather, or balk at dust and heat, the Toughbook CF-19 is your soldier. They even have a model with embedded GPS receivers! It was a little awkward to get a loaner, but when you’re a little school with only a little bit of money, and you want to make an educated decision, you need to actually hold the equipment in your hands and give it a thorough test. If you want advice on getting a Toughbook loaner, contact me (mestewart@vassar.edu) and I can let you know what I went through so maybe you can avoid some of the pitfalls. The price, including GPS and extra RAM is a whopping $4,000+ each. We cannot justify buying 10 or more of these rugged tablet PCs for teaching purposes, plus the weight alone could make students a little cranky. But the faculty members who routinely use the tablet PCs for mapping loved the ruggedness and know the hardship of heavy field equipment. They wanted a couple to have around for their summer field projects. Who am I to argue?! HP 2710p with Windows Vista Business and daylight viewable option (weight 1.68 kg or 3.7 lbs.) I was about ready to order fifteen TC4400 tablets, I was on the phone with my sales rep and he said are you sure you don’t want to try out the newer 2710p? (Shown above) I didn’t want to. I was quite tired of trying out tablet PCs and felt like I was annoying everyone around with, ‘here, try this one out!’ exuberance. But when the sales rep said that they’re a good ½ pound lighter, I thought that I better give a test. I am glad that I did. The 2710p is terrific, light-weight, sleek, easy to use, great screen for outside use, it is exactly what we need to replace our older TC1100 tablets. The 2710p tablet has a metallic case, so it doesn’t seem like a standard laptop and it doesn’t seem frail. I doubt that it can stand a drop from a second story window, but at least it looks like it could handle it. The price I got was a little over $1,300 each, but I think we will go for the 2710p. It reminds me of our TC1100 tablets, only more rugged, more serious, less ‘novelty item.’ I might look into getting the embedded camera that takes still and video pictures; might be great for field work. Note that this model comes with Vista Business operating system and ArcGIS 9.2 needs service pack 4 to work with Vista. It seemed to do fine in my test.

Added 2/12/2008: We bought 2 CF-19 Toughbooks, and they're working great, and have on order 15 HP 2710P tablets. By the way, in addition to a daylight viewable screen, we are getting the 2710Ps with an embedded camera, all for a fabulous price. Would really like to have an embedded GPS receiver (I know I'm not the only one) but will have to wait until more people shout about this to the vendors.

Web Mapping for Educators

I missed the live version of a really terrific webinar put on by Wiley publishing. I know it was good because, fortunately, Wiley sent to link to me since I was signed up to listen to the presentation. Joseph Kerski, of Environmental Systems Research Institute, Inc. Education Team, gave a great overview of useful mapping web sites on a variety of educational topics and the importance of bringing geospatial technologies into the classroom. He also gave ways to use these web sites for class exercises, examples are shown below. The slide show is called “Geo Tools: Spatial Inquiry Using Web Mapping Tools.” It’s really worth checking out in 2008.

Using the MLA site to map German speakers in the U.S.

Using the National Atlas to map multiple births.

If you’re interested in finding out about and signing up for more of these webinars, visit Wiley Faculty Network where they “provide technology training, live workshops, a one-on-one mentor program, and other valuable resources designed to help you collaborate and communicate with your colleagues to create a more proactive, enjoyable classroom experience for all.”

(Google) Earth at Depth

Using Google Earth and SketchUp, professor Declan De Paor has found a way for geologist, geophysicists, archaeologists and others to display what they interpret to be happening below the surface of the earth. De Paor gave a presentation at the recent American Geophysical Union meeting on his use of Google Earth for subsurface investigations. I wasn't there. It was reported in Google Earth Design, Google Earth Blog and Ogle Earth. Sounds like AGU 2007 had many good virtual globe presentations plus the presence of a Google Earth booth.

Because it's still holiday break, I haven't tried this yet, but it should be rather simple to do, and if I'm successful, I'll report back.

An added note about De Paor, he is a structural geologist, just like the developers of the other cool geology application of Google Earth that I talked about before.

Wildfires and Computer Modeling

An interesting story appeared in the New York Times today about how the US Forest Service and others are enlisting a "one-of-a-kind" computer model to help with fire prediction. They're using slope, prevailing wind, soil moisture, locations of trees killed by a bug, and other geographically identifiable attributes, so why didn't the Times call this high-end computer model a GIS? This is a long-standing frustration I've had with Times reporting; they don't usually call something that is GIS...GIS.

There's even a slick figure showing a time series of relative numbers of lodgepole pines trees killed by beetles. It's all GIS! (In the Times article, click on the map like the one shown below.)

Nor did they fully report the story. The project is the result of an inter-agency collaboration. In the paper version of the Times, no mention was made of this really great web site, that anyone with a browser can use.

From the GeoMAC (Geospatial Multi-Agency Coordination) web site:

The Geospatial Multi-Agency Coordination Group or GeoMAC, is an internet-based mapping application originally designed for fire managers to access online maps of current fire locations and perimeters in the conterminous 48 States and Alaska. Using a standard web browser, fire personnel can view this information to pinpoint the affected areas.

They've got Google Earth layers and a web mapping application with current fire status data. This is terrific, useful and powerful information made available for everyone and it's not some kind of inaccessible data or something only scientists can view.