This is a tutorial about importing data from a CSV file hosted on cloud storage or a Google Sheet into Stae. We'll walk you through this process by using the Detroit Murals Inventory as a Data Source. To see more information about this data set, visit the Detroit Mural Project and view the murals map on Detroit's public portal.
This Tutorial is for Admins Only
Only accounts with Admin Access can import data.
To start, find the Building Permits dataset from your city's open data portal. If one doesn't exist, you can use another common dataset like 311 issues. For non-admins, if you have a dataset you'd like to upload to Stae, get in touch with us at email@example.com
At the end of this tutorial, you’ll be able to
- Create a new Data
- Import a data set from Google Sheets
One of Detroit's many murals documented by Viranel Clerard of the Detroit Murals Project.
1. Create a Data Source
To create your own Data Source, navigate to "Create Source" on the Sources Page.
- Step 1: Name your source and give it a simple description.
- Step 2: Choose whether you’d like the Source to be public or private.
- Step 3: Search for the Data Type that best matches your Data Source. (For the Detroit Mural Data set, Artwork is the appropriate data type).
- Step 4: Select the "Fetch" option to pull data from Google Sheets.
Select "Create a Source" from the Sources page
Step 1: Add a name and description for your Data Source
Step 2: Publish the source to the city and select your desired visibility. Stae Admins can select different visibility permissions based on their preferences.
Step 3: Select the Data Type that maps to your Data Source. In this example, the mural data will be represented with the Artwork Data Type.
Step 4: Select "Fetch" and then click save to create your new Source.
2. Publish your Google Sheet Data
After creating your Data Source, open the Google Sheet you'd like to import from your Google Drive. Select File > Publish to Web > Select your sheet name and then select CSV on the dropdown options. This will be a live link to your spreadsheet so that data updates at the interval your Importer runs. Copy the URL and then navigate back to Stae.
To export a CSV from your Google Sheet, select File > Publish to Web > select your sheet name and then select CSV under the dropdown options.
3. Create an Importer for your new Data Source
Now that you've created a new Data Source, you're going to create an Importer that will fetch data from an API. If you're working from a different city, the process will be the same as importing data from the city's open data portal.
- To begin, select Create Importer on the Data Source's details page.
- Step 1: Copy the URL from your Google Sheet to the "address" field and test the link to see a preview of the data set you'll be importing. Make sure File and CSV are selected.
- Step 2: Select the code tab and copy the Importer code from the code sample provided and paste it in. Select Test Now to see the result. If you're using the Murals Data, you should see the mapped output on the right side. See the Importer Utilities tutorial for more troubleshooting and documentation.
- Step 3: Select a time interval at which you'd like the Importer to run and collect data
from Google Sheets.
On the Source Details page for the Data Source you just created, navigate to the Importers section and select "Create Importer".
Step 1 - Copy the URL for your data
Paste the Mural Data CSV address from Google Sheets.
Step 2 - Copy and paste the importer code
Below is the code you'll be using to import the CSV from Google Sheets, along with an explanation of each section in the comments. Visit the importers GitHub repo to copy this code over.
Paste the Importer's code from the GitHub repo above to Step 2 and select "Test Now" to get a preview of the output.
Step 3 - Name and describe your importer
Name and describe your importer. Next, you'll assign a regular interval to import your data to Stae. Select "save" when you're ready to import the data.
4. Monitor the Importer's Progress
You can monitor the Importer's progress on the "Jobs" page. Select this link on the Importer section. Select the dropdown on the Importer currently running to see its current progress. There is also a running count that details information about the integrity of the data being transformed.
View of "Jobs" in the Mural Importer.
Job Status Definitions
The job status will give you an idea on the health and integrity of your datasets. When there are errors, the log will return a response on what caused the problem.
Success: The row was successfully imported
Skipped: Matched your filter and ignored this row
Errored: The transform function had an error and the row was not imported. This could be due to data containing bad rows, for example invalid location coordinates. Check the job status log for the importer to trace the error.
Rejected: The row was not imported because it didn't meet a required validation such as having a unique ID or a location. For example, in the Building Permits dataset, some permits were missing locations so those rows would be rejected. Check out the data type's validations as well as the job status log to trace the error.
*“The essential characteristic of the city ... is that it demands participation.”*
Lawrence Halprin, Cities
Have any questions or running into issues with this feature?
Reach us at: firstname.lastname@example.org