Data in a worksheet can be updated and added via a spreadsheet. Accepted file formats are .xlsx and .csv
Please note that it is possible to both create a new worksheet from a spreadsheet, as well as update an existing worksheet with new data. This article is only concerned with the latter operation, updating data in an existing worksheet.
TABLE OF CONTENTS
Unique identifier
For a row in a spreadsheet to be matched to a record in a worksheet, there needs to be a unique identifier for that record. This can be any field, and the platform will allow a record to be matched on any field, but in most use cases this will be a short code, a number or a text string.
The spreadsheet being used as the data import source needs a column containing the unique identifier for each record. It does not need to have the same column title as the worksheet field title.
For clarification on terms, a row in a spreadsheet can be seen as the equivalent of a record in a worksheet. A column in a spreadsheet is the equivalent of a field in a worksheet.
Other than the identifying column, the spreadsheet columns do not need to match the worksheet fields. During import, users may match a column to an existing field or exclude a column from being imported.
It is not possible to create a new worksheet field during import. If there are columns in the spreadsheet that do not exist as a field in the worksheet, these need to be added to the worksheet before data can be imported into it.
If a row in the spreadsheet contains data that cannot be matched to an existing unique identifier, a new record will be created in the worksheet.
Importing spreadsheet data
The import dialog is accessed from the ‘Manage’ button in the Worksheet menu, via the ‘Import items’ option. This opens a spreadsheet upload dialog. Once a spreadsheet has been selected, this is uploaded into the data import dialog. This dialog displays the columns and content of the spreadsheet. Each row in the spreadsheet is displayed in the dialog to allow the user to scroll through the data before import to check if there are any errors highlighted.
The Import dialog
Before data can be imported into a worksheet, certain parameters need to be specified. This can be done by the user directly in the import dialog before starting the import.
There are four configuration options in the import dialog:
Source tab | If the imported spreadsheet has more than one tab, it is possible to choose which tab should be used as the source for the data import. By default, the dialog will load data from the first tab in the spreadsheet |
First row content | If the source spreadsheet has a header row, this can be specified by choosing ‘Column headings’. The first row in the source spreadsheet will not be imported when this is selected.
If there are no headings in the spreadsheet and all rows should be imported, the ‘Data’ option should be selected. |
Import behavior | During import, rows in the spreadsheet will be matched against existing records in the worksheet. If there are matches, these can be updated with new data from any column in the source spreadsheet other than the column containing the unique identifier.
If ‘create and update’ is selected, spreadsheet rows that can be matched against existing records will be updated, and rows that cannot be matched will be created as new records in the worksheet.
If ‘create’ is selected, rows that cannot be matched to existing records will be created as new records, but rows that can be matched will be ignored and not imported.
If ‘update’ is selected, rows that can be matched to existing records will be updated, but rows that cannot be matched will be ignored and not imported. |
Match on | The ‘Match on’ option indicates which column should be used as the unique identifier that records are matched against. The following field types may be used as unique identifiers: Short text; Email; Short code; Sequence number; Added by; Updated by; Hyperlink: Text. If the worksheet has been configured with any of these field types, the field will be made automatically available in the drop-down menu of the ‘Match on’ button.
If the spreadsheet used for data import has previously been exported from an Opus 2 project, there may be a column titled ‘Id’ in the spreadsheet that can be used for matching records. This column contains the database Id of each record and is included in spreadsheet exports by default. This field is not visible in the Worksheet. The main use case for this ‘Id’ field is if data should be exported from Opus 2, manipulated in an external application and then imported back into the same Worksheet. |
Matching spreadsheet columns to worksheet fields
The import dialog allows users to match columns in the uploaded spreadsheet to fields in the active worksheet.
In the dialog, each column title has a drop-down option underneath it that allows the user to choose the worksheet field that the column data should be imported into. The importer will attempt to match these columns automatically, but changes can be made manually before completing the import if the matching is incorrect.
If no automatic matching is possible, the drop-down button will simply display ‘select’. The user must select an appropriate worksheet field to match the column to if they want to import the data. If no field is selected, that column will not be imported.
If a match has been automatically selected but the user does not wish to import the data in that column, there is an option in the drop-down menu to ‘ignore’ that column. If the option is set to ‘ignore’, the data in that column will not be imported.
Matching spreadsheet columns to remote worksheet fields
If the active worksheet has a remote field in it, data being imported can be matched to existing data in the remote worksheet.
As an example of this, most Opus 2 dispute case management solutions contain two preconfigured worksheets, ‘Chronology’ and ‘Characters’. The Chronology worksheet, in most use cases, has a remote field that allows for the display of multiple records from another worksheet, in this case the Characters worksheet. When importing data into a Chronology, it is possible to match references to records in the Characters worksheet that will then be established as remote records in the Chronology worksheet.
If a field in the worksheet is of either ‘single remote record’ or ‘multiple remote records’ type, the import dialog allows for the ability to match all or some fields in the spreadsheet to the remote record. This is achieved by showing the same field with multiple parameters in brackets, where each parameter is a specific field in the remote worksheet.
When matching a spreadsheet column to the remote field, the first option will state the title of the field only, with no brackets. This matches the data in the spreadsheet cell to the principal ID of the remote field. If the data in the cell can be matched to data making up the principal ID of the remote worksheet the import can be done with no need to identify further parameters.
If data in the spreadsheet cannot be matched against the principal ID of the remote worksheet, it is possible to choose other fields in that worksheet to match the data against. The remote worksheet fields that are available to be matched against will show in the drop down in brackets after the title of the remoted worksheet field.
Please note that not all field types in a remote worksheet are available to match data against. The most common field type for matching data are fields other than principal ID is short text .
Matching spreadsheet columns to fields in a subtype
If the active worksheet has a subtype in it, it is possible to match columns in the spreadsheet to fields in the subtype and the parent worksheet. This requires an identifier in the parent worksheet to determine which record in the worksheet should be the parent of each individual sub-record.
When importing records into a worksheet that has one or more subtypes, a new option, ‘Import into’, becomes available in the import dialog. This allows the user to specify if the data in the spreadsheet should be imported into the main worksheet or into the subtype. All subtypes in the worksheet will be listed as options in the drop-down menu.
If a subtype is selected, another option becomes available, ‘Identify parent by’. This option specifies the field in the parent worksheet that should be used to match records against. By default it will be set as the principal ID of the parent worksheet, but other fields of type short text can also be used to identify the parent.
If a subtype is selected, a ‘parent’ option also becomes available in the drop-down menu of each column, to allow the user to specify which column contains the parent ID that should be used to match each sub-record against.
The data that is being imported into a subtype does not need to be contained in a separate tab on the spreadsheet. If the spreadsheet contains a column that can be used to identify the parent of each sub-record it is possible to import data into either the parent worksheet or the subtype from the same spreadsheet.
Importing data into both a parent worksheet and a subtype in the same import session is not possible. If the spreadsheet contains data that pertains to both types, two separate import sessions are needed.
Data matching errors
Sometimes it is not possible to match all data in a spreadsheet to the relevant Worksheet field. If this happens, the cell containing the data that cannot be matched will be highlighted in either orange or red, indicating that there is a problem with matching that particular piece of data. Usually this is because the data is of a type that cannot match the type expected by the worksheet field.
If the cell contains data that cannot be imported into the field it has been matched to, a red highlight will indicate that the data in this individual cell will not be imported.
For instance, attempting to match the text ‘apple’ with a field of type ‘Date’ would show a red highlight error code. If hovering over the cell, the text ‘could not read date:Red’ will display to provide more context.
Note: Even if one cell in a row has a red error highlight, the remaining cells that make up the full row may still be imported.
If some but not all the data in one cell can be imported, an orange highlight will indicate that there is an error, but that import may still be possible.
For instance, attempting to import a complete timestamp into a date field that has not been configured to allow for time as well as date will result in an orange error code. If hovering over the cell, the text ‘Times will not be displayed in this column’ will display to provide more context.