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! It not only lets you see you data on a map – you can also build compelling interactive dashboards and identify trends within your data.
This article will explain the process of taking a Microsoft Excel spreadsheet or a CSV file with address information and walk you through the process of getting data into SpatialKey.
We will start with a file containing real estate sales data from Sacramento, California as reported by the Sacramento Bee. This file contains the address, city, ZIP code, state, number of bed, baths, square footage, type, sale date and price for 985 real estate transactions in the Sacramento area as reported over a 5-day period. This file contains address level information that you can choose to geocode during the process, or you can use the existing latitude and longitude coordinates in the file.
You can download this data (download .csv file) if you want to follow along.
Formatting Your Dataset
First, we need to get our data into CSV (comma-separated values) format if it isn’t already. The Sample Real Estate data is already in CSV format so we are all set; but if you are using your own data, here are some steps you can follow.
Is your data in a database? Refer to this article on Exporting Data From Database to CSV File for tips on converting your data into CSV format.
Is your data in MS Excel? You will need to export the data from Excel by saving the file as a CSV. Make sure you have cleaned up your data before exporting:
- The first row should include column header names
- Make sure there are no extra columns or characters outside of the ones you want to export
- Delete any columns you will not be using
To export your data from Excel, go to File → Save As and choose the “Format” to be a CSV file.
After saving, we are ready to jump into SpatialKey and import our data.
Importing Your Data
After logging into SpatialKey go to the Manage Data tab and click to “Upload a Dataset”.
The first screen contains links describing what files types are supported for upload and also displays your organization’s limits, so you know how many records and columns are allowed for each dataset. Select to upload a dataset and navigate to your file.
Once you select your dataset for import, SpatialKey will do an initial upload of the file so we can read from the columns in future steps. While the file is uploading, you are asked to identify whether your dataset is mappable within a single country or multiple, or whether it isn’t mappable at all. Since our sample file contains records in Sacramento, CA, we will select that we have mappable data within the United States.
Next, identify what kind of mappable information you have. Does your dataset contain latitude and longitude coordinates already or does it contain addresses that need to be geocoded? Our sample data contains latitude/longitude coordinates so we will select that option.
Next, we will identify the latitude and longitude columns within the dataset.
The next step is the thematic configuration screen. We will proceed with the default settings. Since the U.S. boundaries are enabled, we will be able to create thematic maps that show the distribution of a single metric by either state, county or postal code. For more information and an example, check out this article on Thematic Mapping.
The next step can be confusing at first, but you will get the hang of it quickly. Most of your data will fall into three data types: Text, Number or Date. Take the time to look over each column, what you choose here can have implications when viewing your data in a dashboard (e.g. you can only sum Numeric fields). You can see sample data, specific to each column, by hovering your mouse over the column name to validate if the importer guessed your data types correctly. This is a very important step because data type adjustments cannot be made after your data has been imported into SpatialKey.
You may have noticed a “Preview Data” option at the top of all screens in the Data Upload Wizard. Click this to preview the top 500 records from your dataset. This is helpful when mapping columns and validating that you have selected the correct dataset.
The final step is to name your dataset, request an email notification when the import completes, and opt to share this dataset with everyone in your organization. Part of the power of SpatialKey is being about to easily share and collaborate with others in your organization. You are given the option to share this dataset with others; but don’t worry too much about your choice for now, you can manage permissions for datasets later.
You are done setting up your first import job! You can close this screen and continue working in SpatialKey until the upload finishes.
That’s all there is to getting data into SpatialKey, a simple step by step process. The real fun begins when you Open Your First Dashboard.