How to Prep Your CRM Data for Importing
Prior to importing your data into ClientLook commercial real estate software some prep work will be required, whether you do the import yourself using our self-import tool or we do the import for you. Each data point (first name, last name, city, state, ZIP code, etc) needs to be in its own individual column on your spreadsheet. Depending on where your data came from, some of these items may be combined into one column (such as the complete name, “John Smith”). You need to split these up prior to importing. Excel provides some processes to make this easier.
Here are some basic steps to standardize your data in Excel:
- Save your file as an Excel file (.xls or .xlsx depending on your version of Excel)
- Press ‘File’
- Press ‘Save As’
- Choose ‘CSV (Comma delimited)(*.csv)’
- Press ‘Save’
- Freeze the Top Row
- Press ‘View’
- Press ‘Freeze Panes’
- Press ‘Freeze Top Row’
- Save the File
This will enable you to scroll down and have the columns headers frozen at the top
- Add a Filter to your file
- Highlight the entire sheet by pressing the top left corner (in-between the letter A & number 1)
- Press ‘Data’
- Press ‘Filter’
- Save the File
This will enable you to look at all the values in each column without scrolling down the page to make sure the correct data is in the right column (i.e. ensure there are no phone numbers in the email column, etc.)
- Delete all the columns without any data or those that you do not want imported
- Press the letter at the top of the column
- Right Press your mouse
- Choose ‘Delete’
- Save the File
- Split the contact names into separate columns for ‘First Name’, ‘Middle Name’, ‘Last ‘Name’ and ‘Suffix’ if they are not already separated (you will NOT need to manually type in each of those data points in their respective fields, we are going to let Excel do it for us)
- Insert blank columns for each of the data points
- Press the letter at the top of the column to the right of the column with the unsplit names
- Right Press of your mouse
- Insert a column for each of the data points (i.e. ‘‘First Name’, ‘Middle Name’)
- Insert blank columns for each of the data points
-
- Label each blank column for each data point (i.e. ‘First Name’, ‘Last Name’)
- Save the File
-
- In the new ‘First Name’ column type the first name in the cell below the label, continue to type the next 2 or 3 first names in the respective cells below (Excel will recognize the pattern and continue the pattern all the way down the column)
- Press ‘Enter’ on your keyboard
- Save the File
- If Excel does not recognize the pattern you will force it to using ‘Flash Fill’
- Highlight the first name you typed, hold the left mouse button and drag down to the bottom of the column
- Press ‘Data’
- Press ‘Flash Fill’ (it has a lightning bolt on the icon)
- Save the File
Excel will automatically fill in the values based on the pattern you started. If it does not populate all of the cells correctly, press into one of the cells that is not populated or correct and type the correct value. It will auto correct the data in that column (you may need to do this more than once to get all the data correct).
-
- Repeat step 4 C and step 5 for each of the other new columns you added
- Save the File
- Standardize data (i.e. ‘States’, ‘Countries’, etc.)
- Press the filter in the right corner of the cell
- Press ‘(Select All)’ to unselect all the check boxes
- Select the values you want to standardize and press ‘OK’
- Add the desired value in the top cell
- Press the left mouse button on the bottom right corner of the cell and brag your mouse to the bottom of the column
- Save the File
You are now ready to import your data by either:
- The self-import tool (If you are using the self-import tool you will need to save your file as a .csv)
- Sending it to support@clientlook.com (you can leave it in Excel format)
Related posts