Add items (records) in iSheets

Import iSheet data from an Excel file

Import records from an Excel file

Create an iSheet from an Excel file

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
You should check the Excel file to confirm the format will import correctly.
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.
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 revise 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 can 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 following column types are supported:
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.
iSheet creation is in progressiSheets item creation in progress (Total: 10, Success: 1, Fail: 0)
This shows how many records are to be created, with a total of successes and failures.
Open the created iSheet
When the import process has finished, the
iSheet was created successfully
 message appears.
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
.

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:
  1. Export the Import template Excel file from
    More Actions
     >
    Export
  2. Update the downloaded Excel file by editing and/or adding new rows
  3. 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:
Protected view: Be careful - files from the internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing.
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 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.)
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
.
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 correct 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. 
1 Error. The data cannot be imported. Review the details below. View a preview.
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:
1 Error. The data cannot be imported. Review the details below. View a preview.
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, standalone tags without a slash, 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:
1 Error. The data cannot be imported. Review the details below. View a preview.
Number
If a number value for import is outside any minimum or maximum value restrictions set in the column configuration, an error message displays:
1 Error. The data cannot be imported. Review the details below. View a preview.
If a specific number of decimals is configured, the value is rounded to match the decimal places in Excel before import.
Date and time
Date and time values must be properly formatted 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:
3 Errors. The data cannot be imported. Review the details below. View a preview.
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, 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.
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.
12 warnings. Do you want to proceed with the import? View a preview.
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 can 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, before 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 behaviour 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 to 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:
2 warnings. Do you want to proceed with the import? View a preview.
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.

This article applies to:

  • Product: HighQ
  • Subject: iSheets, Setup

Related content