Importing Contacts: Split and Combine Fields

Applies to: All ONTRAPORT accounts.

ONTRAPORT may have a different field structure than your old system. A common example is a single name field with both first and last names, that you need to import into ONTRAPORT's separate First Name and Last Name fields. This video includes information on splitting the field in the first chapter. More detail is provided below.

This article explains the basics of editing your CSV file to combine or split fields prior to import into ONTRAPORT. The normal tool used is Excel, but we include instructions for the free and open source OpenOffice and LibreOffice. Upon import, each field will occupy one cell of the spreadsheet.

These are the basic, default fields for the Contacts collection in ONTRAPORT. You can create custom fields during import for information not found in this list, but in cases where you have a single name field for both and first names, or multiple contact tags in separate fields, we strongly recommend changing your data in the CSV file before import.

Microsoft Excel's online help has an excellent article on this. The process is the same in Excel, LibreOffice and OpenOffice. All three use a function called "text to columns"

1. Inspect your data to determine how many additional columns you will need. The "text to columns" function splits the field at spaces, so if your data has just a first name and a last name, you need two additional columns added next to the Name column. If your data includes a middle name as well, you'll need three additional columns.
   
2. Add the number of new columns needed to the right of the column with the names.
   
3. Select the column you want to split.
   
4. On the Data tab, in the Data Tools group, click Text to Columns.
   
   
   
5. In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
   
   
6. In Step 2 of the wizard, select the Space check box, and then clear the other check boxes under Delimiters.
   
7. The Data preview box shows the first and last names in two separate columns.
   
   
   
   
8. Click Next.
   
9. In Step 3 of the wizard click a column in the Data preview box and then, under Column data format, click Text.
   
10. Repeat this step for each column in the Data preview box.
    
11. If you want to insert the separated content into the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example)
    
    
    
12. Important If you do not specify a new destination for the new columns, the split data will replace the original data.

13. Click the icon to the right of the Convert Text to Columns Wizard.

    
    

14. Click Finish.

15. Verify the data is correct. You may have to manually edit some data; as an example, some entries in the Last Name column may include a middle initial or a suffix such as "Jr".

You may have data in several fields that you want to combine into one field in ONTRAPORT. A common example of this are Contact Tags. If your CSV file has multiple Tags in separate columns you must format them into a single column, separated by either "," or "*/*" for import into ONTRAPORT. This can be done using either the & symbol or the "CONCATENATE" function. This silent 2 minute video shows how to combine fields.

Note: ONTRAPORT accepts either the standard CSV comma separated list enclosed in quotes as multiple values for a single field, or the proprietary "*/*" format to separate the values. These work exactly the same during the import process.

Using the & Symbol

In this example we have six columns, I through N, that may contain a Tag. Some cells are blank. We add a column next to N and name it Combined Tags.

1. Click in the first cell under the Combined Tags heading and type =
   
   
2. Click on the cell in the same row for the first Tag (column I)
   
3. Type &"*/*"&
   - for a standard comma separated list, use &","&
   
   
4. Click on the second cell in the same row (column J)
   
5. Type &"*/*"&
   - for a standard comma separated list use &","&
   
   
6. Repeat the process until you have all six tag columns listed in the formula
   
7. Your formula will look like this:
   
   =I2&"*/*"&J2&"*/*"&K2&"*/*"&L2&"*/*"&M2&"*/*"&N2
   - a standard comma separated list will look like:
   =I2&","&J2&","&K2&","&L2&","&M2&","&N2
   
   Here's a color coded example to explain it further. The cell references are in red:
   
   =I2&"*/*"&J2&"*/*"&K2&"*/*"&L2&"*/*"&M2&"*/*"&N2
   
   
8. Press ENTER, then copy the formula down for all the rows in the CSV file.
   
   
9. Create another blank column next to the Combined Tags column. Select the entire Combined Tags column, right click and select "Copy". Select the entire blank column created next to Combined Tags and right click and select Paste but paste the values only. This image shows how to do that in Excel:
   
   
   
   This image shows LibreOffice (similar to OpenOffice). Accept the default choices after selecting Paste Special in LibreOffice:
   
   
   
   
10. Rename the new column "Combined Tags for Import". Highlight the entire "Combined Tags" column with the formulas, right click and select Delete.
    
11. You can also delete the unnecessary columns for each of the multiple tags, I2 through N2 in our example. The data now looks like this:
    
12. ONTRAPORT ignores the "empty Tags". The duplicate */* or comma strings are simply
    ignored so you do not need to manually edit this list.
    
13. Here is a video of the process:
    
    

Using CONCATENATE

Some prefer to use the CONCATENATE formula. It uses the same format as the simple ampersand format used above. Instead of typing the equals sign and then clicking on the first cell containing a Tag, you type =CONCATENATE(, click the first cell and a comma, then */*, a comma, then click the second cell and so on. The completed formula would look like this:

=CONCATENATE(I2,"*/*",J2,"*/*",K2,"*/*",L2,"*/*",M2,"*/*",N2
- for a comma separated list, it looks like:
=CONCATENATE(I2,",",J2,",",K2,",",L2,",",M2,",",N2

In the formula the comma replaces each instance of the ampersand (&) character. There is no functional difference between the two methods and you are free to choose the one you feel most comfortable with. CONCATENATE is slightly more efficient when you do not need any characters between the values being combined.

ONTRAPORT saves your uploaded CSV in Contacts > Settings > Import Contacts. These files are stored for 60 days and then deleted. If you want to save the original upload CSV file be sure to archive it on your local computer.

Additional Knowledge Base articles:

• Importing Contacts: Using EU Format CSV
• Importing Contacts: Capitalizing Proper Names
• Importing Contacts: Import Failures - Cleaning Up Your CSV File

ONTRAPORT / Projects are step-by-step in-app instructions with a checklist to track your progress. You can stop and resume the Project at any time. Log into ONTRAPORT in another tab and click Importing Contacts to start the Project.