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
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,firstname.lastname@example.org 42,"Compasspoint ",Organization,0 47,"Social Source Foundation ",Organization,0,32,email@example.com 52,"Compasspoint ",Organization,0 71,"Cisco Systems ",Organization,0,40,firstname.lastname@example.org 73,"Conservation Corp ",Organization,0,44,email@example.com 75,"Conservation Corp ",Organization,0,38,firstname.lastname@example.org 93,"Project Open Hand ",Organization,0,42,email@example.com 95,"Eastmont Computing Center ",Organization,0,36,firstname.lastname@example.org 101,"Conservation Corp ",Organization,0,34,email@example.com
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...
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!
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 ?
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:
* 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
* php bin/csv/export.php -e <entity> [ --field=value --field=value ] [ -s site.org ]
* e.g.: php bin/csv/export.php -e Contact --firstname.lastname@example.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):
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
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.
I'm using an older version of CiviCRM (4.6) with Drupal 7 on Linux and until now I've not really used the command line stuff for CiviCRM, only for Drupal.
But I now need to do exports & imports of contacts etc to & from CiviCRM and so I've installed Drush & got it working. I've tried doing an export with the export.php script with Drush and can manipulate the search results to some degree. But for some reason if only exports the first 26 rows ... is the a parameter that I should be using to get the full rowcount?
Also, where is the best place to get a more detailed list of examples, list of possible parameters etc?