Blog > How to Format Your Spreadsheet for Bulk Geocoding

How to Format Your Spreadsheet for Bulk Geocoding

Published on Tuesday, February 27, 2024 by Lisa Schleifer

---

---

In this guide, we’ll describe the way to prepare your spreadsheet for use with Geocodify’s Spreadsheet Uploader to ensure the highest accuracy in geocoding results. This write-up is a structured guide inspired by the instructional content available in the video below. For visual step-by-step instructions, refer to the linked video.

Our Spreadsheet Uploader is designed to be easy and intuitive to use, but there are many ways to set yourself up for success. We’ll walk through the upload process at the end of this tutorial – and see what kind of results we can get – but first, let’s discuss some formatting tips to keep in mind when creating your spreadsheets.

Accepted File Formats

You can use whatever software makes sense for your project. We accept a number of different formats – including: XLS, XLSX, CSV, TSV, and ZIP files. In this case, I’ve got a CSV open in Google Sheets that I would like to upload and geocode.

There are three major factors to check when formatting your spreadsheet.

Headers

First, your spreadsheet must have headers at the top of each column – preferably in the very first row. We use headers to identify and reference the columns we will use to retrieve specific data.

You can name the header anything you like. For example, if you were geocoding Canadian addresses and wanted to switch column C from “state” to “province”, we would still be able to find that information. During the upload process, you get to choose which columns contain the appropriate address components.

Consistent Formatting

Next, make sure that your formatting is consistent.

There are a number of ways that you can input data into our spreadsheet uploader. You can split the address into multiple columns as shown in my initial sample. You can also place the entire address in a single column.

When reverse geocoding, the same is true. You can split the latitude and longitude into separate columns or place them into a single “coordinates” column split by a comma.

The important thing to remember is that once you’ve chosen the format you would like to use, it needs to remain consistent throughout the document. If you elect to use a multi-column format, you shouldn’t have rows that contain a full address – and vice versa. This could potentially cause errors and prevent us from getting the correct results.

If your spreadsheet includes addresses or coordinate pairs in multiple different formats, you will need to split them into different lists.

A couple of other considerations:

• Street Number and Name should always be in the same column, even if you are using a multi-column format. We don’t have a means of concatenating that information on our end.
• When using a multi-column format, we recommend splitting the address into four (or five) columns: the street address, city, state and zip code. You may also want to include a country column if you will be geocoding Canadian addresses. We’ll talk about this more in a second.
• Finally, when inputting Zip Codes, be sure to use standard ZIP format. We do not accept ZIP+4 inputs at this time.

It’s important to note that we only ever add columns during the geocoding process. We never delete them. So if you have other data stored in your spreadsheet beyond address information, that will be retained in your result.

Country Column

The last thing to check for before uploading your spreadsheet is only important if you plan on geocoding addresses in Canada.

If this is the case, be sure that you include that information at the end of your address or coordinate pair. For example, if you are using a multiple column format, add a fifth column called “Country” that allows you to define the address as either in the “US” or “Canada”. If you are using a single column format, add the name of the country at the end of the address string.