Import field custom check boxes [Solved]

[osep]

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 ...

[petednz]

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

[osep]

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]

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

[JonGold]

One more thing - you MAY need to remove the space after the commas before (or after) importing.

[abeverley]

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]

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]

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]

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]

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.

[CiviTeacher.com]

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'));