How does importing people work?
Our import feature allows you to bulk add or update your people by uploading a Microsoft Excel spreadsheet that contains a single sheet of person data structured into specific columns.
How do I bulk add new people?
Go to Contacts > People > People.
Click the Import button in the top right.
Tick Adding new people under Which type of import you are doing?
A help box will appear telling you how to structure your spreadsheet columns correctly, see How do I structure my spreadsheet columns?
Once you've structured your spreadsheet columns, add the person data, see What data do I provide when adding people?
Once you've added your data, click the Choose File button to open a File Explorer window and select the spreadsheet from your computer.
Click Import.
If there have been any issues with your data, an error message will be displayed with the row number and issue. Make the relevant changes to your spreadsheet then repeat the process above from step 6.
How do I bulk update people?
Go to Contacts > People > People.
Click the Import button in the top right.
Tick Updating existing people under Which type of import you are doing?
A help box will appear telling you how to structure your spreadsheet columns correctly, see How do I structure my spreadsheet columns?
Once you've structured your spreadsheet columns, add the person data, see What data do I provide when updating people?
Once you've added your data, click the Choose File button to open a File Explorer window and select the spreadsheet from your computer.
Click the Import button.
If there have been any issues with your data, an error message will be displayed with the row number and issue. Make the relevant changes to your spreadsheet and then repeat the process above from step 6.
How do I structure my spreadsheet columns?
The spreadsheet must be of the .xlsx file type and only contain one sheet, any additional sheets will be ignored.
The first row of the spreadsheet must list the column headings in separate columns, see What are the columns for adding people? and What are the columns for updating people? for the supported columns. All column headings should be in uppercase.
An easy option for structuring a spreadsheet is to first export your people, this will download a spreadsheet that is the correct file type (.xlsx) and already has most of the columns that are used for adding or updating. You can then use the downloaded spreadsheet as a template.
You can add additional columns to the spreadsheet by adding a new column and entering a supported column heading into the first row of the new column. If you wish to remove any columns, simply delete them from the spreadsheet.
What are the columns for adding people?
When adding people, the FIRST_NAME and LAST_NAME columns are required, all other columns are optional. The supported columns are as follows:
FIRST_NAME
LAST_NAME
JOB_TITLE
EMAIL_ADDRESS
MOBILE_PHONE_NUMBER
HOME_PHONE_NUMBER
WORK_PHONE_NUMBER
ORGANISATION_NAME
USERNAME
GENDER
BIRTH_DATE
HOME_ADDRESS_LINE_1
HOME_ADDRESS_LINE_2
HOME_ADDRESS_TOWN_CITY
HOME_ADDRESS_STATE_PROVINCE_REGION_COUNTY
HOME_ADDRESS_ZIP_POSTCODE
HOME_ADDRESS_COUNTRY_TITLE
WORK_ADDRESS_LINE_1
WORK_ADDRESS_LINE_2
WORK_ADDRESS_TOWN_CITY
WORK_ADDRESS_STATE_PROVINCE_REGION_COUNTY
WORK_ADDRESS_ZIP_POSTCODE
WORK_ADDRESS_COUNTRY_TITLE
MAILING_ADDRESS_LINE_1
MAILING_ADDRESS_LINE_2
MAILING_ADDRESS_TOWN_CITY
MAILING_ADDRESS_STATE_PROVINCE_REGION_COUNTY
MAILING_ADDRESS_ZIP_POSTCODE
MAILING_ADDRESS_COUNTRY_TITLE
BILLING_ADDRESS_LINE_1
BILLING_ADDRESS_LINE_2
BILLING_ADDRESS_TOWN_CITY
BILLING_ADDRESS_STATE_PROVINCE_REGION_COUNTY
BILLING_ADDRESS_ZIP_POSTCODE
BILLING_ADDRESS_COUNTRY_TITLE
TAG_NAMES
EMAIL_LIST_NAMES
STATUS
ALLOW_ON_ACCOUNT
INHERIT_FROM_ORGANISATION
PRICING_TIER_NAME
CREDIT_LIMIT
BLOCK_ORDERS_IF_CREDIT_LIMIT_EXCEEDED
INTERNAL_NOTE
XERO_CONTACT_NAME
CUSTOM_FIELD_[NAME]*
MEMBERSHIP_TYPE_NAME**
MEMBERSHIP_JOINING_DATE**
MEMBERSHIP_EXPIRY_DATE**
MEMBERSHIP_AUTO_RENEW**
MEMBERSHIP_CURRENCY_CODE**
MEMBERSHIP_PAYMENT_METHOD**
*The NAME component of the CUSTOM_FIELD heading should be an existing person custom field name.
** If any of the MEMBERSHIP columns are present then all of the MEMBERSHIP columns must also be included apart from MEMBERSHIP_EXPIRY_DATE which is optional.
What data do I provide when adding people?
When adding people using the import feature, it is important that the data you provide meets certain guidelines to avoid any issues. The guidelines are as follows:
Each row of your spreadsheet is read as a single person.
FIRST_NAME - cannot exceed 50 characters.
LAST_NAME - cannot exceed 50 characters.
JOB_TITLE - cannot exceed 100 characters.
EMAIL_ADDRESS must be a valid email address and cannot exceed 320 characters.
MOBILE_PHONE_NUMBER - cannot exceed 20 characters.
HOME_PHONE_NUMBER - cannot exceed 20 characters.
WORK_PHONE_NUMBER - cannot exceed 20 characters.
ORGANISATION_NAME - must reference an existing organisation name.
USERNAME - must be unique and cannot exceed 320 characters.
GENDER - must be either male or female.
BIRTH_DATE - must use the following format: dd/mm/yyyy (e.g. 23/05/2022).
HOME_ADDRESS_LINE_1 - cannot exceed 100 characters.
HOME_ADDRESS_LINE_2 - cannot exceed 100 characters.
HOME_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
HOME_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
HOME_ADDRESS_ZIP_POSTCODE - cannot exceed 10 characters.
HOME_ADDRESS_COUNTRY_TITLE - must reference an existing country.
WORK_ADDRESS_LINE_1 - cannot exceed 100 characters.
WORK_ADDRESS_LINE_2 - cannot exceed 100 characters.
WORK_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
WORK_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
WORK_ADDRESS_ZIP_POSTCODE - cannot exceed 10 characters.
WORK_ADDRESS_COUNTRY_TITLE - must reference an existing country.
MAILING_ADDRESS_LINE_1 - cannot exceed 100 characters.
MAILING_ADDRESS_LINE_2 - cannot exceed 100 characters.
MAILING_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
MAILING_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
MAILING_ADDRESS_ZIP_POSTCODE cannot exceed 10 characters.
MAILING_ADDRESS_COUNTRY_TITLE - must reference an existing country.
BILLING_ADDRESS_LINE_1 - cannot exceed 100 characters.
BILLING_ADDRESS_LINE_2 - cannot exceed 100 characters.
BILLING_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
BILLING_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
BILLING_ADDRESS_ZIP_POSTCODE - cannot exceed 10 characters.
BILLING_ADDRESS_COUNTRY_TITLE - must reference an existing country.
TAG_NAMES - must reference existing person tag names, multiple names must be separated by commas.
EMAIL_LIST_NAMES - must reference existing person email list names, multiple names must be separated by commas.
STATUS - must be either active or disabled or archived.
ALLOW_ON_ACCOUNT - must be either yes or no.
INHERIT_FROM_ORGANISATION - must be either yes or no.
PRICING_TIER_NAME - must reference a sellable pricing tier name.
CREDIT_LIMIT - must be a positive number (i.e. zero or greater)
BLOCK_ORDERS_IF_CREDIT_LIMIT_EXCEEDED - must be either yes or no.
XERO_CONTACT_NAME - Xero must be connected and must reference an existing Xero contact name.
CUSTOM_FIELD_[NAME] - If the custom field uses the Yes/no value type then only yes or no are accepted. If the custom field uses the Pre-defined option value type then only names that reference an existing option are accepted, if the custom field uses the URL value type then only valid secure URLs are accepted.
If MEMBERSHIP columns exist:
MEMBERSHIP_TYPE_NAME - must be provided and must reference a existing person membership type .
MEMBERSHIP_JOINING_DATE - must be provided and must use the following format: dd mmm yyyy (e.g. 23/05/2022).
MEMBERSHIP_EXPIRY_DATE - must fall after the MEMBERSHIP_JOINING_DATE and use the following format: dd mmm yyyy (e.g. 23/05/2022) .
MEMBERSHIP_AUTO_RENEW - must be provided and must be either yes or no.
MEMBERSHIP_CURRENCY_CODE - must be provided and reference an existing currency code (i.e. USD, NZD, etc).
MEMBERSHIP_PAYMENT_METHOD - must be provided and must be either credit card, bank transfer or on account.
What are the columns for updating people?
When updating people, the PERSON_ID column is required, all other columns are optional. The supported columns are as follows:
PERSON_ID
FIRST_NAME
LAST_NAME
JOB_TITLE
EMAIL_ADDRESS
MOBILE_PHONE_NUMBER
HOME_PHONE_NUMBER
WORK_PHONE_NUMBER
ORGANISATION_NAME
GENDER
BIRTH_DATE
HOME_ADDRESS_LINE_1
HOME_ADDRESS_LINE_2
HOME_ADDRESS_TOWN_CITY
HOME_ADDRESS_STATE_PROVINCE_REGION_COUNTY
HOME_ADDRESS_ZIP_POSTCODE
HOME_ADDRESS_COUNTRY_TITLE
WORK_ADDRESS_LINE_1
WORK_ADDRESS_LINE_2
WORK_ADDRESS_TOWN_CITY
WORK_ADDRESS_STATE_PROVINCE_REGION_COUNTY
WORK_ADDRESS_ZIP_POSTCODE
WORK_ADDRESS_COUNTRY_TITLE
MAILING_ADDRESS_LINE_1
MAILING_ADDRESS_LINE_2
MAILING_ADDRESS_TOWN_CITY
MAILING_ADDRESS_STATE_PROVINCE_REGION_COUNTY
MAILING_ADDRESS_ZIP_POSTCODE
MAILING_ADDRESS_COUNTRY_TITLE
BILLING_ADDRESS_LINE_1
BILLING_ADDRESS_LINE_2
BILLING_ADDRESS_TOWN_CITY
BILLING_ADDRESS_STATE_PROVINCE_REGION_COUNTY
BILLING_ADDRESS_ZIP_POSTCODE
BILLING_ADDRESS_COUNTRY_TITLE
TAG_NAMES
EMAIL_LIST_NAMES
STATUS
ALLOW_ON_ACCOUNT
INHERIT_FROM_ORGANISATION
PRICING_TIER_NAME
CREDIT_LIMIT
BLOCK_ORDERS_IF_CREDIT_LIMIT_EXCEEDED
INTERNAL_NOTE
XERO_CONTACT_NAME
CUSTOM_FIELD_[NAME]*
*The NAME component of the CUSTOM_FIELD heading should be an existing person custom field name.
What data do I provide when updating people?
When updating people using the import feature, it is important that the data you provide meets certain guidelines to avoid any issues. The guidelines are as follows:
Each row of your spreadsheet is read as a single person.
PERSON_ID - must be provided and must reference an existing person (you can get these by doing a people export).
FIRST_NAME - cannot exceed 50 characters.
LAST_NAME - cannot exceed 50 characters.
JOB_TITLE - cannot exceed 100 characters.
EMAIL_ADDRESS - must be a valid email address and cannot exceed 320 characters.
MOBILE_PHONE_NUMBER - cannot exceed 20 characters.
HOME_PHONE_NUMBER - cannot exceed 20 characters.
WORK_PHONE_NUMBER - cannot exceed 20 characters.
ORGANISATION_NAME - must reference an existing organisation name.
USERNAME - must be unique and cannot exceed 320 characters
GENDER - must be either male or female
BIRTH_DATE - must use the following format: dd/mm/yyyy (e.g. 23/05/2022).
HOME_ADDRESS_LINE_1 - cannot exceed 100 characters.
HOME_ADDRESS_LINE_2 - cannot exceed 100 characters.
HOME_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
HOME_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
HOME_ADDRESS_ZIP_POSTCODE - cannot exceed 10 characters.
HOME_ADDRESS_COUNTRY_TITLE - must reference an existing country.
WORK_ADDRESS_LINE_1 - cannot exceed 100 characters.
WORK_ADDRESS_LINE_2 - cannot exceed 100 characters.
WORK_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
WORK_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
WORK_ADDRESS_ZIP_POSTCODE - cannot exceed 10 characters.
WORK_ADDRESS_COUNTRY_TITLE - must reference an existing country.
MAILING_ADDRESS_LINE_1 - cannot exceed 100 characters.
MAILING_ADDRESS_LINE_2 - cannot exceed 100 characters.
MAILING_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
MAILING_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
MAILING_ADDRESS_ZIP_POSTCODE cannot exceed 10 characters.
MAILING_ADDRESS_COUNTRY_TITLE - must reference an existing country.
BILLING_ADDRESS_LINE_1 - cannot exceed 100 characters.
BILLING_ADDRESS_LINE_2 - cannot exceed 100 characters.
BILLING_ADDRESS_TOWN_CITY - cannot exceed 50 characters.
BILLING_ADDRESS_STATE_PROVINCE_REGION_COUNTY - cannot exceed 50 characters.
BILLING_ADDRESS_ZIP_POSTCODE - cannot exceed 10 characters.
BILLING_ADDRESS_COUNTRY_TITLE - must reference an existing country.
TAG_NAMES - must reference existing person tag names, multiple names must be separated by commas.
EMAIL_LIST_NAMES - must reference existing person email list names, multiple names must be separated by commas.
STATUS - must be either active or disabled or archived.*
ALLOW_ON_ACCOUNT - must be either yes or no.*
INHERIT_FROM_ORGANISATION - must be either yes or no.*
PRICING_TIER_NAME - must reference a sellable pricing tier name.*
CREDIT_LIMIT - must be a positive number (i.e. zero or greater)
BLOCK_ORDERS_IF_CREDIT_LIMIT_EXCEEDED - must be either yes or no.*
XERO_CONTACT_NAME - Xero must be connected and must reference an existing Xero contact name.
CUSTOM_FIELD_[NAME] - If the custom field uses the Yes/no value type then only yes or no are accepted. If the custom field uses the Pre-defined option value type then only names that reference an existing option are accepted, if the custom field uses the URL value type then only valid secure URLs are accepted.
If a supported column is provided without any person data, then that value will be cleared from the relevant people, e.g. if a FIRST_NAME column was provided without any data then the first name of the relevant person would be cleared.
*Some person values cannot be empty (status, pricing tier, etc) so if those supported columns have been provided, then they must contain data.
Troubleshooting
Despite our best efforts, there are still things that can go wrong while importing people, here are some issues that have previously been encountered:
Some numeric values are incorrect and contain an 'E+' - This is a behaviour of Microsoft Excel, when you enter a value above a certain length into a cell that is using the number data format, Excel automatically converts that number into a scientific notation with a decimal place after the first digit and an 'E+' near the end To fix the problem do the following:
Open the spreadsheet in Excel and open the Data tab at the top of the page
2. Select the entire column that you are having an issue with by clicking the letter above the column heading (the whole column should be highlighted) then click the Text to Columns button.
3. This will open the Text to Columns wizard, click the Next button to skip through steps one and two. On the third step, select the Text checkbox and click the Finish button.
4. You should then see a small green triangle in all of the cells in the selected column indicating that they are now set to the text data format and can be imported correctly.