Importing Contacts Using EU Format CSV

About EU Format CSV Files

This article applies only to countries where the CSV format uses a semicolon instead of a comma to separate field values. This is most common in Europe and Northern Africa.

In the EU, CSV files use the semicolon to separate the field values. This avoids conflicts with the comma used as the decimal separator. Excel sold in the EU is hard coded to use the semicolon for any CSV file. Changing settings will not allow Excel to save the file in the format that ONTRAPORT requires (comma separated values).

Converting EU Format for ONTRAPORT

We recommend using an alternative spreadsheet program such as Libre Office or Open Office (both are free). The following instructions show how to convert the file using LibreOffice.

1. Open LibreOffice first, then use File > Open to locate the file and open it. Select Text CSV (*.csv) as the file type to open.

   

2. The Text Import wizard will open. On the first page, select Unicode (UTF-8) to preserve the formatting of accented characters (1), then select Separated by and Semicolon (2 and 3). The data preview should show the first few rows of your spreadsheet correctly. Click OK to open the file in LibreOffice.

   

3. Select any columns with formatted numbers using periods for the thousands separator and commas for the decimal separator (1).
   
   Use Edit > Find and Replace and replace all periods in the column (2) with nothing (leave the "Replace with" box in number 3 completely blank). Ensure you select Current Selection Only (4), set the wizard to Search in Values (5) and set the Search direction to Columns (6).

   

4. After you have replaced all the periods used as thousands separators, replace all the commas used as decimal separators in this same column.
   
   Select the column as before (1), select Edit > Find and Replace and replace all the commas (2) with periods (3). Ensure you select Current Selection Only (4), set the wizard to Search in Values (5) and set the Search direction to Columns (6).

   

5. Repeat steps 3 and 4 for each column with periods as the thousands separator and commas as the decimal separator. 
   
   
6. Check other data that may use commas such as telephone numbers, long / lat numbers, etc. Because the comma is used as a field separator during import, you want to ensure none of the fields (shown in "cells" in LibreOffice) have a comma in them. 

7. Review and clean up your data before saving by removing empty rows and columns you do not want to import. You can add custom fields during the import process but now is the time to consider not importing data you do not need. The default fields in ONTRAPORT are:

   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

    

8. When satisfied, use File > Save As and select the Edit Filter Settings check box.

   

9. When the Export Text File dialog box opens, select Unicode (UTF-8) to preserve accented characters and set the Field delimiter to the comma. Make sure the Save cell content as shown check box is checked. Click OK. The file will now save with a comma delimited format suitable for importing into ONTRAPORT.

   

Note: To confirm the data is formatted correctly, do not open the file in Excel, as it will reformat and auto-save the file with semi-colons as separators, causing you to question Excel's parentage out loud. You can use a freeware text editor such as Notepad++ (recommended), Notepad on PC or TextEdit on Mac to verify the fields are separated by commas.

Other Resources

Knowledge Base articles:

• Importing Contacts: Splitting and Combining Field Values
• Importing Contacts: Capitalizing Proper Names
• Importing Contacts: Import Failures - Cleaning Up Your CSV File

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:

• Import Failed: I have semi-colons instead of commas!
• How Do I Import Contacts?
• Wrong Information in Contact Fields
• All Questions About 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. After you have a valid CSV file to upload, log into ONTRAPORT in another tab and click Importing Contacts to start the Project. It will walk you through the rest of the process!

Articles in this section

• Contact Management Overview
• Importing Contacts
• Best Practices for Preparing a CSV File for Import
• Contact Management Segmentation (Groups)
• Contact Tags
• Add Custom Fields & Sections
• Formatting Merge Fields
• Merge Duplicate Contacts
• Managing Contact Transactions
• Managing Contact Credit Cards