Import Data from Excel
After designing the worksheet, you can import the data from Excel.
When importing the data, you can:
- Update existing records
- Associate records in other worksheets
I. How to import data
1. Entrance to import data
In a worksheet, click […] > [Import from Excel]
2. Select the Excel file
You can drag and drop files to the area, or click to select the file and upload.
You can upload files in xls or xlsx format, no more than 10MB, up to 20,000 rows and 200 columns.
You can import fields with multiple options such as mutiple-select, department (mutiple-select), members (mutiple-select), etc. Before importing, make sure that the options are separated by English commas in Excel, like Option1, Option2.
3. Upload the file
4. Set the header
The header are the column titles in Excel, corresponding to the field names. Once selected, the data below the header will be imported into the worksheet (the header line will not be imported). The header cannot be empty, or the import will fail.
5. Fields mapping
The left side are the column titles in Excel, and the right side are the fields in the worksheet. You should match the columns with the fields (if the column title is the same as the field name, they will be matched automatically; if not, you can operate manually).
If the columns and fields are not matched, and click [Import] directly, the data in Excel will be imported as new records.
If the column title remains as [Please select], the data will not be imported to its corresponding field.
6. When importing data
6.1 Identify duplicate records
If checked, before importing data, it will check whether this record already exists in the worksheet based on the [matching field] you select. If it already exists, you need to choose whether to update the existing record, or skip it.
Skip: According to the [matching field] you set, if the record already exists in the worksheet, skip it; if not, import it as a new record.
Override: It is to overwrite the data in the worksheet with data from Excel. If the record already exists in the worksheet, update it; if not, import it as a new record.
Update only (Do not create new): When importing, only update the existing records. Even if some data in Excel does not exist in the worksheet, it is not imported as new records.
How to update existing records when importing data
6.2 Skip error data
When importing, if a field is not in the proper format, or if a required field is empty, the field is considered as error data.
If [Skip error data] is checked, and there is error data, the entire row will be skipped and not imported.
If it is unchecked, the fields with error data are imported with empty values and other fields are imported normally.
About error data:
There are the following 8 kinds of errors, you can check some of them when importing.
For example, if you check [Email],
- If the mailbox is not in the correct format, the entire row of data will not be imported.
- If there is other error data, the fields with error data are imported with empty values and other fields are imported normally.
6.3 Import data to trigger the workflow
For workflows that are triggered when adding or updating records, sometimes they only need to be triggered when adding or updating a single record but not triggered when bulk importing data. In this case, you can leave [Import will trigger workflow] unchecked.
If [Import will trigger workflow] is unchecked, for a workflow that is triggered by the date field, the workflow will not be triggered on the day you import the data, and it can be triggered normally the next day.
6.4 Save the configuration for importing
You can save the configuration of the previous four steps as the default configuration the next time you import data.
If [Do not allow users to modify the default configuration] is checked, ordinary members cannot modify the configuration, they can only import based on the saved configuration and will not see the configuration items.
6.5 Allow new option
For the option to be imported, if there is no such a option in the selection field of the worksheet, if [Allow new selection] is checked, the option will be added as a new one.
6.5 Associate records
When importing, you can associate the data with records in other worksheets.
For example, when you import the sheet Contact, there is a field [Company], and if this field is already in the sheet Customer Company, you can associate them.
How to associate records when importing data
7. After importing data
Import successfully
There will be the following prompt and message.
Failed to import
If any data is not imported successfully, there will also be a prompt. You can also download the report in text format.
Importing logs
The number of imported records mentioned in the message is the sum of the number of new records and the number of updated records. You can view details in importing logs.
II. Rules for Importing Data
1. Fields that cannot be imported
Sub-form, Formula, Uppercase, Positioning, Foreign, Rollup, Signature, Barcode, Segment, and Remarks.
None of the above fields can be imported into the data, even if the App Admin.
Fields with the properties of [Hide], [Hide when create] and [Read-only].
For fields with these properties, only App Admin can import the data.
Fields that are not visible when adding records because of role permissions.
For such fields, only App Admin can import the data.
2. When importing data, the default value originally set for the field is not available
You can set the default values in Excel and import.
3. Restrictions on imported files
- No more than 10MB.
- In xls, xlsx, or csv format.
- Up to 20,000 rows, 200 columns.
4. Import data to fields Multiple-select, Department, Members, O-role
You can import multiple options for such fields. Before importing, make sure that the options are separated by English commas in Excel.
In Excel:
In Worksheet:
5. Import data to the field [Members]
Matching fields for the Members field
It includes name, telephone, email, job number, and member ID.
The above four are the system fields of the contacts, which can be used to identify the corresponding account.
If there are members with same names
If the members field is multi-select, import by name, and it will import all members with the same names.
If the members field is single-select, members with the same names will not be imported.
It is suggested to add a suffix to the name to distinguish them, and also to facilitate future collaboration.
Or import the members without duplicate names first, and import the rest separately. When importing, you can choose to import based on the phone number, email, job number, or member ID.
6. Import data to the field [Region/City]
When importing data to a region/city field, in Excel, the province, city and county must be separated by /, such as XX Province/XX City/XX County. If there is no /, it will be matched precisely by name.
For example, if you import “Henan ProvinceZhengzhou City”, there is no /, it will check if there is a province called “Henan Zhengzhou”, so it cannot be imported.
Importing Rules:
First check the configuration of the field, and import if it matches.
Provinces, cities and counties must correspond to each other separately. For example, if it is Henan Province/Qingdao City, the city will not be imported as Qingdao City because Qingdao City belongs to Shandong Province.
It must be written completely, like XXX Province/XX City/XX County. If it is written as Henan/Zhengzhou, it will not be imported, but Henan Province/Zhengzhou City.
For the four municipalities directly under the central government, it is written as XXX City/XX District.
7. Import files/images
How to export/import files and images
8. Format of Excel files
It is recommended to keep only the fields to be imported in Excel.
The first row is as far as possible the column title (field name), and starting with the second row is the data to be imported.
Have questions about this article？ Send us feedback