Spreadsheet formatting
How to format your spreadsheet of addresses or coordinates for geocoding
In order to ensure your spreadsheet is geocoded properly, you need to check three things before uploading:
Whether it has headers (such as "address" or "street" / "city" and so forth),
Formatting the addresses or coordinates consistently in either single column or multiple-column format, and
Including the country (if geocoding Canadian addresses).
You can upload .xls, .xlsx, .csv, .tsv, and .zip files for geocoding. Download a sample list: .XLSX (shows all acceptable formats), .CSV (shows multiple-column format only).
When you upload a file, we only add columns -- we won't delete any information from your spreadsheet. You can see an example geocoded file here.
If you would like to review the following information in a video format, check out our Video Tutorial.
Data quality
Your addresses do not need to be complete. They just need to be formatted the same way (i.e. either single column or multiple column) with any of the following combinations of data:
Street address, city, state, ZIP/postal code [+ 'Canada' for Canada]
Street address, ZIP/postal code [+ 'Canada' for Canada]
Street address, city, state/province [+ 'Canada' for Canada]
city, state/province [+ 'Canada' for Canada]
ZIP/postal code [+ 'Canada' for Canada]
Latitude/longitude
Geocodio can only accept five-digit ZIP codes as an input. If ZIP+4 is provided as an input, we will utilize the first five digits instead.
Spreadsheet Geocoding Formats
If you do not have data for a certain field (like ZIP), you can just leave that blank.
Multiple-Column Format with Addresses
| Street | City | State/Province | ZIP | Country |
|---|---|---|---|---|
| 1785 Military rd | Niagara Falls | NY | 14304 | |
| 323 Yonge St | Toronto | Ontario | Canada | |
| 1432 Mackay street | H3G 2H7 | Canada | ||
| 146 south Main street | 01949 | |||
| 3211 wilson boulevard | arlington | va | ||
| 8421 NY-89 | Interlaken | NY | 14847 | United States |
| 29042 |
Single-Column Format with Addresses
| Address |
|---|
| 323 Yonge St., Toronto, ON M5B 1R7, Canada |
| 1432 Mackay St, H3G 2H7, Canada |
| 1785 Military Road, Niagara Falls, NY 14304 |
| 3211 Wilson boulevard, arlington va |
| 8421 NY-89, 14847 |
| H3G 2H7, Canada |
| 29042 |
Multiple-Column Format with Latitude/Longitude Coordinates
Note: your columns must be in Latitude | Longitude order.
| Latitude | Longitude |
|---|---|
| 43.65687 | -79.380951 |
| 38.885496990291685 | -77.09966660383554 |
Single-Column Format with Latitude/Longitude Coordinates
Note: your columns must be in Latitude | Longitude order.
| Latitude/Longitude |
|---|
| 43.65687,-79.380951 |
| 38.885496990291685, -77.09966660383554 |
1. Make sure your spreadsheet has headers
You can name the header columns anything you like
During the upload process, you'll get to choose which columns contain the address components.
| Street | City | State | ZIP | Country |
|---|
| Address |
|---|
| Latitude | Longitude |
|---|
| Latitude/Longitude |
|---|
2. Consistently format the addresses in one column or multiple columns
You can put the address in a single column or split the address into separate columns
The street number and name must be in the same column
Addresses do not need to be complete; see here for a list of acceptable addresses
If your spreadsheet includes addresses in both formats, you will need to split them into different lists
If you choose to include ZIP Codes, use standard ZIP format instead of ZIP+4. We do not support ZIP+4 inputs.
Acceptable format: multiple-column Address data (street, city, state, ZIP) are split into multiple columns, and all addresses are formatted in the same way.
| Street | City | State | ZIP | Country |
|---|---|---|---|---|
| 1785 Military Rd | Niagara Falls | NY | 14304 | |
| 323 Yonge St | Toronto | Ontario | Canada | |
| 740 S Meadow St | 14850 | |||
| 2204 W 12th St | Erie | PA |
Acceptable format: single-column All of the address data is in a single cell, and all addresses are formatted the same way.
| Address |
|---|
| 323 Yonge St., Toronto, ON M5B 1R7, Canada |
| 1785 Military Road, Niagara Falls, NY 14304 |
NOT acceptable: Mixed format Some addresses are formatted in a single cell, and some are spread across multiple cells. This would not process correctly. If your spreadsheet looks like this, you should split it into two spreadsheets. You can do this by filtering for addresses that have a blank second column and copy/pasting them into another spreadsheet.
| Address | |||
|---|---|---|---|
| 435 S Van Buren St, Shipshewana, IN 46565 | |||
| 1785 Military Rd | Niagara Falls | NY | 14304 |
3. If your spreadsheet includes addresses in Canada, include a country column or "Canada" exactly
With Canadian addresses, a Country column must be present and it needs to say "Canada" exactly (no abbreviations)
If no country is given, USA will be assumed
Note that you may input postal codes, but full postal codes will not be returned in the results as they are proprietary to Canada Post. (They will still be present in the original input columns, if you included them.)
| Street | City | State | Postal Code | Country |
|---|---|---|---|---|
| 323 Yonge St | Toronto | Ontario | Canada | |
| 100 City Centre Dr | Mississauga | ON | Canada |
Uploading Your Spreadsheet
You can upload your spreadsheet using our Spreadsheet Uploader tool:
You can also upload your spreadsheet directly through our API using our Lists endpoints. These endpoints allow you to create, check the status of, and delete spreadsheets programmatically.
The Lists API requires some additional configuration, so check out the documentation below for more information.