Contact & Company Import Tool
The Contact & Company Import Tool (accessed via the 'Import Contacts & Companies' menu option within the Eploy Core System) can be used to import company structure, contact records and associated users into Eploy. It can also be used to update existing records. If you plan to use the API to synchronize contact and company data, the tool can also be used to check the format of your data and diagnose any issues that could result in errors.
Import Formats
The import tool supports two file formats, CSV and XML. Refer to the ‘Sample Data’ section for examples.
Import Types
The import tool now has three modes for importing data. These are Eploy ID, External ID and Standard.
Eploy ID
Requires that the records being imported have their Eploy ID specified, I.e. CompanyID for Companies and ContactID for Contacts. Because of this constraint, no new records can be imported using this method, we only allow the updating of existing records.
External ID
Requires that the records being imported have their External ID specified, I.e. ImportID for Companies and Contacts. Existing records will be matched on that value and records that do not match will be inserted.
Standard
This import is the one that existed in version 38. It works by matching based on Company Name or other Matching Options. This can be used to create companies and contacts and even users. This can also be used to update External IDs.
Required / Allowed Fields
For each type of Import, Eploy ID, External ID and Standard there are certain fields that are required and others that are not allowed altogether.
Eploy ID
- Required
- CompanyID / ContactID - Main RecordID
External ID
- Required
- ImportID
- Not Allowed
- CompanyID / ContactID - Main RecordID
Standard
- Required
- CompanyName
- Not Allowed
- CompanyID / ContactID - Main RecordID
If you include the ImportID field on an Eploy ID or Standard Import then that column will be updated as it is not used for matching existing records in those import types.
Including the CompanyName field in any Import other than Standard will also result in the Company Name being updated.
We have 3 fields for ParentIDs, ParentCompanyID, ParentImportID and ParentCompanyName. Only one of these can be passed for each Companies and Contacts. We will give a validation error on the first page, where we tell users they are using the wrong fields for the type of import chosen.
Validation Checks
Standard
There is no data validation so this will just validate columns on first page.
Eploy ID
If Company ID column exists then ALL of them must be in Eploy.
If Contact ID column exists, then ALL of them must be in Eploy.
If there is a Parent Company ID or Parent Contact ID that do not exist in Eploy, these give errors as well.
External ID
Company Import ID's must be populated for each row and must be unique for each company. It can appear multiple times against the different Contacts, but all the Company Fields must be the same.
If there is a Parent External Company ID that do not exist in Eploy these will only get set if the parent company is included in the import.
N.B. Some of these may still not get set as the External ID passed is not even in the DB or Import Sheet. These will just not get set in the DB.
Contact Import ID's are populated for each row and must be unique for each contact. It can appear multiple times against the different Users (As a contact can have multiple Users of different types), but all the Contact Fields must be the same.
If there is a Parent External Contact ID that do not exist in Eploy these will only get set if the parent company is included in the import.
N.B. Some of these may still not get set as the External ID passed is not even in the DB or Import Sheet. These will just not get set in the DB.
All
User Field Checks
If we are going to create any users for any of these contacts, we need the minimum number of fields:
- UserDisplayName
- Username
- UserTypeID
It is also recommended that you include Usr_Copy_Usr_ID to copy the permissions granted from another existing user on Eploy. This is not a required field, but without specifying it the user record will have no permissions and the user will be unable to log in until a system administrator manually allocates permissions to them.
User Business Logic Checks
If we are gong to insert or update any users from these contacts, we do not allow the creating of users with duplicate Usernames.
Also UserEmails must be unique for core system users. We will not set this for HM users. If there is data in the UserEmail column and the UserType is not 1 (Core System User) then we will not set this field.
Passwords
Passwords cannot be set during the import, forgotten password links can be sent out for new Users.
Sample Data
Below are sample import files, designed to show the columns allowed and the structure of the data source.
CSV
ExternalID Sample.csvXML
Drop Down Lists Import Tool
The Drop Down Lists Import Tool (accessed via the 'Import Drop Down Lists' menu option within the Eploy Core System) can be used to import drop down list entries, which may be used for various fields within the Eploy system. If you plan to use the API to synchronize drop down list data, the tool can also be used to check the format of your data and diagnose any issues that could result in errors.
For drop down lists that support hierachy (e.g. Locations), you can specify the immediate parent of each entry to build/update the hierarchy of the dropdown via the import.
For drop down lists that support archiving, you can specify whether they are active or not. Not specifying an active flag for new records will assume they are active.
Sample Data
Below are sample import files, designed to show the columns allowed and the structure of the data source.
Import Formats
The import tool supports two file formats, CSV and XML. Refer to the ‘Sample Data’ section for examples.
Import Types
The import tool now has three modes for importing data. These are Eploy ID, External ID and Standard.
Eploy ID
Requires that the records being imported have their Eploy ID specified. Because of this constraint, no new records can be imported using this method, we only allow the updating of existing records.
External ID
Requires that the records being imported have their External ID specified.s. Existing records will be matched on that value and records that do not match will be inserted.
Standard
Neither the Eploy ID or External ID are required. Existing records will be matched on Description and records that do not match will be inserted.
CSV
XML
API
All types of Import that can be done in the core system using the interface can also be performed using the API, the API is to be used with a SOAP or REST envelope.
Location
All API methods are located under https://{eploycoresystemdomain}/API/Import
{eploysystemdomain} will be the designated domain where your core Eploy system is hosted, e.g. https://abctrading.eploy.net/API
- Company and Contact: https://{eploycoresystemdomain}/API/Import/CompanyContact
- Drop Down: https://{eploycoresystemdomain}/API/Import/DropDownList
Authorisation
We have two methods for authorising API requests, Basic and OAuth.
Basic
When calling import methods using Basic a header needs to be set called 'Authorization'.
The Authorization header needs be in the following format:
Authorization: Basic <client_id:client_secret>
The client_id:client_secret need to be base64 encoded.
OAuth
To use OAuth, call the GetOAuthToken method. This takes no parameters in the request body.
When calling the GetOAuthToken a header needs to be set called 'Authorization'.
The Authorization header needs be in the following format:
Authorization: Basic <client_id:client_secret>
The client_id:client_secret need to be base64 encoded.
Example Header Creation
Client ID: eploy
Client Secret: oabLkRauQkbdHYmZfaYW
Now combine the client id and secret:
eploy:oabLkRauQkbdHYmZfaYW
Now base64 encode this:
ZXBsb3k6b2FiTGtSYXVRa2JkSFltWmZhWVc=
Now build the header:
Authorization: Basic ZXBsb3k6b2FiTGtSYXVRa2JkSFltWmZhWVc=
Example OAuth Token Request
POST /WebServices/API.asmx/GetOAuthToken HTTP/1.1
Host: abctrading.eploy.net
Content-Type: application/x-www-form-urlencoded
Content-Length: length
Authorization: Basic ZXBsb3k6b2FiTGtSYXVRa2JkSFltWmZhWVc=
Once a token has been created and returned in the response, this can then be used to perform API requests for Imports.
To use the token for import requests an Authorization header needs to be set:
Authorization: Bearer <token>
Expiry
Tokens are valid only for 5 minutes.
Company Contact
The import methods are broken down into Standard, EployID and ExternalID imports, similar to the Import user interface. Thus there are 3 API methods for importing Companies and Contacts:
- Standard
- ExternalID
- EployID
|
Parameter
|
Values
|
Comment
|
|---|---|---|
| importData |
CSV/XML data Filename |
Data needs to be passed as a string, not bytes. This is the filename of the data file that was uploaded via FTP. |
| uploadMethod |
Direct FtpUpload |
Direct is for sending a string of data. Is for when a file has been uploaded and a filename is passed in the importData parameter. |
| ignoreMissingDropDownOptions | true or false | True will allow the tool to ignore missing Drop Down values such as Location, Industry etc. |
| updateMatchedRecords | true or false | True will update records in Eploy that have matched data being imported. |
When using the Standard import method there are two extra parameters:
|
Parameter
|
Values
|
Comment
|
|---|---|---|
| matchingCriteria_Comp |
CompName CompName_Address |
This allows the company data to be matched on the Company Name Matches the company data on Company Name and then attempts to match on address criteria in this order:
|
| matchingCriteria_Cont |
ContFirstName_ContSurname_CompName ContFirstName_ContSurname_OR_ContEmail__CompName |
This matches the contact data on the Contact Firstname and Surname. This matches the contact data on the Contact Firstname and Surname or Email Address. N.B. All Contact matching criteria always also matches on Company Name. |
Drop Down
The import methods are broken down into Standard, EployID and ExternalID imports, similar to the Import user interface. Thus there are 3 API methods for importing Drop Downs:
- Standard
- ExternalID
- EployID
All the methods take these parameters as a minimum:
|
Parameter
|
Values
|
Comment
|
|---|---|---|
| importData |
CSV/XML data Filename |
Data needs to be passed as a string, not bytes. This is the filename of the data file that was uploaded via FTP. |
| uploadMethod |
Direct FtpUpload |
Direct is for sending a string of data. Is for when a file has been uploaded and a filename is passed in the importData parameter. |
| dropDownType |
Departments |
This is the type of drop down the data is to be imported against. Custom drop downs can also be imported. The value for these can be found in the Drop Down Import Interface under the Drop Down Type field. |
| ignoreMissingDropDownOptions | true or false | True will allow the tool to ignore missing Drop Down values such as Location, Industry etc. |
| updateMatchedRecords | true or false | True will update records in Eploy that have matched data being imported. |
API Examples
REST Examples
Company Contact
Request Headers:
POST /API/Import/CompanyContact HTTP/1.1
Host: abctrading.eploy.net
Content-Type: application/x-www-form-urlencoded; charset=utf-8
Content-Length: length
Authorization: Basic aWtma2RKc0pwa1FCbnJleE5Kc0U6ZUV0bnpjS3RtbUJQaEJSRk1Ud1k=
Request Body:
importData=<Import><ImportRow><Comp_External_ID>EXT-15</Comp_External_ID><Cont_External_ID>EXT-1</Cont_External_ID><Cont_Addr1>146 Deramore Avenue 1</Cont_Addr1><Cont_Addr2>chadwell heath</Cont_Addr2><Cont_Addr3></Cont_Addr3><Cont_Branch></Cont_Branch><Cont_Comments></Cont_Comments><Cont_Country>UK</Cont_Country><Cont_Country_ID>186</Cont_Country_ID><Cont_Country_External_ID>UK</Cont_Country_External_ID><Cont_County>essex</Cont_County><Cont_Decision_Maker>1</Cont_Decision_Maker><Cont_DD_No></Cont_DD_No><Cont_Email>resourcing.manager@itssystems.co.uk</Cont_Email><Cont_Fax>0800 0734243</Cont_Fax><Cont_First_Name>Business</Cont_First_Name><Cont_Merge_Addr>0</Cont_Merge_Addr><Cont_Mob>0800 0734243</Cont_Mob><Cont_Pers_Email></Cont_Pers_Email><Cont_Pers_Mob></Cont_Pers_Mob><Cont_Pers_Tel></Cont_Pers_Tel><Cont_Position></Cont_Position><Cont_PostCode>rm6 4ta</Cont_PostCode><Cont_Ref></Cont_Ref><Cont_Salutation></Cont_Salutation><Cont_Surname>Partner</Cont_Surname><Cont_Tel>0800 0734243</Cont_Tel><Cont_Title>Mr</Cont_Title><Cont_Title_ID>1</Cont_Title_ID><Cont_Title_External_ID>MR</Cont_Title_External_ID><Cont_Town>romford</Cont_Town><Cont_Web_Addr></Cont_Web_Addr><Cont_Cont_Position>HRBP</Cont_Cont_Position><Cont_Cont_Position_ID>13</Cont_Cont_Position_ID><Cont_Cont_Position_External_ID></Cont_Cont_Position_External_ID><Cont_Reg_Cont>0</Cont_Reg_Cont><Cont_Occ_Cont>0</Cont_Occ_Cont><Cont_Third_Party_Cont>0</Cont_Third_Party_Cont><Cont_Status>Active</Cont_Status><Cont_Status_ID>1</Cont_Status_ID><Cont_Status_External_ID></Cont_Status_External_ID></ImportRow></Import>&uploadMethod=Direct&ignoreMissingDropDownOptions=true&updateMatchedRecords=true&sync_Comp=false&deactivationStatus_Comp=0&sync_Cont=false&deactivationStatus_Cont=0&sync_Usr=false&deactivationStatus_Usr=0
Request Headers:
POST /API/Import/DropDownList/Standard HTTP/1.1
Host: abctrading.eploy.net
Content-Type: application/x-www-form-urlencoded; charset=utf-8
Content-Length: length
Authorization: Basic aWtma2RKc0pwa1FCbnJleE5Kc0U6ZUV0bnpjS3RtbUJQaEJSRk1Ud1k=
Request Body:
importData=<Import><ImportRow><External_ID>EXT-1</External_ID><Description>Commercial</Description><OrderID>0.00</OrderID><Active>0</Active></ImportRow></Import>&uploadMethod=Direct&dropDownType=Business+Areas&ignoreMissingDropDownOptions=true&updateMatchedRecords=true&matchingCriteria=DropDown_Description
SOAP Examples
Company Contact
Request Headers:
POST /API/Import/CompanyContact HTTP/1.1
Host: abctrading.eploy.net
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
Authorization: Basic aWtma2RKc0pwa1FCbnJleE5Kc0U6ZUV0bnpjS3RtbUJQaEJSRk1Ud1k=
Request Body:
<?xml version='1.0' encoding='utf-8'?>
<soap12:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap12='http://www.w3.org/2003/05/soap-envelope'>
<soap12:Body>
<ExternalID xmlns='http://tempuri.org/'>
<importData>
<![CDATA[
<Import>
<ImportRow>
<Comp_External_ID>EXT-15</Comp_External_ID>
<Cont_External_ID>EXT-1</Cont_External_ID>
<Cont_Addr1>146 Deramore Avenue 1</Cont_Addr1>
<Cont_Addr2>chadwell heath</Cont_Addr2>
<Cont_Addr3></Cont_Addr3>
<Cont_Branch></Cont_Branch>
<Cont_Comments></Cont_Comments>
<Cont_Country>UK</Cont_Country>
<Cont_Country_ID>186</Cont_Country_ID>
<Cont_Country_External_ID>UK</Cont_Country_External_ID>
<Cont_County>essex</Cont_County>
<Cont_Decision_Maker>1</Cont_Decision_Maker>
<Cont_DD_No></Cont_DD_No>
<Cont_Email>resourcing.manager@itssystems.co.uk</Cont_Email>
<Cont_Fax>0800 0734243</Cont_Fax>
<Cont_First_Name>Business</Cont_First_Name>
<Cont_Merge_Addr>0</Cont_Merge_Addr>
<Cont_Mob>0800 0734243</Cont_Mob>
<Cont_Pers_Email></Cont_Pers_Email>
<Cont_Pers_Mob></Cont_Pers_Mob>
<Cont_Pers_Tel></Cont_Pers_Tel>
<Cont_Position></Cont_Position>
<Cont_PostCode>rm6 4ta</Cont_PostCode>
<Cont_Ref></Cont_Ref>
<Cont_Salutation></Cont_Salutation>
<Cont_Surname>Partner</Cont_Surname>
<Cont_Tel>0800 0734243</Cont_Tel>
<Cont_Title>Mr</Cont_Title>
<Cont_Title_ID>1</Cont_Title_ID>
<Cont_Title_External_ID>MR</Cont_Title_External_ID>
<Cont_Town>romford</Cont_Town>
<Cont_Web_Addr></Cont_Web_Addr>
<Cont_Cont_Position>HRBP</Cont_Cont_Position>
<Cont_Cont_Position_ID>13</Cont_Cont_Position_ID>
<Cont_Cont_Position_External_ID></Cont_Cont_Position_External_ID>
<Cont_Reg_Cont>0</Cont_Reg_Cont>
<Cont_Occ_Cont>0</Cont_Occ_Cont>
<Cont_Third_Party_Cont>0</Cont_Third_Party_Cont>
<Cont_Status>Active</Cont_Status>
<Cont_Status_ID>1</Cont_Status_ID>
<Cont_Status_External_ID></Cont_Status_External_ID>
</ImportRow>
</Import>
]]>
</importData>
<uploadMethod>Direct</uploadMethod>
<ignoreMissingDropDownOptions>true</ignoreMissingDropDownOptions>
<updateMatchedRecords>true</updateMatchedRecords>
<sync_Comp>false</sync_Comp>
<deactivationStatus_Comp>0</deactivationStatus_Comp>
<sync_Cont>false</sync_Cont>
<deactivationStatus_Cont>0</deactivationStatus_Cont>
<sync_Usr>false</sync_Usr>
<deactivationStatus_Usr>0</deactivationStatus_Usr>
</ExternalID>
</soap12:Body>
</soap12:Envelope>
Drop Down
Request Headers:
POST /API/Import/DropDownList HTTP/1.1
Host: abctrading.eploy.net
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
Authorization: Basic aWtma2RKc0pwa1FCbnJleE5Kc0U6ZUV0bnpjS3RtbUJQaEJSRk1Ud1k=
Request Body:
<?xml version='1.0' encoding='utf-8'?>
<soap12:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap12='http://www.w3.org/2003/05/soap-envelope'>
<soap12:Body>
<ExternalID xmlns='http://tempuri.org/'>
<importData>
<![CDATA[
<Import>
<ImportRow>
<External_ID>EXT-1</External_ID>
<Description>Commercial</Description>
<OrderID>0.00</OrderID>
<Active>0</Active>
</ImportRow>
<ImportRow>
<External_ID>EXT-2</External_ID>
<Description>Industrial 2</Description>
<OrderID>0.00</OrderID>
<Active>1</Active>
</ImportRow>
<ImportRow>
<External_ID>FIN</External_ID>
<Description>Finance 2</Description>
<OrderID>0.00</OrderID>
<Active>1</Active>
</ImportRow>
</Import>
]]>
</importData>
<uploadMethod>Direct</uploadMethod>
<dropDownType>Business Areas</dropDownType>
<ignoreMissingDropDownOptions>true</ignoreMissingDropDownOptions>
<updateMatchedRecords>true</updateMatchedRecords>
</ExternalID>
</soap12:Body>
</soap12:Envelope>
Use Cases & Scenarios
When begining to use the API to synchronize Contact, Company and Drop Down List data, there may be several scenarios that affect how you should introduce the synchronization functionality. The most effective method for syncronizing data is using the External ID Import Type. This allows records to be uniquely identified with a reference that is maintained outside of Eploy, allowing you to ensure duplicates are not created while not needing to know the Eploy IDs for records. While it is also possible to create and update records using the Standard Import Type, this relies on every company name and contact name / email address being unique, to avoid duplication. The Standard import also limits you from being able to update these key details, for example if a department or contact changes their name.
Therefore, for new Eploy systems, when setting up the Company Structure and Contacts for the fist time, performing an External ID Import will allow the External IDs to be set in preparation for introducing the synchronization.
Existing Contact & Company Data
If you have existing records in Eploy, these are unlikely to have External IDs to begin with, therefore a one-off Standard or EployID Import may be necessary to set these up. The existing data can be exported from Eploy as a CSV file using a Document merge by selecing the 'Doc Merge' option and choosing CSV as the 'Merge To' option. This will contain the Eploy IDs for the existing records, which can be used to perform an EployID import to set the ExternalIDs. ExternalIDs can also be set using the Standard import, as long as all Company names/Contact name/email combinations are unique.
Existing Drop Down List Data
The Eploy IDs for Drop Down List entries are visible on the Drop Down Lists Admin page (Admin > Drop Down Lists). These can be used to perform an initial update of existing drop down entires to set the External IDs when preparing to introduce a Drop Down synchronization. External Drop Down IDs can also be set using the Standard import, as long as their descriptions are unique.
User Permission Templates
If introducing a Contact synchronization that needs to create user records and set permissions, the Usr_Copy_Usr_ID field can be used to copy permissions from an existing user in Eploy. The source user does not necessarily need to be active, therefore you can prepare user permission templates simply by adding User records, assigning the relevant permissions and then deactivating them (noting the User IDs for use within the Usr_Copy_Usr_ID field when calling the API).