Clean up data in a workspace
Paste, upload or pull data into a spreadsheet, tidy it, then turn it into a table.
On this page
Create a workspace
Click Workspaces in the sidebar, then New workspace. Or click the plus next to Workspaces.
A blank spreadsheet opens. Type or paste into it, or click Upload CSV / Excel.
Workspaces
Connect, clean, and shape data before it becomes a real table
Add a data connection
Click Workspaces in the sidebar, then Data connections. Only an admin sees this button.
For a database, click SQL database. Type a Name, the Host, Port, Database, Username and Password. Leave Use SSL on. Untick Verify certificate only for a self-signed certificate. Click Save.
For an API, click REST API. Type a Name and the base URL. Type the Header name the API expects, the Scheme if it needs one, and the API key / secret. Click Save.
Data connections
Analytics DB
db.example.com:5432/analytics
Pull the data in
Open the workspace. Click Connect to SQL / API. Pick the Connection.
For SQL, click Browse tables and click a table, or type a Query. Click Run. Only a SELECT or WITH query runs, read only, and a query returns at most 1000 rows.
For an API, type the Path after the base URL and click Fetch. The response must be JSON: either a list of records, or an object holding a list, such as results or records.
Check the preview. Click Pull into workspace. The button then reads Refresh from source so you can pull again later.
Pull from a connection
Only SELECT/WITH queries are allowed. Every query runs read-only.
Tidy the data
Click a column header for Trim whitespace, Find & replace or Delete column. Click the plus at the end of the headers to add a column.
Click Remove blank rows or Remove duplicate rows under the grid. A row flagged as a possible duplicate shows a marker on the left.
Use the ribbon for formatting, number formats, conditional formatting and formulas.
Turn it into a table
Click Turn into a table.
Pick New table and type a Table name, or pick an existing table.
For each column, choose New field, Existing field or Don't include. Click Create table.
Turn into a table
Chart it instead
Open a dashboard, click Edit layout, then Widgets. Add a chart widget and pick the workspace as its source.
The chart reads the workspace as it is. Nothing is copied into a table.
Settings for common apps
| Term | What it means |
|---|---|
| Postgres on Supabase, Amazon RDS or Heroku | SQL database. Host and port from the provider's connection details, usually port 5432. Use the read-only user if the provider offers one. Keep Use SSL on. |
| Salesforce | REST API. Base URL https://yourdomain.my.salesforce.com. Header name Authorization, scheme Bearer, secret: an access token from a connected app. Path /services/data/v60.0/query?q=SELECT+Name,Industry+FROM+Account. The records come back under records. If Heroku Connect syncs your Salesforce data to Postgres, use a SQL database connection to that database instead. |
| HubSpot | REST API. Base URL https://api.hubapi.com. Header name Authorization, scheme Bearer, secret: a private app access token. Path /crm/v3/objects/contacts?limit=100&properties=firstname,email. The records come back under results. |
| Airtable | REST API. Base URL https://api.airtable.com. Header name Authorization, scheme Bearer, secret: a personal access token. Path /v0/<base id>/<table name>. The records come back under records, with each record's values under fields. |
| Any API that uses an API key header | REST API. Header name as the API documents it, such as X-API-Key, scheme left blank, secret: the key. The path is the endpoint that returns a list. |