DataTable

Access DataTable

To use DataTable, your Smartsheet account administrator must grant you the DataTable role inAdmin Center. To create a connection between a DataTable and a sheet, you must also be an admin or owner of the sheet.

• On the left navigation, select Browse and then select DataTables.

Prerequisites

To use DataTable you must:

• Be aLicensed User
• Have Owner or Admin permissions on any DataTable connected sheets
• Have DataTable permissions enabled inUser Managementby a Smartsheet System Admin

Get started with DataTable

You can build a DataTable from scratch, but the easiest way to create one is to upload the data via Data Shuttle.

1. Create a DataTable.
2. Connect a Data Shuttle workflow to DataTable to import additional data into the DataTable
3. Set up connections to bring records from DataTable into a sheet.
4. Manage your DataTable. (edit field settings, delete records, track and edit connections)

DataTable tips and best practices

Keep these tips in mind as you work with DataTable:

• Your imported dataset can contain no more than 2 million rows and no more than 200 fields/columns.
• If you perform lookups and updates to your data or merge in new rows, your data must contain a column with a unique identifier for each data point. The unique identifier keeps your data consistent. When creating multiple workflows into a DataTable, use the same field in each workflow for your unique identifier to prevent empty fields or duplicate rows.
• You can connect up to 100 sheets to your DataTable.
• When connecting a DataTable with a sheet via Data Shuttle, Smartsheet limitations still apply.
• Data should flow from your original source into DataTable, and down into subsets of sheets. DataTable cannot be used to sync data back from your sheet. Use Data Shuttle to offload sheet changes back to your original source, if required.
• The connection ID is a helpful piece of information if you need support. To find the ID, go to the Connection Summary screen and select the arrow icon between the DataTable and sheet names.
• Data Shuttle workflows into a DataTable have different options to those directly into a sheet. Data Shuttle workflows:
  • Cannot schedule workflows to run On Attachment to a sheet
  • Cannot add advanced date format options
  • Cannot replace all data with a new import file
  • Cannot delete rows that do not match filter criteria

Prerequisites

To use DataTable, you must:

• Be aLicensed User
• Have Owner or Admin permissions on any DataTable connected sheets
• Have DataTable permissions enabled inUser Managementby a Smartsheet System Admin

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, or use a Data Shuttle workflow.

Option 1: Create an empty DataTable

Using this method, you will 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.

1. On the left navigation bar, select Browse > DataTables.
2. On the top right corner of the DataTables screen, select Create, then select Create a blank DataTable.
3. Follow the instructions on your screen.

You can edit the DataTable schema on the Setting page at any time. Be aware changes can impact your sheet connections and Data Shuttle workflows.

Option 2: Create a new DataTable and Data Shuttle workflow

The import wizard auto-detects the source file column heading and maps the headers to your DataTable schema.

This method does not support advanced date format options. The Data Shuttle workflow option will only detect the standard ISO format YYYY-MM-DD HH:MM:SSZ. If your source file contains a different format, use option one above.

1. On the left navigation bar, selectBrowse>DataTables.
2. On the top right corner of theDataTablesscreen, selectCreate, then selectFrom OneDrive, Google Drive, Box, or Smartsheet attachment.
3. Follow the instructions on your screen.

Import data into a DataTable

Once you have a created 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.

1. Log into datashuttle.smartsheet.com
2. Select the plus icon in the left navigation bar.
3. SelectUpload Dataand follow the instructions on your screen. For Target (step two), select DataTable and proceed with the setup.

Make sure you have selected field typeNumber任何数值数据。数据表不解释special characters (currency symbols, commas, percentage signs, etc.) as numeric values. Do not include percent, commas, or currency symbols — instead, format percentages as a decimal value (e.g. 0.5 instead of 50%).

---

Smartsheet Universitysubscribers can learn this process in theCreate and Connect a DataTable course.

Prerequisites

To use DataTable, you must:

• Be aLicensed User
• Have Owner or Admin permissions on any DataTable connected sheets
• Have DataTable permissions enabled inUser Managementby a Smartsheet System Admin

Connection methods

There are two ways to connect a sheet to a DataTable:Add & update modeandLookup mode.To create a connection, you must have Admin, Owner, or Editor rights on a sheet.

• Add & update mode: This connection type pulls subsets of the data into a sheet from a DataTable. You can use filter criteria to narrow down the subset of data, map the DataTable data into the sheet columns, and control how changes to the DataTable apply to the sheet. Use add, update, and remove options to ensure your sheet matches the filter criteria defined in the connection. You can count on your sheet being up-to-date with the latest data from the DataTable.
  
• Lookup mode这从DataTabl连接类型合并数据e into a sheet based on a unique identifier, similar to a VLOOKUP. You will select a unique identifier to look up values in the DataTable, and choose which sheet columns to map back to the DataTable. This method allows you to use a DataTable as a reference for processes running in a sheet.
  
  In Lookup mode, the connection will overwrite changes made in the sheet with data from the DataTable. Your sheet will always match what’s in the DataTable. Because of this, the connection will lock the mapped columns, so people can’t edit the data.
  

Changes made in the sheet do not sync back to the DataTable.

Add & update mode

Step 1: Select the table

1. Go to the sheet you want to connect with a DataTable, select theConnectionsmenu, and then selectConnect to a DataTable.

2. SelectAdd & update mode.

3. Select the DataTable you want to use. The list includes DataTables you created and those you’re shared to as an Admin or Viewer.

Step 2: Filter your data

Filter the data you will sync from the DataTable into your sheet. Your sheet is limited to 500,000 cells of data and 20,000 rows, while the DataTable will likely have significantly more data.

You must have at least one filter criterion; you can add up to ten criteria to your connection. You can filter your data using various criteria depending on the field type.

1. SelectallinSync records that meet all the conditionsto toggle between requiring all filters to be met andat least one condition. Select+Add New Conditionif you want to add more conditions.
2. Set your filters and selectNext.

Step 3: Map data from the DataTable into a sheet

1. Toggle on the fields you’d like to map, then select the sheet column you’d like that information to port to. The names in your DataTable and sheet do not need to match.
   The connection adds a new column to your sheet called DataTable Record ID. This ID tracks rows added from the DataTable. This column is locked and hidden by default. If you change values in this column, the corresponding row will stop receiving updates.
2. SelectNext.

Step 4: Set connection behaviors

1. Toggle any combination of behaviors:
   Add rows as they are added to the DataTable
   Update rows as they change in the DataTable
   Remove rows that longer match the filter conditions
2. When you’ve toggled on your desired set of behaviors, SelectNext.

Step 5: Review the connection

You will see a summary of the connection you’ve built. To revise your connection settings, selectBack.

SelectCreatewhen you’re done. Any data that matches the filter criteria will be automatically added to your sheet. It may take several minutes, and you may need to refresh your sheet.

Lookup mode

Step 1: Set up the connection

1. Go to the sheet you want to connect with a DataTable, select theConnectionsmenu, and then selectConnect to a DataTable.
2. SelectLookup mode.
3. Select the DataTable you want to use. The list includes DataTables you created and those you’re shared to as an admin or viewer.

Step 2: Select a unique identifier

1. Select a unique identifier to match rows in your sheet to records in the DataTable. Be sure to select a DataTable field that contains unique values. This value is typically a string of numbers and letters from the dataset, such as a store code, employee email address, or database record ID. If the DataTable has records with multiple matching unique identifier values, the connection will use the data from the first record it finds. In most cases, this will be the lowest DataTable record ID.
2. After you map your unique identifier, selectNext.

Step 3: Match Fields

1. Toggle on the fields you want to map, then select the sheet column you’d like that information to port to. You must select at least one field. The names in your DataTable and sheet do not need to match.
2. SelectNext.

Step 4: Review the connection

You will see a summary of the connection you’ve built. To revise your connection settings, selectBack.

SelectCreateto finalize your connection. The connection will automatically run and add lookup values for any unique identifiers with a match in the DataTable. It may take several minutes and you may need to refresh your sheet.