CiviCRM Community Forums (archive)

We are transitioning from using Constant Contact for our mailings and this has exposed one of the more annoying oversights in the design of CiviCRM. The way I import a mailing list is to create a text file of email addresses, then import contacts set to fill duplicates and assign to a new group.

It is critical to check off the right boxes here and not include additional fields; otherwise you will create a mess of duplicate records that must be cleaned. I've also learned from experience not to include extra fields because special characters like non-English text e-accent/e-grave/etc. will crash the import with server errors. Even simplified like this, I often get cryptic non-recoverable system errors that require breaking the input file up into chunks and doing a trial and error import. So this is not a task that can be delegated to users; it has to be done by the administrator.

Hi,

Import is a pain and civi could handle stuff better, but IMO, most of the issue is in the source itself (eg. one single column containing both first and last name, or name of organisation, or...) badly encoded emails...

For your encoding, your problem is that somehow the file isn't unicode utf8 encoded, if you have it the right format, the encoding will work fine.

They are a few things that would improve the process:
- being able to automatically set a source (in the source field)
- handling errors as warning (eg if the country you import doesn't exist, create the record anyway without the country instead of skipping it completely)
- making easier to rollback. That one is tricky to implement probably
- making errors easier to process, the csv to download would be better handled as a html table, ideally with an option to automatically correct and import line by line
- being able to automatically put some values on the related contact, eg adding the website on the current employer rather than the user, or create the address on the household and share it with the contact you import
- being able to import a column with several tags or groups

But at the end of the day, as long as I have a few k contacts to import, I tend to write import code, that handle better missing values and custom matching rules...
X+

if you include the Internal ID in your export - then do your culling - then import a file that only has the (remaining) Internal IDs (ie the csv has one column only for the Internal ID) - and add those contacts to a group - where do you hit problems?

Just another idea that might help:

The problem: You want to add a number of e-mail addresses stored in a csv file, to a certain civicrm group (e.g. Group-ID=42), without using any sql stuff which is not available for a normal user.

The solution:
Step 1: Add a custom field named "Temporary-GroupID", and define it as searchable.
Step 2: Edit the csv file, add a column which is all filled with the value of "42" (same as your group-ID!)
Step 3: Import the csv file (only import e-mail address and the column with the group-ID, use a dedupe rule with e-mail only).
Step 4: Within extended search, look up all contacts with Temporary-GroupID=42
Step 5: Action: Add all matched contacts to your Group. Voilà!

Greetings, Detlev

Hi,

One problem is that csv is a standard format, but then you have many formats named csv that have different separator (semi column or tab) and different charset based on which version of excel you use (fun fact, Microsoft has decided that the comma in the us version of excel for csv would be more suitably replaced by a semi-column in the french version because whatever).

I have moved away from that for the normal export, for the import, not sure how long it would take to create a new parser (like csv or sql today)

But in general, complex imports have complex rules (if the first name is empty then it means it's an organisation, use the address both for the individual and her employer, if the country is missing but not the street name put US by default unless the phone number starts with a +49 in that case it's germany...)

That and the ability to re-run the script without having to jump through all the wizard steps of the UI makes it a better option to go custom import code IMO. Checkout the work of eileen using the migrate module if you are using drupal, seems an interesting approach

http://fuzion.co.nz/content/migrate-2-civicrm-api-3-civimigrate

Hey sounds like a great solution. Wanna come to civicon london and present it?  (that's not that far)

X+

Then come to the sprint as well. See already more worthwhile