How to Format Your Addresses for BatchGeo

Part of the magic of BatchGeo is that we create maps from your existing Excel files and other spreadsheets. Just copy-paste and you’re done. That said, there are a some simple things you can do to increase the accuracy of turning that list of addresses into a fantastic map. Some recent changes to the geocoder we use makes formatting and arranging your data even more important.

BatchGeo relies on Google Maps for the visual maps platform, as well as the geocoder, which we use to convert addresses and other locations into coordinates to plot on your maps. A recent update from Google is more strict with what they call “ambiguous addresses.” The change promises better performance, which could mean your data is mapped even faster. For this benefit, you’ll want to pay closer attention to your formatting, so we’ve compiled a few tips for making better map data.

Identify Your Location Columns

Validate and Set OptionsWhen you paste your data into BatchGeo, we attempt to guess at the structure of your location columns. Using your header names, we take a stab at the address, city, state, and postal code. In fact, you can use the headers from our Excel Spreadsheet Template to ensure we’ll get it right.

Of course, you don’t need to change your column names just for us. As you create a map, you can choose Validate & Set Options to see our guess. You also have the opportunity to override our guesses. Just match each dropdown to the field in your spreadsheet. If you don’t have a postal code, for example, just choose “none.”

BatchGeo uses these location columns to prepare a formatted, unambiguous address to send to the Google Geocoder. Here’s how the recent update describes address formatting:

Compared to other Google APIs, the Geocoding API provides the best quality matching of addresses globally for these types of complete, unambiguous queries. However, Geocoding API is not recommended if your application handles ambiguous or incomplete queries, such as “123 Main St”, or if it handles queries that may contain non-address information such as apartment numbers or business names.

Since the majority of BatchGeo customers are mapping full addresses, we anticipate few issues, as long as your data is properly formatted. If you’ve previously mapped business or landmark names, you may see an increase in un-mappable locations. We’ll certainly be keeping an eye on it and also welcome your feedback.

Beware Numeric Postal Codes

In addition to identifying columns within your data, you should pay close attention to one field in particular. Postal codes that contain only numbers, such as Zip codes in the United States, can sometimes be misinterpreted. If your postal code starts with a zero—as is the case in seven northeastern U.S. states, Puerto Rico, and Virgin Islands, plus European military and diplomatic mail—Excel needs to treat it as text, not a number.

Consider this data for three baseball stadiums in the US:

Team Stadium Address City State Zipcode
Boston Red Sox Fenway Park 4 Yawkey Way Boston MA 02215
New York Yankees Yankee Stadium One East 161st Street Bronx NY 10451
Philadelphia Phillies Citizens Bank Park One Citizens Bank Way Philadelphia PA 19148

If you copy and paste that into a spreadsheet, you’ll find Fenway Park’s Zip code will go from five digits to four. What’s the difference?

Zip code in Excel

The leading zero, of course. When treated as a number, 02215 becomes 2215. If you copied that spreadsheet data into BatchGeo, it would come without the zero, which could cause issues with the geocoder.

The solution in Excel is fairly simple:

  1. Select the entire postal code column
  2. Choose the Format menu, then Cells
  3. Click Text for the category of the field

You should be able to make similar formatting adjustments in other spreadsheet tools. One quick way to spot a numeric field: it’s right-aligned. Once you make your postal code into text, it should be left-aligned.

Now when you copy your spreadsheet to BatchGeo, the entire postal code (including any leading zeroes) will come along for the ride.

Better Performance Through Formatting

Now that your columns are identified, your addresses are complete, and your postal codes aren’t dropping zeroes, you’re ready to make some maps. With the latest Google changes, they promise to be even faster than our already fast geocoding.

We’ll naturally be watching these changes to see how our service and customers are affected. If you notice anything you think we should check out, be sure to let us know.