Prior to importing any data into The Layer please review our article Data Migration: What's involved?

Why do I need to cleanse my data? 

Cleansing your data is vital as it ensures that the data that you import into The Layer is as accurate and update to date as possible. We often find that when exporting data from a legacy CRM or collating from spreadsheets that not all of the data populated within the fields is accurate or correct.

Reviewing this data and cleansing it ensures that when it's imported into The Layer it will allow for a better user experience and allow users to trust the data presented to them as accurate.

In addition to this it will aide you to better manage your customers, from our experience at The Layer, we have found that there tends to be some common issues that we spot when looking at how data is stored and its structure and content.

The most popular issues include:

• Blank Data: in key areas where the data is used throughout the Layer e.g. customer name, pricing and key date fields etc. 
• Duplicate data: This can be fields, entire columns or complete records 
• Wrong Data: The wrong data can be populated in the wrong field e.g. a phone number instead of an email address
• Incorrect dates: Common examples include invalid contract end dates and made up/invalid call back dates
• Incorrect formats: A typical example of this is date formats, they must be imported as DD/MM/YYYY in The Layer but may be populated in your data as June 2016 
• Merged Data: A typical example of this is where a contacts name have been merged into one field e.g. Joe Bloggs whereas The Layer requires these to be split out into two fields e.g. Joe and Bloggs 
• Record Owners: Typically you may find that users who are no longer with your organisation are still assigned as a record owner, only users that exist within The Layer can be marked as a record owner and the entry value must match their Layer username 
• Phone Numbers: You may find that excel has stripped the leading zero from your telephone numbers or has truncated them as a scientific (exponential) notation e.g. 1.24508E+12a or a space in a number or brackets within the numbers etc. 
• Email addresses: Missing or invalid email addresses or you may find you have duplicate email addresses across different records 
• Contract Terms: You may have a contract start and expiry date but no term has been entered, rules can be used to calculate the term based on these, or you may find it has been entered as word and not an integer, in The Layer it must be an integer. 
• Drop down options: Some fields within the Layer use drop down options and these must be used when populating any data within your import template to ensure the correct drop down option is selected 

These are just some of the most common issues that can be presented when trying to cleanse your data. 

What format do my import files have to be in?

All files that are imported into The Layer must be imported in a .CSV format, to do this in excel simply ensure the Save as type is CSV (Comma delimited): 

Removing blank records at the end of a CSV file

CSV stands for "comma-separated values" and it means that the data within your excel file is separated (delimited) by a comma and not an excel cell, e.g. you import excel file is as follows:

When this file is saved as a comma-separated value the data will be presented as per the below when opened with notepad:

Each record that is imported into The Layer is separated by a new line (ROW) and the comma's represent a new field entry so we need to be sure there are no comma's at the end of the data like above, as this will result in blank records being created, to remove these follow these steps:

---

Author: Natasha Cox