Setup your data file for the Spreadsheet Data Loader

by Phil Vu

Setting up the data file to geocode your addresses is the most important step. Geocoding is the process of locating an address on a map by finding the latitude and longitude of the address. Here are some helpful guidelines to ensure addresses in your file are located when geocoding.

To geocode an address, your spreadsheet must have columns that contain standardized names used for an address: Address, Address2 (if needed), City, State, and Zip code (5 digit). Please note that the address column must be labeled as ADDRESS; any other headings like “Street,”, “Project Location”, “Addr” etc will not be recognized when geocoding. In the example below, the columns have the correct labels for geocoding.

Here are some helpful tips that will locate an address:

  • Address – This primary address column should contain only the street number and full street name including St., Blvd., etc., and any directional information such as N, S, NE, etc. Do not include unit or apartment numbers in this column.
  • Address2 – If the address has an apartment, suite, or unit number, put it in this column. This does not have an effect on geocoding and can be displayed for the address later.
  • City – City name.
  • State – Standard state postal abbreviation.
  • Zip code – 5-digit zip code. At this time, we cannot locate points on a map using the zip+4.
  • Latitude & Longitude – If you happen to already have the geocoded information for an address(es), the coordinates must be in decimal format not minutes and seconds.

Addresses can come in a variety of different formats; these are common mistakes when geocoding an address which would result in the PolicyMap Data Loader failing to find the address

    1. Multiple addresses should be entered in separate rows on your spreadsheet.
    2. An address range should be separated into multiple rows if it refers to multiple properties.
    a. Ranges of address with hyphens, such as 123-127 Main Street, will be mapped to the position of the first street number – in this case, 123 Main Street. If you intend a hyphenated address to mean a series of points on the map (e.g., 123 Main St, 125 Main St, 127 Main St), you need to list them out as individual addresses on separate rows.
    3. Hyphenated addresses, such as those in Queens, NY (65-30 Kissena Blvd) should be entered without the hyphen (6530 Kissena Blvd).
    4. Street numbers are essential. Addresses that do not contain street numbers will not appear in PolicyMap.
    5. Locations given that are not addresses will not appear in PolicyMap. You cannot map “scattered sites”; each site requires its own address to be mapped.
    6. Locations with empty address fields will not be displayed in PolicyMap.
    7. Cross streets cannot be used to identify an address.
    8. Apt, Units, Number, or anything normally in Address2 should not be in included in Address1.
    9. Columns that contain numbers as the value should not contain any text, such as “N/A”.
There are a few limitations for text fields, headers, and file sizes, so please pay attention to how much data you are uploading as you will see the error message during the geocoding process.
  • Files cannot have more than 5,000 rows (4,999 addresses and 1 header)
  • Files cannot have more than 45 columns of data
  • Header columns can have up to 50 characters
  • Text Fields can have up to 250 characters


If you go over these limits, simply add an additional column in your field and distribute the additional text.


Once you have your spreadsheet setup and ready to upload, make sure you save the file as a CSV (or Comma Separated Value) file as that is the only file format accepted in the Data Loader tool. All excel files can be easily converted to a CSV file. Just select Save As in the excel and choose CSV (Comma delimited).

Please note that, here at PolicyMap, we customarily get a 75%-80% match rate when geocoding addresses. And once we investigate the remaining 20%-25% of addresses, we can usually adjust the problematic addresses, resulting in a 95%-98% match rate. As such, we’ve included the feature in our spreadsheet data loader to allow you to adjust those addresses that our geocoder could not locate. Once you’ve run the file through our data loader, simply click in the “Geocoded” column to see which addresses received the “red light.” You can then try alternative address formats in order for our geocoder to retry finding the location. Feel free to contact us at info@policymap.com or call us tool free at 1(866) 923-MAPS. if you need help with this.

Now that you have a data file that is ready to use, read our blog on Using the Spreadsheet Data Loader.

Categories:
Tags:
Post link