Published
Friday, April 15, 2011 - 04:22
Written by

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+

Comments

Nice one, tech to the people!

somebody is owed much warm beer :)

 

 

this is AWESOME.

Kyle can get the first round, I'll buy the rest!

Very cool. 

By integrating with Drush you could skip the u/p requirements and be able to a lot of other cool things. 

It would apply as well to the other bin scripts (eg. to send emails/process bounces...). Could you try patching bin/cli.php so it's drush aware ?

 

X+

I've tried it on 3.4.6 and 3.4.7, and I can't get either the import or export scripts to work (I haven't tried the delete script.)

For import, I don't get any errors, but nothing is put into the DB.

For export, I don't get any errors, the file is created, but it is empty.

We've done a lot of refactoring with 4.2 that changes the behavior of the import/export scripts (and fixes some bugs that prevented it from working in earlier releases).

 

It's now better integrated with the re-designed cli.php.

 

Here are the new usage directions for 4.2:

 

* Usage:
 * php bin/csv/import.php -e <entity> --file /path/to/csv/file [ -s site.org ]
 * e.g.: php bin/csv/import.php -e Contact --file /tmp/import.csv

 

* Usage:
 * php bin/csv/export.php -e <entity> [ --field=value --field=value ] [ -s site.org ]
 * e.g.: php bin/csv/export.php -e Contact --email=jamie@progressivetech.org -s mygroup.org

The export doesn't export all the fields in Version 4.2.2

With a standard install I first exported the Custom Group Table:

php export.php -e CustomField Tag > custom_field.csv

I then re-imported it:

php import.php -e CustomGroup --file C:\Exports\custom_group.csv

When checking the before / after copies of the Custom Group Table (and the export file) I noticed that the following fields had not been included in the export (and hence, the values in these fields could not be re-imported, and so were lost):

extends_entity_column_id

extends_entity_column_value

 

 

With 4.2, there's a change in how parameters are passed using these tools.  You need to add columns to the CSV itself.  The parameter name goes in the header, and the value in the record.

Here's a snippet I used to remove all the contacts from a database while keeping the settings, custom fields, etc. intact.  it adds a column called "skip_undelete" with a vaue of "1" for each record:


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

Simon:

I ran into that issue.  This is the potential cause: http://bit.ly/1oo2u5Q . Not sure if this export method is still supported so be careful.