New tools to automate the configuration and staging/production migration

Have you ever clicked a few zillion time on the admin interface to create a lot of groups, tags, values for a custom field... ?

Have you ever screamed having to do the same tedious tasks you did on the staging one more time on the production site?

Have you cursed the import wizard after having clicked again and again on the next button ?

Friends of the shell, rejoice, the API is there to the rescue and tech to the people has contributed a few simple tools. As of civicrm 3.4, we have introduced 3 scripts: bin/csv/export.php bin/csv/import.php and bin/csv/delete.php

They allow to import (create or update), export and delete any entity exposed by the API (almost everything).

The syntax is simple: the first line is the name of the fields all the others are entities. you can find the name of these fields using the getfields action on any entity from the API explorer

Say that you want to create several tags. create a file /tmp/tag.csv

name,description
Tag A, description A
Tag B, description B
...


cd your/civicrm/root
php bin/csv/import.php -uyourlogin -pyourpass -syoursite Tag /tmp/tag.csv
line 2: created Tag id= 26
line 3: created Tag id= 27

...

You can then export them all

php bin/csv/export.php -uyourlogin -pyourpass -syoursite Tag > /tmp/tagall.csv

or filter the exports, eg only the Tag A (filtering by name)

php bin/csv/export.php -uyourlogin -pyourpass -syoursite 'name=Tag A' > /tmp/tagA.csv

equivalent of filtering by id

php bin/csv/export.php -uyourlogin -pyourpass -syoursite id=26

and delete in batch too

php bin/csv/delete.php -uyourlogin -pyourpass -syoursite taga.csv

We have been using it for a while. Beside offering a faster tool to create or edit a lot of entities, that has been proven very convenient to migrate data between environments. And we are using it with git as a way of backuping and keeping an history of some key configuration elements and being able to restore/apply them on a more granuar basis than sql dumps.

We also have found it very convenient to configure civicase to create the needed relationships and activity types.

eg. to export all the activity types (and by importing OptionValue with the column option_group_id = 2 you will create/update them)

php bin/csv/export.php -uyourlogin -pyourpass -syoursite  OptionValue option_group_id=2

It works as well for the values of custom fields that are a list (select/radio...)

To make easier to generate an empty csv with the right column names, add a criteria id=0, eg. to prepare the file to create tags:

php bin/csv/export.php -uyourlogin -pyourpass -syoursite  Tag id=0
id,name,description,parent_id,is_selectable,is_reserved,is_tagset,used_for,created_id,created_date

In that example, you can delete most of the columns beside the name if you don't need them, the API is trying to have sensible defaults (eg a tag will be for contacts).

A last tip, you can also use it to export/import contacts, for instance to export the name and email of the organisations in the group 42

php bin/csv/export.php -uyourlogin -pyourpass -syoursite Contact contact_type=Organization return=sort_name,email

1,"Inner City Arts",Organization,0,95,cityart@example.com
42,"Compasspoint  ",Organization,0
47,"Social Source Foundation  ",Organization,0,32,socialsourcefoundation@yahoo.co.in
52,"Compasspoint  ",Organization,0
71,"Cisco Systems  ",Organization,0,40,ciscosystems@aol.co.pl
73,"Conservation Corp  ",Organization,0,44,conservationcorp@yahoo.net
75,"Conservation Corp  ",Organization,0,38,conservationcorp@indiatimes.edu.in
93,"Project Open Hand  ",Organization,0,42,projectopenhand@tsec.net
95,"Eastmont Computing Center  ",Organization,0,36,eastmontcomputingcenter@brown.net.in
101,"Conservation Corp  ",Organization,0,34,conservationcorp@yahoo.edu

Next time you have batch actions, try using these API tools, you'll love it, and who knows, you might even find a new nice feature one of the api is missing and contribute back?

Why is that a shell tool? Isn't there a web interface too I can almost hear you asking?

Because the cli is a much more convenient tool to handle these batch tasks and deal much better with files that are a lot bigger (eg if you start exporting/importing loads of contacts). It's also made to be scripted, so for instance sending you by email weekly the organisations above is a trivial oneliner. Really, you should have a server that offers you a ssh access and spend the time to learn bash, at least for the basic stuff.

This being said, if enough of you are interested of having a web interface too, you can make it happen...

X+

drush civicrm-sql-query "SELECT id from civicrm_contact WHERE id > 1;" > test.csv
sed -i '1 s/.*/&,skip_undelete/' test.csv
sed -i '2,$ s/.*/&,1/' test.csv
php /var/www/<mysite>/sites/all/modules/contrib/civicrm/bin/csv/delete.php -e Contact -u civi.admin -p <mypass> --file=<mypath>/test.csv