The spreadsheet data loader allows you to load address-level data from a spreadsheet file to view in PolicyMap.
Be sure to read our tips on setting up your CSV data file for use in uploading. We know that all files can be different and all users will have a different way of storing data. Our tips are to help make sure your data files process and load easily.
With your data file setup correctly and saved as a CSV file, you are ready to use the Data Loader. Log into your PolicyMap subscription account and select the Data Loader tab. The Data Loader is available to all subscribers. With the Create New Dataset selected, follow the steps to setup and upload your dataset.
- 1. Method to Load Data: Select “Upload Spreadsheet of Sites”.
- 2. Name of Dataset: Enter the name of your dataset.
- 3. Share with: Select how you would like to share this data:
- Myself Only– Available to only your account.
- Publicly Shareable – Datasets can be share with any user through the Share features; link, email, embed maps, or social media.
- Select Individuals – Share the dataset to specific people by entering their email address. Please enter one email address per entry box.
- 4. Icon: Choose a custom shape, size, or color for the icons to represent your data. This allows users to create multiple datasets with different icons and colors, each could represent a unique characteristic in the dataset. This customization is similar to the “filtering” option with our premium subscription datasets.
- 5. Import CSV File: Finally, browse for the file to upload.
After selecting the button, you will open the Import Sites page:
The Import Sites page will display the column names from your spreadsheet. The top section identifies the columns which will be used to geocode (locate) an address.
The geocoder will use the following combination to successfully locate an address: Latitude & Longitude OR Address 1 & Zip Code; Address 1, City, & State. Use the drop down menus for Data Type to identify the Address 1, City, State, Zip Code, or Latitude and Longitude from your dataset. If the incorrect column name is being used to geocode an address, be sure to check the “remaining columns” list below; find and select the Data Type for the correct column.
If you do not see all your fields in the Import Sites page or missing required Data Types to geocode an address, select the Delete Sites and Reimport button to exit the Import Sites page. Re-check your data file to make sure it has the correct labels for each column and check the Spreadsheet Tips page.
The Import Sites page also allows you to identify if a data column represents a currency or percentage. The data loader will initially attempt to identify columns which have only numerical values and then set the Data Type to “number.” Select the $ or % radio buttons if the corresponding column represents that type of data.
When you have identified the columns for geocoding and set the data types for each column, select Next to start geocoding.
After the Data Loader has geocoded your addresses, the Add/Edit Sites window will open and display a table of all addresses and the data for each address. You can edit any field for an address, add or remove addresses, set unique features, and see results of the geocoding process.
The top left section will give you the results of the geocoding process. Please note, even at PolicyMap, we return a average 75%-80% match rate when geocoding addresses. The section will also give you great tips on how to interact with the table below.
For addresses which fail the geocoder, we use a process of “hand geocoding” each address. Here are some helpful write-ups regarding the PolicyMap geocoder;
- Data Loading and Geocoding: Part I
- Data Loading and Geocoding: Part II
- Data Loading and Geocoding: Part III -Using Coordinates
The top right section gives users optional features for search and display. The first will give users the ability to display a label above each icon on the map. Select which data field from the drop down menu. Due to usability and performance, this feature is only available for datasets with 250 addresses or less. The second option turns on the ability to search for an address. Simply select which column of data to search by.
The first four columns in the table are default from PolicyMap:
- Geocoded – Each color will confirm if the address was successfully geocoded; Green means the address was found, and Red means the address failed to be found during geocoding.
- Row – This will show the order that each address was imported into the Data Loader.
- Latitude & Longitude – These are the coordinates that were found for a successfully geocoded address.
Column headers can be selected to re-sort, normally in alphabetical or numerical order; the default sort is by Row. All text boxes (except for column headers and latitudes and longitudes) can be edited. Selecting the text will release the field and allow you to edit. The ability to re-sort a column and edit text, together can be very helpful for addresses which were not successfully geocoded. Simply sort by the Geocoded column to display all unsuccessful address first, and then select the address field for each row to enter missing addresses, remove address information that could cause the failure (e.g. Apt #, PO Box, etc.), or change the city, state, or zip code.
Columns which represent percentage values will have to option to increase or decrease the decimal be a factor of two. Click the corresponding button to move the decimal.
he last column for any row will allow you to delete a row. Simply select the close button.
At the bottom of the Add/Edit Sites table, users will have a variety of functions, including:
- Add Row – This will add an additional row for an address to the table.
- Delete Sites and Reimport – This is only available for newly created dataset and will allow you to delete the current dataset to re-upload another file. This is helpful if; columns are not in the correct order, headers are labeled wrong, data in the tables are incorrect, or you want to adjust your data file before saving. Once you save the dataset, this button will not be available.
- Download Sites – Users can download the current dataset in the table. This will include the latitude and longitude for any successfully geocoded address.
- Save – Saving will save any edits and settings during the session.
- Save & Close – This will save any the session and close the Add/Edit Sites page. Taking you back to the initial Data Loader page.
The Set Access to Fields tab allows you to select which fields are visible when viewing an address on the Maps or Tables page. By default, all fields are available to all users and yourself. Simply check off which fields you wish to share. Select All to check or uncheck all fields in a column. The Display for Public or Display for Individual Users columns are only accessible if that sharing option was selected during the setup of the dataset. You can also update the viewing options at any time. Please note; any changes to how the dataset is shared on the setup page will reset all fields in the Set Access to Field page.
On the top right, users have the option to allow the dataset to be downloadable. This option ties into the share fields function on the tab, so whichever fields are marked to share will be the only fields downloaded.
When you are finally done with editing and customizing your dataset, select the Save & Close button on the bottom. This will close the Add/Edit Sites page and return you to the setup page. The interactive map on the right will show all geocoded address. Users can click onto an icon to see all the shared data for a site.
Users can make additional edits and customization to the dataset including; changing the name of the dataset, how/who the dataset is shared with, or the icon type, color, and size. The “Edit Sites” button will re-open the Add/Edits Sites page, allowing users to make edits to the current dataset or add additional address. The “Load Dataset on Maps Page” will open the dataset onto the Maps page giving users the ability to use all the unique features and data layers on PolicyMap with the dataset.
While we created the Spreadsheet Data Loader to be easy to use, we understand that not all experiences will be the same. If you want to learn more about the function, think about attending one of our free online training sessions (select a date from our calendar here). If you have questions or just need some additional help, please contact us at any time.
Please note, PolicyMap will work on all supported browsers but your experience will vary depending on what browser you use. If you are using Microsoft’s Internet Explorer, we recommend you upgrading to version 9.0 and up.
We appreciate the feedback from our users, so after using the tool and uploading your datasets, please complete this survey and help us make the Data Loader perfect for your needs.