CRMTray includes tools for importing data from Excel spreadsheets.
NB: View the Leads user guide for information on importing Leads for CRMTray users.
To import data, System Administrators click the Settings button on the menu bar. Then click Import Data, then CRM Data.
Overview
- Data is imported from Excel spreadsheets with a .xlsx file extension.
- The first row of the import spreadsheet is treated as column names and is not imported.
- The order and name of the columns in the upload spreadsheet are flexible as you can map the spreadsheet columns to the available destination fields when setting up the import.
- Only the data in the first sheet of the spreadsheet file may be used for data import.
- A maximum of 1,000 spreadsheet rows may be imported in a single import event.
Insert and Update
All data import Types have the ‘Insert’ option available. Some data import Types have both the ‘Insert’ and ‘Update’ options.
Insert Option: When using the Insert option, new records are added to your CRMTray data. Existing records are not changed. An existing record is characterized by its key fields. For example, the key field for a Company record is the Company Name.
Update Option: Existing records will be updated; new records will not be created. The update option means that you can change the data in your import spreadsheet, then reload the data without creating duplicate records.
The update option will be hidden for some import ‘Type’ selections.
Importing process
From the Settings menu (available to System Administrators):
- Select the Type of data import.
- Choose the Excel spreadsheet file.
- Select Insert or Update if available.
- Click Next.
The data is loaded into table view for checking. At this stage no checks have been performed by PeopleTray. The data is displayed in the order of the spreadsheet columns. The names of the columns in the grid are the names of the columns in your spreadsheet.
Click the button labelled ‘Click the button to apply mappings’, to map the System fields (destination fields) with the Spreadsheet fields. You must provide a map to the Primary field (See Data Fields section below). However the other fields can be left blank if you do not wish to load data into those fields. When using the Update option, fields that are not mapped, will not change.
When you have mapped the required fields, click ‘Apply mapping and import’, to import your data.
This example shows:
- 9 records were submitted from the spreadsheet.
- 8 records were processed.
- 1 record failed.
The rejected record is displayed in the table. The right hand displays the reason the record failed.
Clicking the Excel button on the above left of the table output the failed records to a spreadsheet where the data can be amended, then re-imported if required.
Details for each Import Type
Import Type: Company
Fields: Name, Company Type, Phone, Email, Website, Description, Address 1, Address 2, Suburb, Postcode, State, Country, Latitude, Longitude, Tags.
Name – Limit of 100 characters
Company Type
Must exist in Types table. See Settings > CRMTray Settings > Company Categories > Company Types.
The default options for Company Type for new accounts are: Contractor, Customer, Partner, Supplier
Area – Limit of 100 characters
Must exist in Area table. See Settings > CRMTray Settings > Areas. You must add your Areas before you can import area data for grouping companies.
Phone – Limit of 100 characters
Email – Limit of 100 characters
Website – Limit of 200 characters
Industry – Must exist in Industries table. See Settings > CRMTray Settings > Company Categories > Industries. – Limit of 100 Characters.
Description – Limit of 2000 characters
Address 1 – Limit of 200 characters
Address 2 – Limit of 200 characters
Suburb – Limit of 100 characters
Postcode – Limit of 30 characters
State – Limit of 50 characters
Country – Must be a valid country
Customer status – Options are Customer, Non Customer, Previous Customer
Longitude
Latitude
Tags – Comma separated (E.g. Employees <200, Employees 200>500, Employees >500). When using the Update option for importing company data, new tags are added to the existing tags for each company that already exists.
Import Type: Contacts
Fields: Company, Salutation, First Name, Last Name, Description, Title, Phone, Mobile, Email
Company – The Company name must exist in your CRMTray data. (i.e. Contacts are associated with Companies in CRMTray).
Salutation – Options are: Mr, Mrs, Mr & Mrs, Ms, Miss, Dr
First name – Limit of 50 characters
Last name – Limit of 50 characters
Description – Limit of 2000 characters
Contact Type – Must exist in Contact Types table. See Settings > CRMTray Settings > Contact type. Limit of 100 Characters.
Title – Limit of 50 characters
Phone – Limit of 50 characters
Mobile – Limit of 50 characters
Email – Limit of 50 characters
Import Type: Contact Notes
Fields: Contact first name, Contact last name, Note, Added on, Added by person first name, Added by person last name.
Contact first name/Contact last name – The contact person name must exist in your CRMTray account.
Note – Limit of 2000 characters
Added on – Date of note creation Text format in the form of: 2016-01-06T00:42:47Z
Added by person first name/ Added by person last name – The Added By person name must exist in your CRMTray account as a User. (A User is a person that you have added to your account as a Reader, Editor or Administrator. This is the person that will be denoted as having added the note to the Contact on the Added by date).
Import Type: Company Notes
Fields: Company name, Note, Added on, Added by person first name, Added by person last name.
Company name – The Company name must exist in your CRMTray account.
Note – Limit of 2000 characters
Added on – Date of note creation Text format in the form of: 2016-01-06T00:42:47Z
Added by person first name/ Added by person last name – The Added By person name must exist in your CRMTray account as a User. (A User is a person that you have added to your account as a Reader, Editor or Administrator. This is the person that will be denoted as having added the note to the Contact on the Added by date).
Import Type: Leads
New leads must be unique based on a combination of:
Company name/Contact name
OR;
Company name/Contact email address.
When mapping PeopleTray system fields to the fields in the import spreadsheet during Lead import, selectors are available for fields that require a match with settings lists. In this way, the same values can be assigned to all of the leads during the current import, without adding columns to the import spreadsheet.
Fields: Company name, Status, Notes, Industry, Estimated size, Source, Web Address, Phone Number, Email, Address 1, Address 2, Postcode, Suburb, State, Country, Contact first name, Contact last name, Contact Job Title, Contact Phone, Contact Mobile, Contact email, Owner first name, Owner last name, Tags.
Company name – Must be unique in CRMTray. (Two leads cannot have the same company name).
Status – Must be a value in the list: Unknown, Cold, Warm, Hot.
Notes – Limit of 2000 characters.
Industry – Must exist in the Industry settings list.
Estimated size – Number that represents the size of potential sale.
Source – The source of the lead. (E.g. Cold Call, Referral, Social Media). Must exist in the Source settings list.
Web Address – Valid web address. Limit of 200 characters.
Phone Number – Limit of 100 characters.
Email – Limit of 200 characters.
Address 1 –Limit of 200 characters.
Address 2 – Limit of 200 characters.
Postcode – Limit of 50 characters.
Suburb – Limit of 100 characters.
State – Limit of 100 characters.
Country – Select from list.
Contact first name – Limit of 100 characters, or select using selector.
Contact last name – Limit of 100 characters.
Contact Job Title – Limit of 100 Characters
Contact Phone – Limit of 100 characters.
Contact Mobile – Limit of 100 characters.
Contact email – Limit of 100 characters.
Owner first name – Limit of 100 characters.
Owner last name – Limit of 100 characters.
Tags – Load a single tag from a tag column in your import spreadsheet, or choose an existing tag using the selector.
Importing LinkedIn connections
To export your LinkedIn connections to an Excel spreadsheet file (.xlsx file extension), ready for importing into PeopleTray:
- Open your LinkedIn Home Page.
- On the menu bar at the top, click My Network > Connections.
- Click the settings (cog button) on the top right.
- Click ‘Export LinkedIn Connections’, Then export to .csv file.
- Open the exported .csv file, then Save As format Excel Workbook file (.xlsx).
This LinkedIn export file (.xlsx) can now be imported to PeopleTray without modification. Use the picklist during mapping to add information such as Lead Status, Lead owner, Source, Country. You can also nominate a single Tag.