Excel is an excellent tool which allows you to build data lists and saving them with the format of csv means the data is ready to be imported into OpenCRM. As we have lots of experience of formatting data to get it ready for import we have put this handy guide together for you.

1. How can I join two cells into one (concatenate)?
2. How do I split a field with 'Firstname Lastname' into two separate columns?
3. How can I add a leading Zero (0) into a column, for example in phone numbers?
4. How do I add a line break within a cell?
5. How do I import to multi-select fields?
6. How can I copy values from one spreadsheet to another (VLOOKUP)?
7. How do I get Excel to recognise the date format yyyy-mm-dd hh:mi:ss which is exported to Excel from OpenCRM?

1. How can I join two cells into one (concatenate)?

Sometimes data you need to import into OpenCRM will be in a different format - for example your data source may have your "ADDRESS LINE 1", "ADDRESS LINE 2" and "ADDRESS LINE 3" which need to all be imported into the OpenCRM field "ADDRESS STREET".

Merge more than one cell with a line break between each data row

Here is an example of a record where the Company Street Address is mapped to three separate fields in the import data, and this needs to be mapped to one field "ADDRESS STREET" to be imported into OpenCRM:

Firstly you need to insert a blank row to contain the new Address field. Right-click the row "D" and select Insert from the menu:

With this blank row added you need to add a formula which tells the programme which rows to merge into one. 

This formula =A2&CHAR(10)&B2&CHAR(10)&C2 tells you to merge cells A2, B2 and C2 into the one new cell D2, and to put a line break between each row.

When you import the data into OpenCRM (saving the Excel sheet as a csv document), you can now map the new STREET ADDRESS field to the relevant field in OpenCRM and it will show as required in OpenCRM:

Note: Where your data may contain blank cells the above formula will leave you with blank rows.

For example:
Cell 1 - The Manor House, Cell 2 -  BLANK, Cell 3 - Mains Lane 

Your merged cell would look like:

The Manor House

Mains Lane

To avoid this you can use this alternative method:

First concatenate your cells with a comma between with a formula like this 

=CONCATENATE(G:G,",",H:H,",",I:I)

Where you had blank cells you will have commas double up you can strip these additional commas by simply using the find a replace function, e.g. Find ,,, Replace with ,

Once you have each piece of information separated by a single comma use Find and Replace again to replace the commas with a line break. To insert the line break within the "Replace with" box press CTRL+SHIFT+J

2. How do I split a field with 'Firstname Lastname' into two separate columns?

This is particularly useful when you have a spreadsheet that contains data that you wish to import into OpenCRM.

Quite often the data for the 'name' will be held in a single field, in the format 'John Smith'. OpenCRM holds the Contacts name in two fields, firstname and lastname, and this requires the data in this single column to be split in to separate columns for the import to be as easy as possible.

To split the data in this example follow these steps ;

• Create a number of blank columns to the right of the name column you are looking to split (if you have a straight forward 'firstname lastname' format, then you will require 1 new column, however, if you have a double surname, then you may need 2 blank columns, as the data is going to be split into its separate parts
• Select the column you wish to split (click on the column header)
• Click on the DATA menu
• select the Option Text to Columns
• This Wizard will lead you through the options available, the most common choice will be to select how the text (that's the 'firstname lastname' needs to be split, in our example this is split using the 'space' command - by scrolling through the pages (next and previous) you can make these selections and preview the data as it will be written after you FINISH the Wizard.

This will split the data into separate columns.

3. How can I add a leading Zero (0) into a column, for example in phone numbers?

Excel has a tendency to strip the leading 0 in numeric fields can. This can be very annoying when you are preparing data for import such as phone numbers where the leading zero is a part of the phone number.

Here is how you can insert a zero without having to copy and paste into a new field or column:

1. Press [Ctrl] and "1" or right mouse click and select the option to Format Cells.

2. Under Category, click Custom.  

3. Enter "0"# in the Type box (where you should see "General" displayed)  

4. Click OK.  

To replace the leading zero, select the cells and apply the new custom format.

4. How do I add a line break within a cell?

In most word applications, pressing Enter moves the cursor to the next line. When you press Enter in Excel, it does not add a line break within a cell. Instead, it places the cursor in the cell below.

If you want to add a line break within a cell, press Alt + Enter, instead of just Enter.

5. How do I import to multi-select fields in OpenCRM?

If you want to import into a multi-select field in OpenCRM, the values in your CSV file need to be comma separated. 

So they should appear like this:

6. How can I copy values from one spreadsheet to another (VLOOKUP)?

This is a really powerful function for copying information and related data from one excel file to another.

To give an example:

You have one Excel file with all your Company records, and another with all your Contact records. But certain information you need to be present on BOTH sheets (eg Assigned to, or External ID) is only on the Company record and not the related Contact. A VLOOKUP enables you to look up information from one the Companies records and paste it onto the matching records in the Contacts sheet.

This function could also be used if you wish to add an external ID to more than one data sheets before you import, making matching up of records in OpenCRM so much easier.

This is done using a function called VLOOKUP. To access this function go to the Formulas tab, select Insert Function, and choose VLOOKUP from the menu. If this is not listed you can find it by using the Search box at the top.

This opens up a window where you need to enter information into four fields.

For each of these fields, there is an on-screen explanation about what needs filling in.

Although this may seem like a complicated procedure it is actually very straight forward when carried out step by step.

Here is an excellent guide which takes you through the individual steps, with detailed screenshots and explanation:

http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/

7. How do I get Excel to recognise the date format yyyy-mm-dd hh:mi:ss which is exported to Excel from OpenCRM?

For excel to understand this format as a date instead of general text, the file will need to first be saved as a CSV file.

In Office 2003 this is done by clicking on the file menu, clicking save as, and then selecting the format CSV (Comma delimited) from the "Save as type" drop down menu.  After that, enter the file name you wish to use and click save.

In Office 2007, click on Home Button (the circle at the top left of the screen), click save as, and select "other formats" and set the "save as type" drop down to be "CSV (Comma delimited)".  After that, enter the file name you wish to use and click save.

Once it is saved in the CSV format, open up your CSV file you have just created and the date format will be a localised date format.

OpenCRM also has the option to export reports to a CSV file which will save you needing to create the CSV file from the Excel spreadsheet.