After trying to import event participants for many hours I discovered a confusing bug with Import Field Separator.

During import it is possible to specify which character delimits fields - comma, semicolon, pipe or even tab. In fact semicolon does not work and neither does tab. They just break the data.

It is particularly confusing because MS Excel by default exports semicolon separated files. Which I did not see the need to convert to comma-separated as CiviCRM was seemingly offering an option to specify semicolon as Import Field Separator.

Tested on CiviCRM v4.7.9. on Wordpress. Also does not work in CiviCRM Sandbox.


This is CRM-19192: CSV values must be comma separated

| improve this answer | |

I just created test data from http://generatedata.com (see below) and tested against the master branch of both Drupal and WordPress. I could not replicate this issue.

Excel has inexcusably poor CSV parsing code - presumably because they have little interest in interoperability. I suspect the issue is with the data rather than the import code.

I suggest downloading and installing a copy of LibreOffice, which is free. Open your CSV there, and check to see if the formatting is correct.

If the formatting is correct, save a COPY of the CSV and try importing that to CiviCRM. If that works (and I expect it will), you can open up the two CSVs side-by-side in a regular text editor (e.g. Notepad, Sublime Text). The difference in how Excel and LibreOffice saved the file should hopefully be obvious.

If the formatting is wrong, that's a good indication that it's Excel that's generated bad formatting. In that case, open the Excel file in LibreOffice and save as a CSV from there.

Here's a snippet of the data I was able to successfully import. You can test to see that CiviCRM is working with semi-colon imports by saving this to a file:

Martha;Benson;ante.dictum.mi@vitae.com;(206) 125-2379
Wesley;Christensen;quam.Pellentesque@egestas.org;(329) 985-3800
Darryl;Garrett;felis.ullamcorper@Duis.net;(749) 385-5900
Ariel;Holt;luctus.sit.amet@nislsemconsequat.ca;(110) 493-9459
| improve this answer | |
  • 1
    Just a note - I realize after seeing your other question that this issue is specific to participant import; I tested contact import. – Jon G - Megaphone Tech Aug 8 '16 at 5:41
  • Yes, indeed, I experienced problems with Event Participant import. Messy Excel might not be the issue, because I run the data through Notepad++ which is excellent in showing the exact contents of file. Talking about Contact import, I have run into problems with tab-delimited CSVs (with somewhat richer data). Replacing tab with comma immediately solved the issue. – Aivars Aug 8 '16 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.