DataTable by Smartsheet is how Smartsheet can store millions of rows of data and sync subsets of that data into sheets. Once your DataTable is created and populated with data, you can connect the DataTable to a sheet (or multiple sheets) and allow cross-team collaborations using a single data source.
Create and populate a DataTable
Overview
You can add, edit, or remove fields after you've created the DataTable. DataTable supports text, number, date, and checkbox fields. The field type is important for searching and filtering. For example, if you want to search or filter based on dates, your field must be the associated date type.
Create a DataTable
There are two ways to create a DataTable:
Create an empty data table and populate the data later
Use a Data Shuttle workflow
Option 1: Create an empty DataTable
Using this method, you create an empty DataTable and then use a Data Shuttle workflow to populate the data. Creating the DataTable first gives you more data formatting options at setup.
On the left navigation bar, select Browse > DataTables.
On the top-right corner of the DataTables screen, select Create and then select Create a blank DataTable.
Follow the instructions on your screen.
You can edit the DataTable schema on the Settings page. Be aware changes can affect your sheet connections and Data Shuttle workflows.
Option 2: Create a new DataTable and Data Shuttle workflow
Using this method, you create the DataTable and populate it with an initial data set at the same time.
On the left navigation bar, select Browse > DataTables.
On the top-right corner of the DataTables screen, select Create and then choose from the following options:
OneDrive
Google Drive
Box
Smartsheet attachment
Select Continue to Data Shuttle and log in to Data Shuttle if prompted.
Follow the instructions on your screen to build the workflow. Learn more about creating a Data Shuttle workflow.
After building your workflow, select Publish & Run to generate a Data Shuttle workflow to upload your data and create your DataTable.
You can continue using this Data Shuttle workflow to load new data sets to your DataTable.
Keep this in mind
- The import wizard auto-detects the source file column heading and maps the headers to your DataTable schema.
- This method doesn’t support advanced date format options. The Data Shuttle workflow option only detects the standard ISO format YYYY-MM-DD HH:MM:SS. If your source file contains a different format, create a blank DataTable using Option 1.
- The date format tells the DataTable how to parse the data in your CSV or Excel file when Data Shuttle loads data into your DataTable. For example, if you select 12/31/1999, DataTable expects your dates to load in with the format MM-DD-YYYY. If you load data into a DataTable without specifying the Date format, the expected date format is YYYY-MM-DDTHH:MM:SSZ; for example, 2020-09-17T00:00:00Z
Import data into a DataTable
Once you’ve created a DataTable, you can create DataShuttle workflows to populate the DataTable with additional data.
You can create multiple workflows for a single DataTable, but use the same unique identifier to prevent the addition of duplicates and empty fields on subsequent workflow runs. Learn more about DataShuttle.
The Data Shuttle to DataTable features vary slightly, and we don’t yet allow data to be deleted on import.
- Log in to Data Shuttle.
- Select the plus icon in the left navigation bar.
- Select Upload Data and follow the instructions on your screen.
- For Target (Step 2), select DataTable and proceed with the setup.
Make sure you’ve selected field type Number for any numeric data. DataTable doesn’t interpret special characters (currency symbols, commas, percentage signs, etc.) as numeric values. Don’t include percent, commas, or currency symbols; instead, format percentages as a decimal value (for example, 0.5 instead of 50%).
Smartsheet University subscribers can learn this process in the Create and connect a DataTable course.