1 follower Follow

My import failed! I have semi-colons instead of commas in my CSV

Frank Hagan

I'm in Europe, and our CSV files here use the semi-colon instead of the comma to separate field values. I can't get Excel to format it with commas. How can I convert this file to the format ONTRAPORT requires?


Official comment


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

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.

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


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

image description

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

image description

After you have replaced all the periods used as thousands separators, replace all the commas used as decimal separators. Select the column as before, select Edit > Find and Replace and replace all the commas with periods. Ensure you select Current Selection Only, set the wizard to Search in Values and set the Search direction to Columns as you did above.


Review and clean up your data before import. When satisfied, use File > Save As and select the Edit Filter Settings check box.


When the Export Text File dialog box opens, select Unicode (UTF-8) to preserve accented characters and set the Field delimiter to the comma. The file will now save with a comma delimited format suitable for uploading to ONTRAPORT.


Frank Hagan

Please sign in to leave a comment.

1 comment