Preparing your client list for importing into Yocale

One of the great features of Yocale is the ability to import your clients from other lists and systems. Like any technology, in order for Yocale to be able to put the imported information into the correct fields, the import file must be in a certain format that Yocale can understand.

So how do you make sure you get your file into the correct format?

The first step is to look at the file you have and compare it to the Yocale import sample file.

Yocale’s import requires that the file be in the following format:

Notice the first row in the spreadsheet has to have the labels in exactly the same order and spelled exactly the same, this way Yocale can recognize the type of data below each header. There cannot be more columns than this (See below for what to do with extra information) or less columns than this.

It’s always a good start to download the sample file available from the import clients page when you are logged in. You can download this sample file from here.

The second step is to look at the file you have exported from your other software, for the purpose of this tutorial, we will use the file below:

It is obvious this file varies quite a bit from the required format that Yocale needs, but it’s actually not difficult to change this file to the required format, it just needs some editing.

Looking at the exported file, it is obvious that we don’t have any data in columns O,Q, and W (Company, Therapist, and Account Number. Therefore we should remove these columns to simplify the file.

To remove, you can always click on the column header (e.g. “O”), then select the “Delete Column” option, this will vary based on the sheet editing software you are using.

Do this for all columns you don’t need to import into the new file. In this case, we will delete the following columns since they will be of no use for our new Yocale system:

Our file now looks like this:

In order for Yocale to recognize the import data, the column names must match what Yocale is looking for, so, in our case, We need to change the column names as follows:

The file now looks like this:

Yocale requires the columns being imported to be in the same order as shown in the sample file. We therefore must move the columns in our file around to match that order.

For example, we need to move the “UserName” column to be the first on the left (i.e. from K to A)

To do this, use your sheet editing software to cut and insert the column where you want it, we will therefore;

Select the entire UserName column (by clicking on the K header) – Column will be selected

Right click and choose “Cut”

Select the first column (by clicking on the A header)

Right click then choose “Insert Cut Cells” (This may vary depending on your software)

Our file will now look like this:

You can see that “Occupation” has moved to the right of “UserName”, so we are closer to what we want.

We will therefore move the “Occupation” column to the right in order to have the names. We do it the same way by selecting the column header, selecting “Cut” and Inserting Cut Cells where we want to put them. We will also move the following columns:

Our file will now look like this:

We are starting to look a lot more like the desired outcome, but still not quite there.

When looking at our file, it is obvious that we are missing a “Telephone” column (to the left of “CellPhone” and a “Country” column, to add these columns we just right click on the column to the right of the desired column, the choose “Insert” option (may vary for your software), this will insert a new column to the left of the selected one, then we rename the column to the missing one.

We will do the same for the “Country” column, and our file will look as follows:

In our import file, we have some columns that don’t exist in the Yocale sample file, these cannot be imported as they are, however, they can be merged into the “Comments” column which shows up as a note within the client record in Yocale, so in our case, we want to merge “Daytime Phone”, “Evening Phone”, “Heard Via”, and “Occupation” columns into the “Comments” column because we want to keep this data about our clients.

Before starting this, think about the format you want for your comments. We have some client rows that have a comment in them so we also don’t want to lose these, but we also have four other columns to merge.

In this case, we decided we will merge the columns to look like this: <Comments> | “Daytime Phone: “ <Daytime Phone> | “Evening Phone: “ <Evening Phone> | “Source: “ <Heard Via> | “Occupation: “ <Occupation>.

We can therefore create a formula in our spreadsheet to take these values and merge them together

The easiest way if to just create the formula to always add everything regardless of the value in the columns being merged, this means that if none of the columns have any value in them, the value of the comments field will look like this: “ | Daytime Phone: | Evening Phone: | Source: | Occupation: .”

This of course doesn’t look very nice, so we can use some of the spreadsheet software features to only add a value if the row has a value in it. This will vary depending on your software, the example below shows how to do it in Microsoft Excel.

Once the formula is entered, expand it to the rest of the rows by clicking on the dot on bottom right corner of the cell, then dragging it down until the end of the file, this will automatically adjust the cell numbers to the ones you are dragging into.

Our file now looks like this:

We have the merged comments, but they are still in a formula value, i.e. we will not be able to use it the way it is to import, we must convert them into a text value. To do this, we can copy and paste the column as values.

Click on the column header to select the whole column

Right Click and select “Copy” to take the column values

Click on the column to the right of this column (in this case, T1) then right click and select the “Paste Value” option:

Once pasted, you will see a copy of the “MergedComments” field in the T column, this now should have all the values and look identical to the S column.

We now don’t need any of the original columns and can delete them by selecting them and choosing the “Delete” option.

Then we rename the “MergedComments” column header to “Comments” to match the import file requirements.

Our file will now look as follows:

We now have the correct columns in place and don’t have any extra ones.

Time to start looking at the data (rows) in our import file.

Having the correct columns is only half the story for preparing your import file. While importing the data, it’s a good opportunity to make sure that the data you import is accurate and complete, as well as follows the import requirements.

The first step is to ensure that all the required fields are entered. For rows (records) that don’t have values in some required fields, you may choose to delete (not import) these records, or you may choose to populate the required fields.

In our case, “Gender”, “Telephone”, “Country”, and “DOB” don’t have any values in them. The first three (except for “DOB”) are required, so we must populate them in order to import the data.

“Telephone” is easy since for the most part, all the records we are importing have a “CellPhone” value, so we can just copy and paste the value from “CellPhone” into the “Telephone” column.

To copy and paste,

Click on the second row (below “CellPhone” header) where the first value actually is (F2), then drag and select the entire column (without the header)

Click on the “Telephone” column on the second row (E2), the right click and Select “Paste” which will populate the values of Telephone from “CellPhone”

Our sheet will not look as follows:

“Address”, “City”, “State”, “Zip”, are all required fields for import. We therefore need to populate a value for them. An easy way to do that is to enter your own business address into any rows that don’t have these values.

You can do that by first sorting on “Address”, then entering your business address into the first empty row, then dragging and auto-populating the rest of the empty rows. Be careful not to overwrite the records that have real addresses, and not to auto-increment numbers in the addresses (Excel does that so it’s best to enter the address on the first three rows manually, then select all then drag down).

We do that for all the Address fields, including country. Note: Populate your country as a two letter code, e.g. “CA” for Canada, “US” for USA, “NZ” for New Zealand, etc.. use the Google country code.

For “Gender” if your old software exported this value, make sure that the value is either “F”, “M”, or “N” (“N” means “Not Specified”)

Use your sheet software to populate this value for the appropriate value, put “N” if you don’t know.

Our file now looks as follows:
​

“ClientSince” is also a required field, and should be in a “MM/DD/YYYY” format. If this value is not populated in your source data, you can put the date of the import or another date of your choosing.

Like the previous required fields, if you sort the data by “ClientSince” then scroll down to the end, any rows with no “ClientSince” value will be at the bottom, you can then populate the field as necessary or remove the records if they are invalid.

This step is optional but will make your life easier because it will reduce the number of records in your list as well as reduce the number of files you need to create (See splitting your file)

Check to see if there are any junk records, for example, records that neither have an email or a phone number, or records that have no name entered, or records where the name = “Test”. These types of records just move junk from your old system to your new system and is worth cleansing.

The easiest way to do this is to sort by different fields, e.g. if we sort the records by “UserName” and scroll down to the end of the file, we find:
​

The last set of records don’t have a phone number or an email address, these will most certainly be rejected by Yocale. You can either enter one of the two fields for these records, or delete them. We will delete them.

Sorting by “Telephone”, we find the following:

Obviously these numbers were entered at one point because they were required when they were not available. If these records don’t have an email address, then they will be junk data within your system since there is no way of communicating with this client. But if there is an email address then just deleting the values in the “Telephone” and “CellPhone” fields should be sufficient.

Yocale’s import tool will only import a maximum of 1000 clients in one file. So if your list has more than 1000 clients, you will need to split it up into multiple files of 1000 clients each (The number will depend on how many total clients are being imported).

In our case, our file has 1530 records, so we will need 2 files to be created in order to import the full clients list. The import will need to be done twice.

Splitting the file will vary depending on your spreadsheet software, and in most cases it is a manual process.

One simple way of doing it would be to create two copies of the file, then in the first file delete all rows after row 1000, then in the second file, delete the first 1000 rows.

You can also use tools like the CSV file Splitter tool (Download here) to automatically split the file for you, but you must have saved it into CSV format (See below) for get there.

One very important point is to ensure you save the file as a comma separated file, some software will export the files with different separators (e.g. tab separated) , so make sure you save it as comma separated.

From the File menu, choose “Save As”, then in the “File Type” choose “.CSV (Comma Delimited)”

Remember, Yocale can do this for you as part of your setup, contact support@yocale.com or your Customer Success Manager.