Mapping Microsoft Excel Data with SpatialKey
Have you ever wanted to map your sales data, customer data or customer leads from your excel spreadsheets? This can be a challenging task and with most solutions you are limited by the number of points you can place on a map. What if the process from spreadsheet to map only took a matter of minutes and allowed you to share this data out to others? This is possible with SpatialKey and it not only lets you see you data on a map you can build compelling interactive reports and identify trends within your data. This article will explain the process of taking an excel spreadsheet with address information and walk you through the process of getting data into SpatialKey.
We will start with a spreadhsheet containing real estate sales data from Sacramento California. The spreadsheet contains the address, city, zipcode, state, number of bed, baths, sqaure footage, type, sale date and price. This CSV contains a list of 985 real esate transactions in the Sacramento area reported over a 5 day period, as reported by the Sacramento Bee. The larger (250k+ records) file is highlighted in some of our feature videos. If you are not yet ready to start working with your own data you can download this sample CSV file here and experiment within SpatialKey
Note this file has address level information that you can choose to geocode during the process, or you can use the existing latitude/longitude in the file in the steps below we will geocode using the address, city and zipcode.
Lets get started with the process! The first thing we need to do is export our data from excel by saving the file as a CSV (Comma separated) file. Make sure that you have cleaned up your data prior to exporting.
Cleaning up your data:
- The first row should be column headers with the name of the column (see picture above)
- Make sure there are no extra columns or characters in other columns outside of the ones you want to export
- Delete any columns that you will not be using
Within Excel you can go to File -> Save As and choose the “Save as type” to be a CSV file.
After saving we are ready to jump into SpatialKey and import our data.
After logging into SpatialKey go to the datasets tab and click on the “Upload a DataSet”. Click the “Upload a CSV file”. After your initial upload you will be asked to give your dataset a name a seen below
Next, you get to see a preview of your data so that you can validate that it was brought into the system correctly.
The next step can be confusing at first but you will get the hang of it quickly. Basically most of your data will fall into three categories a String (words/text), Number (I am pretty sure you know what that is) or a Date. Look over each column and do not just skip past this. You can see sample data by hovering your mouse over the column names to see if the importer guessed your datatypes correctly.
The next step is where SpatialKey stand out above the rest. Geocoding can be a complex process but if your data is in the proper format SpatialKey makes it really easy. In the screen beow we have three options, we can geocode using the address information from your data, use existing latitude and longitude fields if you already have them or bring it in without geographic information. In the latter case you will not be able to use the mapping features of SpatialKey. In this case we will use the first option “Geocode using address fields”
In this screen we will choose the columns from our data that we will use for geocoding. Take care in choosing these options since they will affect the results of geocoding. SpatialKey has already made some choices for us you should review each one and make changes as necessary. We do not need to fill in every option but the more you have the more accurate the results. If you do not have a column in you data you can manually enter the value. for example if you only had the street in your data and all data points were in the same city you could manually enter the city and state values. We will accept the defaults below.
SpatialKey geocoded our data and in the screen below we see the results. We are provided with a summary showing how accurate SpatialKey was able to geocode the results. The results below are great since it was able to geocode nearly all of them at the exact address level.
That is all there is to getting data into SpatialKey, a simple step by step process. The fun part starts when you can open up this data in a report. We will not walk through the creation of a report in this article but an example report is shown below using the Sacramento sales data.
Follow Us: