CiviCRM Community Forums (archive)

Hi guys,

I need to be able to import Contacts from a CSV into CiviCRM, but the process should also be able to read Tag-columns and/or Group-columns.

Ideally, we should be able to import the same file as we can export using "all possible fields" for a Contact:

Internal Contact ID;Contact Type;Contact Subtype;Do Not Email;Do Not Phone;Do Not Mail;Do Not Sms;Do Not Trade;No Bulk Emails (User Opt Out);Legal Identifier;External Identifier;Sort Name;Display Name;Nick Name;Legal Name;Image Url;Preferred Communication Method;Preferred Language;Preferred Mail Format;Contact Hash;Source of Contact Data;First Name;Middle Name;Last Name;Job Title;Birth Date;Is Deceased;Deceased Date;Household Name;Organisation Name;Sic Code;Unique ID (OpenID);Current Employer ID;Contact is in Trash;Addressee;Email Greeting;Postal Greeting;Gender;Individual Prefix;Individual Suffix;Current Employer;Location Type;Street Address;Street Number;Street Number Suffix;Street Name;Street Unit;Additional Address 1;Additional Address 2;City;Post Code Suffix;Postal Code;Geo Code 1;Geo Code 2;Address Name;Master Address Belongs To;County;State;Country;Phone;Phone Extension;Email;On Hold;Use for Bulk Mail;Signature Text;Signature Html;IM Provider;IM Screen Name;OpenID;World Region;Website;Group(s);Tag(s);Note(s);Phone Type;Im Service Provider

This way users can Export data, modify it in bulk in their favorite spreadsheet editor, and then re-import the data into CiviCRM.


Currently I'm able to map every possible Contact field using the field-mapper utility, except for Tags and Groups.

These two data-fields should be "imported" or better, selected on the "Preview" step, making it very difficult to set different tags/groups for different rows in the import file.

What I want to know is:

1. How can I add extra mapping fields?

2. What is the behaviour when setting multiple columns to the same mapped field? We want to be able to use for ex. Column #1, #7, #9 as Tags, and Column #4 as Group

3. Should I do this by patching / extending the existing importer, or create a duplicate version in a Drupal module, which can then be contributed back and Civi can decide if/how to incorporate it into Core?

Hi Xavier,

1. When processing the rows in the file (not really, I would use the database table created from the file), I would do the following:

-- Find all Tag/Group columns and aggregate values into a single Tag-Array / Group-Array
-- -- Note: one Tag / Group column can contain multiple items, using a "tag, tag, tag" / "group, group" format

-- Do a array_unique() on these Arrays to ensure unique tags, or a Contact might get a double reference to a Tag / Group

2. When our Arrays are cleaned up and contain only unique Tags / Groups I would loop through these Arrays and:

-- Find their ID in the database to see if Tag / Group exists
-- Find out if the found ID is already linked to the Contact by looking up the email address (or any other "unique" identifier field found in the mapping Array)

3. If Tag / Group not yet linked to our Contact:

-- Use the found ID and add it to our Contact

Each row in our CSV would at least require 3 SELECT queries (tag exists? contact exists? tag is linked to contact?) , which can trigger a performance issue on large imports.

A plausible solution to this issue might be to use the Drupal Batch API, which allows for large actions to be chopped up in bits.

This solution would then make this feature Drupal-dependant for large files so it's far from optimal.

I would like to have some feedback on the performance question

Greets,
Kim

Indeed xavier, it would be wise to preload ALL groups / tags into an Array with key = ID, value = Text to match.

I appreciate your opinion adixon and share the same scepticism about Excel to CSV files, based on previous experiences with localisation, but I believe I can warrant this modification for the following reasons:

1. Currently one is able to search + export Contacts as a CSV file, using a mapping feature to specify the columns to export.

The primary fields in these exports already contain a Comma separated list of Tags and Groups associated with the Contact.


2. Currently one is able to import / update Contacts from a CSV (or SQL) file, using a mapping feature to specify which column represents which Contact field.

This feature lacks the possibility to map a column to Tags / Groups.


3. As you can export Contacts, one would believe it is quite easy to import that same file back into CiviCRM with the changed data. It is however NOT in a multilingual environment because of certain enum-type values being translated.


4. Some of our users aren't able to change data in the CRM and use the exported CSV files to update certain information, which is then sent back. It would require a substantial manual effort to preprocess those same CSV files before importing them.

Finally: Simply being able to export and import the same (but modified) file, would benefit a lot of users.

This feature is requested on a semi-regular basis, and kind of make sense.

I think a simple option (it only does something if the tag/group exists already, never creates it) would work.  Migration and the api works, but that's probably too close to the machine for "normal" usage, only for migrations.

If someone is willing to invest and contribute back, think it would be an improvement

X+

For adding Tag or Group designations to Contacts, one can import Contacts and choose in the last step before running the Import to assign Tags or Groups to them. I often need to Tag a bunch of Contacts, by ID number, and so I realized I can upload a CSV of just ID numbers, and then choose a Tag. (I wonder if there is an easier way to do this via an SQL command?)

Yes, this works - for tags as well as for groups.

But, be careful: If you are doing an import using duplicate rules, only the newly inserted contacts will be tagged resp. put into the group!!

As a workaround, I prefer to import the group name (or tag name) into a custom field, and in a second step, I search for that custom field content, and add the contacts to the group via the "Actions" menu.