How to modify and edit data tables

Liz Kiernan
  • Updated

Learn how to edit and manage data tables in a Terra workspace: edit individual cells, add or delete table rows or columns, sort by or hide metadata (columns), etc. 

Edit in Terra: Step-by-step instructions 

You can edit and manage tables in the Data page of your Terra workspace much like you would any other spreadsheet. For example, if you wanted to change the subject ID (or other metadata), add or delete particular subjects, add or delete a column altogether, you could do so in the table below:
Screenshot of a data table in the Data tab of an example workspace.

Click for step-by-step instructions of how to do each action in Terra.

  • To rename a table, click the three vertical dots to the right of the table name and fill in the New Name field in the form. 
    Modify-data-tables_Rename-table_Screen_shot.png

  • You can edit individual cells only by clicking on the pencil icon in the cell you want to change and working directly in the popup that appears.
    Modify-tables_Edit-individual-cell_Screen_shot.png

    Specify the metadata type

    i.e. string, reference, number, or BooleanModify-attribute_Screen_shot.png

    Generate an array

    Select Attribute is a list and add items.Modify-attribute_Attribute-is-a-list_Screen_shot.png

  • 1. Select the rows to edit.

    2. Click the Edit icon (above the table, left corner).
    Modify-table_Edit-selected-rows-menu_Screen_shot.png

    3. Choose Edit selected rows from the menu.

    4. Select the column to edit.
    Modify-table_Select-column-to-edit_Screen_shot.png
    5. Edit metadata type and value in popup.
    Modify-table_Change-selected-values-menu_Screen_shot.png

    Don't forget to click the Save Edits button!

  • 1. Click Edit (pencil icon) at the top of the table.

    2. Select Add row from the menu box.
    Modify-table_Click-on-Edit-and-Add-row_Screen_shot.png

    3. Add ID and metadata type and value for each column (expand column header for menu).
    Add-data_Add-a-new-row-menu-for-each-column_Screen_shot.png

    4. Click the Add button at the bottom of the form.

  • 1. Check the box to the left of the row(s) you want to delete. To delete the entire table, select all rows from the dropdown.

    2. Click Edit (pencil icon) at the top and select Delete Data.
    Modify-data_Delete-rows-in-table_Screen_shot.png

  • 1. Click the three vertical dots at the top right of the column you want to delete. 

    2. Select Delete/Clear column from the menu.
    Modify-table_Delete-metadata-columns_Screen_shot.png

  • 1. Click the three vertical dots at the top right of the column you want to delete. 

    2. Select Sort Ascending/Descending from the menu.
    Modify-table_Sort-by-metadata-columns_Screen_shot.png

  • If you just want to hide columns, you can do that by clicking the gear icon at the top right of the table. This will reveal a popup where you can choose what columns of metadata to show. You can even choose to save this view, especially useful for collaborators working in the same workspace.
    Modify-table_Save-column-selection_Screen_shot.png

  • Edit metadata (column cells) in bulk

    If you want to update the values in a column for several (or all) rows in a table - if the study name or dates have changed, for example - follow the instructions below.

    1. First select the row(s) to edit. 

    2. Click Edit (pencil icon) at the top of the data table.

    3. Select Edit selected rows in the menu.

    4. Select the attribute (column to edit)
    Modify-table_Add-metadata-column_Screen_shot.png

    5. Enter the new value and click Save changes 
    Modify-table-Edit-metadata_Screen_shot.png

  • 1. Starting in the workspace you want to copy from, select the rows you want to export (or all rows, if you want the entire table).
    Modify-tables_Export-data_Screen_shot.png

    2. Click Export at the top and select the option you wish
         - Download as TSV
         - Export to workspace
         - Copy to clipboard

    There is no cost to copying data in a table to a workspaceExporting a data table to another workspace or downloading the TSV metadata does not incur any egress costs because you are not moving/copying the files in the table, merely copying the metadata and references to the new workspace.

    Note that readers of your workspace will need read access to the bucket or workspace which actually owns the data to access files linked in the table.

How to edit or add data in a spreadsheet editor

If you need the full functionality of a spreadsheet, you can modify a TSV outside of Terra following the step-by-step instructions below.

Step 1. Download the existing table

1.1. Select rows to download (edit) in check boxes at left. 

1.2. Click Export in the menu above the table.

1.3. Select Download as TSV from the popup.
Modify-table_Download-as-TSV_Screen_shot.png

Step 2. Edit and save the TSV file using your favorite spreadsheet editor

2.1. Open in your favorite spreadsheet editor and edit.

Beware of unallowed characters! Note that ID cells (i.e. the first column of your table) can only include alphanumeric characters, "-" and "_" and no spaces. Single quotes are not allowed (use double quotes instead). 

All formatting will be lost when you save the file in TSV format.

2.2. Save the file as "tab-separated values" or "tab-delimited text".
Your editor may give you a warning, but we assure you, it's fine! Also, Terra will completely ignore the name you give the file. It's the "root entity" in the first column header that determines the table name in the workspace.
Data-QuickStart_Part2_Save-as-Tab-delimited-text.png

A note about  .tsv  versus  .txt  file extensions Depending on what spreadsheet editor you use, when you save in the proper format your spreadsheet may have either a ".tsv" or a ".txt" extension. Terra will accept either one.

Step 3. Upload the TSV file to your workspace

Click the Import Data button at the top left of the workspace data page and follow the directions.
Modify-tables_Upload-TSV_Screen_shot.png

TSV uploads are additive

 

TSV uploads are additive: a note about overwriting table rows When your TSV load file has the same entity (name) as a table already in the workspace, you may get an error message when you try to upload about overwriting data.

Terra will only overwrite data rows with the same ID (in the first column). If the TSV (load) file includes different IDs, these rows will be added to the existing table.

Uploading tsvs with missing columns or rows does not remove that data.  This allows you to upload new TSVs that only contain the rows or columns you want to be added (but must include the entity ID column which is used as the key). 

Making tables programmatically (i.e. scripting)

You can automate the process of making and modifying tables using a special API called the FIreCloud Service Selector (FISS). Learn more in Managing data and automating workflows with the FISS API.

Next steps: Adding workspace-level (reference) data

Once you have your data entities organized in a data table, you might want to add reference data to the workspace so that you can perform data processing like alignment or calculate quality control metrics. Learn how to add workspace-level metadata to a table in Creating Workspace Data tables.

Additional resources

 

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.