Company Blog Support
Frank Hagan
posted

Contents

    Back to Top

    Note: Not all images and content apply to ONTRAPORT 5.
    For backwards compatibility, some articles cover legacy features.
    Please Contact Us if you run into any problems.

    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.

      Open CSV using Text option

    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.

      Page one of the Text Import Wizard

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

      Page two of the Text Import Wizard

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

      Page three of the Text Import Wizard

    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.

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

      set the field delimiter


    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:

     

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

    Have more questions? Submit a request

    Comments

    Powered by Zendesk