Company Blog Support
Frank Hagan
posted

Contents

    Back to Top

    Importing Contacts: Combining and Splitting Fields

    About

    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 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.

    Recommended Fields for Your First Import

    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.

    First Name     Zip Code
    Last Name     Country
    Email     Fax
    Title     SMS Number
    Company     Office Phone
    Address     Birthday
    Address 2     Website
    City     Referring Page
    State     Contact Tags

    Splitting a Field into Two Fields

    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".

     

    Combining Fields

    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 "*/*" for import into ONTRAPORT. This can be done using either the & symbol or the "CONCATENATE" function.


    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.

    combine 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 &"*/*"&

    4. Click on the second cell in the same row (column J)

    5. Type &"*/*"&

    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

      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:
      tags ready for import
    12. ONTRAPORT ignores the "empty Tags". The duplicate */* 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

    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.

    Other Resources

    Additional Knowledge Base articles:

    ONTRAPORT / Answers is our peer-to-peer support site where you can get answers from our community of entrepreneurs, support staff and Certified Consultants. Some Answers provided on importing contacts:

    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.

    Have more questions? Submit a request

    Comments

    Powered by Zendesk