You can import iSheet records and make changes to an existing iSheet from Excel, or create a new iSheet from an Excel file.
Create an iSheet from an Excel file
As of June 2022, you can create an iSheet from an Excel file.
This allows you to:
- Automatically create iSheets columns for each Excel column
- Assign content types for each column
- Add new data in bulk (this is optional)
Check your Excel file
It is recommended that you check the Excel file to confirm the format will import correctly.
Up to 50 columns and 500 rows may be imported. The maximum file size is 15 MB.
Supported data types
Supported data types from Excel files:
- text
- email address
- number
- date
- choice (data validation)
Cells that use other formats are not imported.
Note on merged cells - the contents of merged cells are imported as if they were in the top-left cell only; other cells in the merged area will be blank.
Select the Excel file
Open Admin, then iSheets.
Click Add, then select From an Excel file.
The Import from an Excel file window opens:
Click Browse to find the Excel file, or drag the file to the browser window, then click Import.
The file is uploaded and checked for viruses. Click the red X to cancel the process.
After the files are imported, click Review to check and manage rows and columns for the new iSheet.
Review imported data
The Review window allows you to define your iSheet:
First, enter details for the new iSheet:
- Name - A name is required and identifies the iSheet in the iSheet module
- Description - Optionally add a description to help identify the iSheet
- Status - Select Active or Pending; only admins can see Pending iSheets
- Type - Select a Normal iSheet, or create a metadata iSheet:
Import Data - If you create a Normal iSheet, deselect this option to import only the column names and structure - no data will be imported
If you select a metadata iSheet, then you may not select import data - it only creates the iSheet columns, ready to add records later
- Select column headers - Select the row that contains the name for each column; this is not necessarily the first row
After you have reviewed and selected the structure from the Excel file, click Next.
Define column types
The Columns section allows you to check and revised the column types for each imported Excel column.
Check the name imported from the Excel file; if necessary type to revise the Column name (you are able to do this later in the iSheet module).
Check the detected column type; by default, if columns are assigned Single line text.
Click the drop-down menu next to a column name to change the column type that will be used. Use the check box next to the column name to select and change the column type for multiple columns.
The checkbox at the top of the table selects all columns.
The following column types are supported:
Some column types are automatically assigned during import, please check that they are correctly identified:
- Single line
- Number (for numbers and decimals)
- Hyperlink
Click Next to see a Preview of the iSheet:
Check the contents are as expected. If you need to make corrections to the import settings click Back, otherwise click Import to create the iSheet.
Import process
You can track progress with the message at the top of the screen.
This shows how many records are to be created, with a total of successes and failures.
Records that fail are left blank in the created iSheet. After the import has finished, add records manually or delete as required.
Open the created iSheet
When the import process has finished, the iSheet was created successfully message appears
If the iSheet is not created, please check compatibility as described above.
Open the iSheets module and navigate to the imported iSheet.
You may now configure each column. Open iSheets in the Admin module, and manage columns.
For example: add descriptions, default values, colours for values in a choice column, etc. See Column types in iSheets and Column settings in iSheets for more information.
Note: It is only possible to change a column type in very limited circumstances; for tips on how to change a column type, see Edit and delete columns in iSheets.
Import records from an Excel file
Import via Excel provides an efficient way to:
- Update a large number of rows at once
- Add new data in bulk
- Upload data queried from an internal database or content repository. Such data can be formatted in Excel and imported in bulk into the iSheet
- Allow users who do not have access to the site or iSheet to review and update data. Administrators may provide the downloaded iSheet data to such users who may update the content in Excel, return the file to an Administrator who can import the updates into the iSheet
If a file metadata iSheet is configured for a site, importing iSheet data may be an efficient alternative to adding metadata to files uploaded in bulk rather than doing so one file at a time. Using an Excel file will allow you to quickly enter the appropriate information for many documents in one place, and import the data back into the file metadata iSheet.
Import is only available to admins (system, site and content admins) to protect the iSheet data integrity and prevent data errors. Admins may provide the Excel import file to another user for updates and modifications, but only an Admin may import the file to update the iSheet.
Importing iSheet data
iSheet data may be imported in three steps:
- Export the Import template Excel file from More Actions > Export
- Update the downloaded Excel file by editing and/or adding new rows
- Import the updated Excel file from More Actions > Import
Export the Import template
Navigate to the iSheet you would like to update via the import process. Then, select More actions > Export and select the Import template. Click Export. An import template Excel file called ItemData.xlsx will be saved to your computer.
All items in the current view will be included in the exported Import template, so be sure to select an appropriate view, or run a search to generate a result list of items to be updated. If you would just like to add new entries in the import process, without modifying any existing entries, see Removing rows. All columns in the iSheet, except lookup columns, will be included in the import template, regardless of whether or not they are included in the current view when downloading the file.
Filters configured on the view you choose to use as the import template will not affect your ability to import items that would not be displayed due to the filter configured. For example, if your iSheet has a choice column with 'Yes' or 'No' as option values, and a view with a filter configured to only display items where that choice column selection is 'Yes', you may still use that view to generate the Import template export and import new items with a value of 'No' in that choice column. However, after importing these items you will need to change the view to see those items.
Edit the Import template
Open the Excel file, ItemData.xlsx. You may be presented with a message similar to the following:
Click Enable Editing.
The Excel file will appear similar to this example, with a header row showing all of the column names:
The first column (A) of the Excel file is hidden. That column contains the System Generated ID, a unique identifier for each row. It is recommended that you leave column A hidden, but if you do unhide it, ensure that you do not change the values. Changing the values could cause data to be imported improperly or iSheet items to be overwritten.
Additionally, do not make any changes to the first row of column names, keep the format of the Excel file as is and do not add additional columns.
All columns, except for lookup columns, appear in the import template. Most column types may be modified or added in the import process. Certain column types require additional instructions for importing, while other column types cannot be added through the import process. See the notes and exceptions listed below.
You may insert new rows after the last row shown in the Excel file. (Any rows added to the Import template for a file or folder metadata iSheet will be ignored.)
If column A is unhidden, leave it blank for any new rows added. They will be assigned a unique identifier value on import.
After making additions or updates to the Import template Excel file, save the changes on your computer and proceed to the next step.
Upload the edited Import template
Return to the iSheet you would like to update via the import process and select More actions > Import.
Click Browse to locate the import template Excel file saved and select it. Click Next.
By default no file size limit is applied, however an admin can contact HighQ support to set a size limit for imported files. In the example above, the limit is set at 2 MB.
An import summary window will appear with information about the rows of data you will be importing:
The Summary tab lists the total number of records (rows) to be imported, and how many of those rows are new records and how many are modifications of existing records.
If any records are locked, they will be indicated in the summary. See below for more information about handling locked records.
If there are any warnings, you may review them in the Warning tab and decide whether or not to proceed with the import as is, or click Cancel and address the warnings in the Excel file first. Any errors in the import will prevent you from proceeding, so you must be corrected those errors in the Excel file before attempting the import again.
You may click the Warning tab to find out which cells have warnings and what they are. You may click the warning message to see the relevant row highlighted. You may also click View a preview to see the data to be imported. The preview will also highlight in yellow any cells with warnings:
If you would like to proceed with the import, close the preview window (if open) and click Import.
Importing column types: notes and exceptions
Single line text
If the text entered into a single line of text column exceeds the character limit defined in the column configuration (up to 255), the following error message will appear. You must cancel the import and limit the text entered into that particular cell accordingly, or increase the column character limit, or change the column type to multiple line text before proceeding.
Multiple line text (with Allow rich HTML text enabled)
These fields will show the HTML markup, which may be edited. For new rows, plain text or HTML markup may be entered. Ensure any HTML markup entered is valid. If any errors are detected, the following error message will appear upon import:
Clicking Invalid HTML detected will identify the cell with the invalid HTML markup. You must cancel the import and identify and correct the markup error before attempting the import again. Typical errors that trigger this warning are opening tags without closing tags (
without a following
) or standalone tags without a slash (
instead of
), or any markup that does not constitute well-formed XML.
Choice
All choice columns are automatically configured as choice dropdowns in the Excel import template. When modifying existing rows or adding new rows, you may click the choice column cell, select the drop-down arrow and select an appropriate value:
If the choice column is configured to allow multiple selections (checkboxes), you can enter multiple selections for import by separating them by a single line break (ALT+ENTER) in the cell.
If the choice column is configured to Include "Other" option, you can enter a value other than those provided in the drop-down. Otherwise, the cell must include a choice value that matches one of the possible configured options exactly. In single selection choice columns, Excel validation typically prevents you from adding values that do not match the configured choice options. However, if multiple selections are allowed, it is possible to enter values and bypass such validation, in which case the following error message will appear upon import:
Number
If a number value for import is outside any minimum or maximum value restrictions set in the column configuration, an error message will appear:
If a specific number of decimals are configured, the value will be rounded to match the decimal places in Excel prior to import.
Date and time
Date and time values must be properly formatted in order to import without errors. Date formats must match the format designated in the column configuration, and columns configured to include time must have a time entered in the Excel cell. Error messages like the ones below will appear if such formatting inconsistencies are in the Excel import file:
User lookup
To import values into user lookup fields, enter the email address of the user as entered into Collaborate. The email address must be entered regardless of the display setting in the column. Users must be within the scope of users as configured in the column, such as All Site users, in order to be imported without error.
If the user lookup column is configured to allow multiple users, you may enter multiple user email addresses separated by a single line break (ALT+ENTER).
Hyperlink
Hyperlinks may be imported in two ways. You can enter just the URL in the cell and the iSheet display text will be the same as the URL. Alternatively, you may use the Excel insert hyperlink feature in which case the display text will be the same as the text used to create the link in Excel.
Image
An Image as URL column may be imported by entering the image URL into the cell. An Image as attachment column cannot be imported.
Attachment
Attachments cannot be imported into iSheets via the Excel import process. Any attachment column cells should be left blank for new rows; any text entered in a cell in an Attachment column will be ignored upon import.
Lookup
Lookup columns cannot be imported into iSheets via the Excel import process and are not included in the import template.
File link, Folder link, iSheet link, Join, Calculation and Auto-increment
These column types cannot be updated or imported via the Excel import process. Document links, folder links, joins and inject, calculation and auto-increment columns should be left blank if new rows are added during the update process, as any text entered will be ignored upon import.
System generated fields
Created by, Created date, Modified by and Modified date cannot be entered during the import process.
Modified date may be entered if Enable modified date override is enabled on the iSheet.
The site admin who imports the Excel file will automatically be assigned in Modified by, and the date and time of the import will be assigned as the Modified date. The same applies to Created by and Created date for new rows added via the Excel import process.
Mandatory Columns
You may import new rows without entering data in columns configured as mandatory. These blank mandatory cells will be listed as Warnings upon uploading the import template Excel file, as in the below example, but you may proceed with the import.
Conditional Columns
You may import values into conditional columns, including columns that would not appear due to the column conditions configured. For example, if a column is configured to display when another choice column value is 'Yes', we could enter and import a value into that column regardless of what was selected in the choice column. However, once imported, that value will appear blank in the column grid view if the condition to display the column is not met. And when viewing and editing the item, that column would not appear. If the choice column were edited to select 'Yes', the value entered upon import would appear, and the user would be able to change the value if needed.
File and folder metadata iSheets
New rows cannot be added to file or folder metadata iSheets via the Excel import process. Files and folders must be added first in the Files module, and then modified via the import process.
Removing rows
You may not delete any rows via the Excel import process. Deletions must be made from the browser in the web interface by selecting one or more items and clicking Actions > Delete. Any row deletions in the Excel file will be ignored on import.
If you need to remove rows in the import template Excel file because they need not be modified, prior to importing the data fully delete those rows by selecting the entire row in Excel from the row ID on the left, right-click within the row and select Delete. Rows must be deleted in their entirety to remove the associated System Generated ID and to avoid inadvertently overwriting existing data.
Additional importing options
Email alert notifications
If an iSheet has Enable sheet alerter configured, you may change the email notification settings triggered by the import by clicking the bell icon and opting to either Send now to every subscribed user or Suppress email alert:
Enable modified date override
If an iSheet has Enable modified date override configured, the following options appear when exporting the import template:
- Current time - The current date and time are inserted as the modified date. This is the default behavior of the modified date when adding or updating iSheet items via the import process.
- No changes - The modified date and time will not change for items that are modified. Any new items added in the import process will have the current date and time applied as the modified date. Note that although the modified date does not change for items when they are updated via an import using this method, the audit and version history records will display the import modified date and time for these iSheet items.
- Custom time - A Site admin can insert their own date and time into modified date fields for each item. This may be useful if importing metadata from another source where date and time stamps need to be maintained.
Enable record locking
If an iSheet has Enable record locking configured, a message similar to the following may appear in the Import summary window if items in the import are locked for editing by a user:
Items that are locked for editing by a user will be indicated as Warnings. Select the Warnings tab for more information:
Each locked record is listed, along with the row location of the record. You may click each Record locked link to view that particular record highlighted in the import preview:
If you choose to proceed with the import, you may overwrite all locked records by checking Overwrite locked records on import:
Or you may leave Overwrite locked records on import unchecked, and those items will remain locked, will be ignored upon import and not modified.
