CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Import (Moderator: Yashodha Chaku) »
  • Import field custom check boxes [Solved]
Pages: [1]

Author Topic: Import field custom check boxes [Solved]  (Read 3315 times)

osep

  • I’m new here
  • *
  • Posts: 26
  • Karma: 0
  • CiviCRM version: 4.1.5
  • CMS version: Joomla 2.5.6
  • MySQL version: 5.1.62-cll
  • PHP version: 5.3.10
Import field custom check boxes [Solved]
April 30, 2012, 02:55:04 am
Hello,

I try desperately to import a contact.
I have a custom field with check boxes.
I saw in the database that these fields are saved with weird characters to separate each value (which translates into a small square in the CSV)

Every time I do an import, Civi tells me there is an error on these custom fields.
Even if I make an export and import a CSV file with the same, Civi does not want the custom field.
There are fields that radio button yes / no walking to the import ... but it is not very practical!

How?
I searched all forums but found nothing.
I tried everything to CSV: separating the values ​​of my custom field with spaces, commas .. nothing to do.
And if I do only one value for this custom field, the import is done well, but in fact the box is not checked in my contact ... because (I assume) of this odd character that I can not replicate between values.

Thank you for your help ...
« Last Edit: May 07, 2012, 06:21:31 am by osep »

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: Import field custom check boxes
April 30, 2012, 12:12:58 pm
my usual advice is export, then import - but it sounds like you did that.

tell us more about the type of field/group of field etc esp confirm this is not a field that allows 'multiple results' (which it can't be since you have been able to export)

also maybe a screenshot of the options for this field
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

osep

  • I’m new here
  • *
  • Posts: 26
  • Karma: 0
  • CiviCRM version: 4.1.5
  • CMS version: Joomla 2.5.6
  • MySQL version: 5.1.62-cll
  • PHP version: 5.3.10
Re: Import field custom check boxes
May 02, 2012, 02:53:02 am
Thank you for your response.

For fields (CheckBox):
they are not "multiple results"
they are used for all types of contacts

When I do an export, I get a file as shown in the image of the board.

Then if I do an import with the same file (separator ";" - I also tried with separator ","), he recognizes the fields first and last names but not "Domaine pastoral". I select the drop down list (see image) and import tells me it is OK. In fact, the recordings are good but nothing is checked for checkbox "Domaine pastoral" field.

I also put a copy of the database with the strange characters.

Thank you for your help: I really wish I could make use of this field ....

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Import field custom check boxes
May 05, 2012, 05:08:54 pm
Hi osep,

I saw your post a few days ago, and then I ran into the same bug today!  I have a workaround that got my data in - it's not pretty, but it works.

So in the database, for any field that can hold multiple values, they're separated by a special character - by default, that character is a non-printable "control character" called "SOH".  You can read about control characters here (http://en.wikipedia.org/wiki/Control_character) if you're interested.  The Unicode hex value for the SOH character is "0x0001", which is why you're seeing that box with "0 0 0 1" in its four corners.

Civi is pretty smart about converting that separator as needed - for instance, converting it to a comma when you're doing an export.  And when you import, it SHOULD convert your commas into SOH characters.  But it doesn't.  Not only that, but if you try to replace your commas with SOH characters in your CSV file, it will get rejected when you attempt to import.  This is a new bug - I'm reimporting under 4.1.1 some data that I could import fine in 4.0 beta.

I'm unfortunately facing a time crunch, or I'd try to fix the bug in the code; for now, I'll share the same workaround I used.  Import the data normally, then go into your MySQL database and replace the commas with SOH characters. 

Here's a sample SQL statement:
UPDATE `civicrm_value_additional_contact_data_4` SET `field_of_concentration_13` = REPLACE(`field_of_concentration_13`,',',UNHEX('01'));

The generic form of this is:
UPDATE `table_containing_your_custom_field`
SET `custom_field_name` =
REPLACE(`custom_field_name`, ',' , UNHEX('01'));

The magic is in the UNHEX('01') - that resolves to an SOH character.  Once you do this, your data should come up OK.

keywords: CRM_Core_DAO::VALUE_SEPARATOR, ^A, SOH
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Import field custom check boxes
May 05, 2012, 05:13:18 pm
One more thing - you MAY need to remove the space after the commas before (or after) importing.
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

abeverley

  • I’m new here
  • *
  • Posts: 12
  • Karma: 3
  • CiviCRM version: 4.1.2
  • CMS version: Drupal 7
  • MySQL version: 5.1.49 (Debian)
  • PHP version: 5.3.3 (Debian)
Re: Import field custom check boxes
May 07, 2012, 01:10:25 am
This sounds like a bug which is fixed in 4.1.2. Upgrade to that version and try again.

I had the same problem when importing checkbox values, but everything was fine once I'd upgraded to the new version.

Andy

osep

  • I’m new here
  • *
  • Posts: 26
  • Karma: 0
  • CiviCRM version: 4.1.5
  • CMS version: Joomla 2.5.6
  • MySQL version: 5.1.62-cll
  • PHP version: 5.3.10
Re: Import field custom check boxes
May 07, 2012, 01:32:29 am
Thank you for your answers !

I will try to do an upgrade : is it difficult ? How do this ? Should we install everything again ? I did not find zip to upgrade ...

Then I would see if it works. Otherwise I will use the JonGold method

I'll let you know

abeverley

  • I’m new here
  • *
  • Posts: 12
  • Karma: 3
  • CiviCRM version: 4.1.2
  • CMS version: Drupal 7
  • MySQL version: 5.1.49 (Debian)
  • PHP version: 5.3.3 (Debian)
Re: Import field custom check boxes
May 07, 2012, 02:26:01 am
Depends on your current installation, but shouldn't be too hard for a minor upgrade. I suggest you take a look at this page:

http://wiki.civicrm.org/confluence/display/CRMDOC41/Installation+and+Upgrades

In a nutshell, you need to replace the existing files of your installation with the new ones, and then run the upgrade scripts.

Andy

osep

  • I’m new here
  • *
  • Posts: 26
  • Karma: 0
  • CiviCRM version: 4.1.5
  • CMS version: Joomla 2.5.6
  • MySQL version: 5.1.62-cll
  • PHP version: 5.3.10
Re: Import field custom check boxes
May 07, 2012, 06:17:50 am
Super : it works !

For those who have this problem : I advise to go to 4.1.2 and use fields check box used for "all types of contacts."
Data import is done very well and very easily

A very big thank you to those who helped me.

see you soon... for more adventures !

PS : I do not know how close a post ??

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: Import field custom check boxes
May 07, 2012, 06:19:08 am
We don't "close" threads here generally, but you can go to the original post you made, edit it, and add [Solved] to the end of the subject. That will help. :)
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

CiviTeacher.com

  • I live on this forum
  • *****
  • Posts: 1282
  • Karma: 118
    • CiviTeacher
  • CiviCRM version: 3.4 - 4.5
  • CMS version: Drupal 6&7, Wordpress
  • MySQL version: 5.1 - 5.5
  • PHP version: 5.2 - 5.4
Re: Import field custom check boxes [Solved]
June 04, 2014, 11:23:07 am
Posting my findings.  While using PHPMyAdmin to import data directly into civicrm_value_myfield with multiple value custom fields (i.e. checkboxes) I became flummoxed by this special character.

The key is to separate each value in a multiple value field with a rare special character.   Any character could do but  I used: | 

Each value must be bracketed front and back by this special charater.

Example: |red|blue|yellow|maroon|

Then after you do the import run
Code: [Select]
UPDATE `civicrm_value_myfield_22`
SET `my_column_9` =
REPLACE(`my_column_9`, '|' , UNHEX('01'));
Try CiviTeacher: the online video tutorial CiviCRM learning library.
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Import (Moderator: Yashodha Chaku) »
  • Import field custom check boxes [Solved]

This forum was archived on 2017-11-26.