How to prepare your data for import
- Roly Birkwood
- Angus Mundin (Unlicensed)
Â
Bulk Upload Excel CSV Template
This guide will take you through how to prepare your data within the provided CSV template.
This will include notes on how the information needs to be formatted, along with sample primary key (PK) information, based on the systems' default ‘off the shelf’ settings.
Each row on the import template represents (and will create a new) a customer, case and product. If you have 2 or more applicants on a row, they will appear together on the same case.
If you have any questions please email the training team or support team.
NOTE:
The file needs to be in a .CSV format, with plain text, ideally with string values quoted and new records separated by a hard return.
The first row should be a header row with the relevant field titles from the tables below.
Please download and use the following template:
Â
To make life easier spilt-out the the data import into the following topics:
Mandatory Fields
Header Name | Description | Format Information | Mandatory Field | Notes |
Surname1 | App 1 Surname | Smith | Yes | Â |
DOB1 | App 1 Date of Birth | dd/mm/YYYY | Yes | if DOB is not known you can enter: null |
MortgageSubType-1 | PRODUCT TYPE/NAME | Mortgage | Yes | Exact product name required |
Applicant 1 Details
Header Name | Description | Format Information | Mandatory Field | Notes |
Title1 | App 1 Title | Miss | Â | Â |
Forename1 | App 1 Forename | Tina | Â | Â |
MiddleName1 | App 1 Middle Name | Louise | Â | Â |
Surname1 | App 1 Surname | Smith | Yes | Â |
DOB1 | App 1 Date of Birth | dd/mm/YYYY | Yes | if DOB is not known you can enter: null |
App1AddressText | App 1 Address | 123 The Street |  |  |
DayPhone1 | App 1 Work Number | 0207 123 4567 | Â | Â |
MobilePhone1 | App 1 Mobile Phone | 07777 123456 | Â | Â |
EveningPhone1 | App 1 Home Number | 01275 400660 | Â | Â |
Email1 | App 1 Email | Â | Â | |
Notes1 | Specific Notes Regards The Applicant E.G. Works Nightshift, Call After 11Am | Free text field (128 Character limit) | Â | Â |
Sex1 | Sex - M or F | 0= M, 1= F | Â | Â |
Smoker1 | Smoker - Y/NÂ | 2= No, 1= Yes | Â | Â |
EmploymentStatus1 | Employed Status | Â | Â | |
ExpectToRetire1 | Expected Retirement Age | 65 | Â | Â |
Occupation1 | Occupation | Office Manager |  |  |
Income1 | Annual Salary | 12345.00 | Â | Do not include comma or pound signs |
Title values
The applicant title descriptions will come across as these appear in the CSV.
Description (Samples) |
Mr |
Mrs |
Miss |
Ms |
Dr |
Employed Status values
The following descriptions are the default that come with the system, these are the only permitted entries.
Description |
Full-time |
Part-time |
Unemployed |
Self-employed |
House Wife/Home-maker |
Retired |
Student |
Header Name | Description | Format Information | Mandatory Field | Notes |
Title2 | App 2 Title | Mr | Â | Â |
Forename2 | App 2 Forename | Terry | Â | Â |
MiddleName2 | App 2 Middle Name | John | Â | Â |
Surname2 | App 1 Surname | Smith | Yes | Â |
DOB2 | App 2 Date of Birth | dd/mm/YYYY | Yes | if DOB is not known you can enter: null |
App2AddressText | App 2 Address | 123 The Street |  |  |
DayPhone2 | App 2 Work Number | 0207 123 7654 | Â | Â |
MobilePhone2 | App 2 Mobile Phone | 09777 123999 | Â | Â |
EveningPhone2 | App 2 Home Number | 01275 400660 | Â | Â |
Email2 | App 1 Email | Â | Â | |
Notes2 | Specific Notes Regards The Applicant E.G. Works Nightshift, Call After 11Am | Free text field (128 Character limit) | Â | Â |
Sex2 | Sex - M or F | 0= M, 1= F | Â | Â |
Smoker2 | Smoker - Y/NÂ | 2= No, 1= Yes | Â | Â |
EmploymentStatus2 | Employed Status | Â | Â | |
ExpectToRetire2 | Expected Retirement Age | 65 | Â | Â |
Occupation2 | Occupation | Firefighter |  |  |
Income2 | Annual Salary | 12345.00 | Â | Do not include comma or pound signs |
Title values
The applicant title descriptions will come across as these appear in the CSV.
Description (Samples) |
Mr |
Mrs |
Miss |
Ms |
Dr |
Employed Status values
The following descriptions are the default that come with the system, these are the only permitted entries.
Description |
Full-time |
Part-time |
Unemployed |
Self-employed |
House Wife/Home-maker |
Retired |
Student |
Header Name | Description | Format Information | Mandatory Field |
1-LegalStatus | Company Legal Status | Â | |
1-CompanyName | Business Name | EKeeper Group | Yes |
1-CompanyAddressLine1 | Address Line 1Â | 123 The Road | Â |
1-CompanyAddressLine2 | Address Line 2 | Town | Â |
1-CompanyAddressLine3 | Address Line 3 | City | Â |
1-CompanyAddressLine4 | Address Line 4 | County | Â |
1-CompanyPostcode | Address Postcode | BS20 0DD | Â |
1-CompanyTelephone | Company Phone Number | 0207 123 4567 | Â |
1-CompanyFax | Company Fax Number | 01275 123456 | Â |
1-CompanyEmail | Company Email Address | Â | |
1-CompanyWebsite | Company Website | Â | |
1-CompanyNotes | Specific Notes Regards The Company E.G. Office hours 10am to 4 pm | Free text area | Â |
1-CompanyRegistrationNo | Company Registration Number | 12345678 | Â |
1-IncorporationDate | Company Incorporation Date | dd/mm/YYYY | Â |
Legal Status values
The following property type PKs and descriptions are the default that come with the system, these do not change.
PK Value | Description |
1 | Sole Trader |
2 | Limited Liability Partnership |
3 | Limited Company |
4 | Public Limited Company |
5 | Ordinary Partnership |
6 | Agricultural |
7 | Charity |
The following column header will determine the first product, which will be assigned to the applicant(s) case.
The upload will fail if it doesn't match with a product name (See Product Type/Name values).
Header Name | Description | Format Information | Mandatory Field | Notes |
MortgageSubType-1 | PRODUCT TYPE/NAME | Mortgage | Yes | Exact product name required |
Product Type/Name values
The following product type/name descriptions are the default that come with the system and will be different if you changed these values in System Settings > Products
Description |
Mortgage |
Remortgage |
Buy to Let |
Commercial Mortgage |
Asset Finance |
Screenshot Example
The following column headers determine the product information to be assigned to the product above.
Header Name | Description | Format Information | Mandatory Field | Notes |
1-AppName | Application Reference | Internal reference number | Â | This can be a mixture of letter, number and special characters, as exampled to the left. |
1-LenderReference | Lender Reference | A/12345-Z | Â | This can be a mixture of letter, number and special characters, as exampled to the left. |
1-Address1 | Security Address Line 1 | 123 The Street | Â | No return carriage |
1-Address2 | Security Address Line 2 | Town | Â | No return carriage |
1-Address3 | Security Address Line 3 | City | Â | No return carriage |
1-Address4 | Security Address Line 4 | County | Â | No return carriage |
1-PostCode | Security Address Postcode | BS20 0DD | Â | No return carriage |
1-RepaymentMethod | Product Repayment Method | Exact Repayment Method Name | Â | System Settings >> Repayment Methods |
1-MortgageAmount | Loan Amount | 12345.00 | Â | Do not include comma or pound signs |
1-PurchasePrice | Purchase/Valuation Price | 12345.00 | Â | Do not include comma or pound signs |
1-StagePK | Product workflow status | Â | NOTE: Do not use 1-StagePK if you are using 1-Stage, one or the other. | |
1-Stage | Product workflow status | New Enquiry | Â | Exact workflow stage name required |
1-AdvisorPK | Adviser P | PK Number | Â | NOTE: Do not use 1-AdvisorPK if you are using 1-Adviser, one or the other. |
1-Adviser | Protection Adviser | John Smith | Â | Please ensure users have been created in the system before uploading this data |
1-UserPK2 | Admin PK | PK Number | Â | NOTE: Do not use 1-AdminPK if you are using 1-Admin, one or the other. |
1-Administrator | Protection Admin | John Smith | Â | Please ensure users have been created in the system before uploading this data |
1-Lender | Product Lender | Halifax | Â | Â |
1-Term | Product Term | 25 | Â | Please only enter this value in YEARS |
1-DealTerm | Deal Term | 36 | Â | Product deal term, where no exact end date applies e.g. 24 month tracker |
1-CommissionAmt | Expected Lender Proc Fee Amount | 12345.00 | Â | Â |
1-NextStageDue | Date for next follow-up call | dd/mm/YYYY | Â | 'Next Stage Due' use this for your follow-up date. |
1-InterestRateType | Product Rate Type | Exact Interest Rate Type Name | Â | Â |
1-Rate | Product Rate Amount | 2.75 | Â | Do not include the % sign |
1-LeadCreationDate | Date Of Enquiry | dd/mm/YYYY | Â | Â |
1-DateAmended | Mortgage Expiry date | dd/mm/YYYY | Â | Â |
1-DateMade | Signup Date | dd/mm/YYYY | Â | Â |
1-DateComplete | Mortgage Completion Date | dd/mm/YYYY | Â | Â |
1-FollowUpDate | Product Expiry Date | dd/mm/YYYY | Â | Â |
1-PurposeOfLoan | Loan Purpose | Â | Â | |
1-BuyerType | Buyer Type | Â | Â | |
1-Relationship | Relationship between the applicant's on the product | PK Number | Â | Â |
1-MortgageSource | Source drop-down option | Exact Source Name | Â | System Settings >> Sources If source drop-down option is not in use, please use the following option 'Lead Provider' instead |
1-LeadProvider | Mortgage Lead Provider - Free Text | John Smith | Â | Â |
Repayment Method values
The following repayment methods are the defaults that come with the system and will be different if you changed these values in System Settings > Repayment Methods
Enter the Repayment Method exact name (description)
Description |
Repayment |
Endowment |
PEP |
Pension |
Interest Only |
With Profits |
Unit Linked |
ISA |
Part and Part |
Stage values
The following stage PKs and descriptions are the default that come with the system and will be different if you changed these values in System Settings > Stages
PK Value | Description |
25 | Enquiries |
32 | Chasing Lead |
31 | Appointment & Fact Finding |
23 | Market Research |
1 | DIP Process |
28 | Application to Lender |
2 | Further Information |
3 | Valuation Instructed |
4 | Valuation Received |
7 | Offer Issued |
9 | Exchanged |
8 | Completed Awaiting Payment |
5 | Waiting to be Recontacted |
6 | Remortgage - Contact Made |
33 | Archive |
19 | Not Proceeding |
Interest Rate Type values
The following interest rate type PKs and descriptions are the default that come with the system and will be different if you changed these values in System Settings > Interest Rate Types
PK value | Description |
1 | Variable |
2 | Fixed |
3 | Capped |
4 | LIBOR |
6 | Discounted |
7 | Tracker |
8 | Stepped (Discount) |
9 | Stepped (Fixed) |
10 | Conditional Sale |
11 | Finance Lease |
12 | Hire Purchase |
Purpose Of loan values
The following purpose of loan descriptions are the default that come with the system and will be different if you changed these values in System Settings > Purpose Of Loans
Description |
None Selected |
New - Purchase |
Remortgage |
Rmg - Capital Rising |
Rmg - Home improvements |
Rmg - Debt Consolidation |
Rmg - School Fees |
Rmg - Holidays/Cars |
Rmg - Buy other property |
Rmg - Business purposes |
Rmg - Divorce Settlement |
Buy to let |
Let to buy |
Buy to Let Remortgage |
Further Advance Purchase |
Further Advance Remortgage |
NOT USED |
Secured Loan |
Right To Buy |
Product Transfer |
NOT USED |
Equity Release |
Lifetime Mortgage |
Buyer Type values
The following buyer type PKs and descriptions are the default that come with the system and will be different if you changed these values in System Settings > Buyer Types
PK value | Description |
1 | Homeowner |
2 | First time buyer |
3 | No Current Mortgage |
4 | Existing Mortgage Single |
5 | Existing Mortgage Joint |
The following column header will determine the second product, which will be assigned to the applicant(s) case.
The upload will fail if it doesn't match with a product name (See Product Type/Name values).
Header Name | Description | Format Information | Mandatory Field | Notes |
InsuranceSubType-2 | PRODUCT TYPE/NAME | E.G. Life Only / Life & Critical Illness / Income Protection | Yes | Exact product name required |
Product Type/Name values
The following product type/name descriptions are the default that come with the system and will be different if you change these values in System Settings > Products
Description |
Life Cover |
Life & Critical Illness |
Critical Illness |
Income Protection |
Multi Benefit |
Screenshot Example
The following column headers determine the product information to be assigned to the product above.
Header Name | Description | Format Information | Mandatory Field | Notes |
2-ReferenceNo | Product reference | A/12345-Z | Â | This can be a mixture of letter, number and special characters, as exampled to the left. |
2-ReferenceNo2 | Internal reference | A/12345-Z | Â | This can be a mixture of letter, number and special characters, as exampled to the left. |
2-Adviser | Protection Adviser | John Smith (Exact system user name) | Â | Please ensure users have been created in the system before uploading this data |
2-Administrator | Protection Administrator | John Smith (Exact system user name) | Â | Please ensure users have been created in the system before uploading this data |
2-Provider | Product Provider | AXA | Â | Â |
2-TaskText | Other useful product info | Free Text area | Â | Â |
2-Status | Product workflow status | New Enquiry | Â | Exact workflow stage name required |
2-Premium | Product premium | 12345.00 | Â | Do not include comma or pound signs |
2-SumAssured | Main sum assured | 12345.00 | Â | Do not include comma or pound signs |
2-Term | Product term | 25 | Â | Please only enter this value in YEARS |
2-DateWritten | Product written date | dd/mm/YYYY | Â | Â |
2-OnRiskDate | Product on-risk date | dd/mm/YYYY | Â | Â |
2-FollowupDate | Date for next chase-up | dd/mm/YYYY | Â | Â |
Status values
The following status descriptions are the default that come with the system and will be different if you change these values in System Settings > Protection Statuses
Description |
Not Discussed |
Appointment Booked |
Quotation Provided |
Declined by Client |
Application Submitted |
With Underwriters |
Medical Report |
Acceptance Terms Issued |
Policy on Risk |
Paid |
Not Applicable |
Clawed Back/Lapsed |
The following column header will determine the third product, which will be assigned to the applicant(s) case.
The upload will fail if it doesn't match with a product name (See Product Type/Name values).
Header Name | Description | Format Information | Mandatory Field | Notes |
InsuranceSubType-3 | PRODUCT TYPE/NAME | Building & Contents | Yes | Exact product name required |
Product Type/Name values
The following product type/name descriptions are the default that come with the system and will be different if you change these values in System Settings > Products
Description |
Building & Contents |
Screenshot Example
The following column headers determine the product information to be assigned to the product above.
Header Name | Description | Format Information | Mandatory Field | Notes |
3-ReferenceNo | Product reference | A/12345-Z | Â | This can be a mixture of letter, number and special characters, as exampled to the left. |
3-ReferenceNo2 | Internal reference | A/12345-Z | Â | This can be a mixture of letter, number and special characters, as exampled to the left. |
3-Adviser | Protection Adviser | John Smith (Exact system user name) | Â | Please ensure users have been created in the system before uploading this data |
3-Administrator | Protection Administrator | John Smith (Exact system user name) | Â | Please ensure users have been created in the system before uploading this data |
3-Provider | Lender / Provider | Paymentshield | Â | Â |
3-TaskText | Other useful product info | Free Text area | Â | Â |
3-Status | Product workflow status | New Enquiry | Yes | |
3-Premium | Product premium | 12345.00 | Â | Do not include comma or pound signs |
3-SumAssured | Main sum assured | 12345.00 | Â | Do not include comma or pound signs |
3-Term | Product term | 12 | Â | Please only enter this value in MONTHS |
3-DateWritten | Product written date | dd/mm/YYYY | Â | Â |
3-OnRiskDate | Product on-risk date | dd/mm/YYYY | Â | Â |
3-FollowupDate | Date for next chase-up | dd/mm/YYYY | Â | Â |
3-AddressText | Associated security address | 123 The Street | Â | Â |
3-Postcode | Associated security address postcode | BS20 0DD | Â | Â |
3-YearBuilt | Security property year built | YYYY | Â | Â |
3-NoBedrooms | Security property bedroom number | 5 | Â | Â |
3-PropertyType | Security property type | Â | e.g. |
Property Type values
The following property type PKs and descriptions are the default that come with the system and will be different if you change these values in System Settings > Property Types
PK value | Description |
1 | Terraced House |
2 | Detached House |
3 | Semi-Detached House |
4 | Terraced Bungalow |
5 | Detached Bungalow |
6 | Semi-Detached Bungalow |
7 | Flat |
8 | Dormer Bungalow |
Status values
The following status descriptions are the default that come with the system and will be different if you change these values in System Settings > Protection Statuses
PK Value | Description |
1 | Not Discussed |
36 | Appointment Booked |
10 | Quotation Provided |
19 | Declined by Client |
2 | Application Submitted |
16 | With Underwriters |
20 | Medical Report |
3 | Acceptance Terms Issued |
4 | Policy on Risk |
6 | Paid |
13 | Not Applicable |
11 | Clawed Back/Lapsed |