Partners Support Log In
Frank Hagan


    Back to Top

    Importing Contacts Using EU Format CSV

    About EU Format CSV Files

    Applies to: All ONTRAPORT accounts.

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

    Articles in this section

    Created - Updated
    Have more questions? Submit a request