CiviCRM Community Forums (archive)

I need to import a few hundred individuals, a few hundred companies and at least a hundred 'families' from our QuickBooks files (essentially everyone whose ever given us money in the last year or two).  I've looked at the Book (http://book.civicrm.org/user/current/common-workflows/importing-data/) & the other documentation (http://wiki.civicrm.org/confluence/display/CRMDOC/Preparing+Data+for+Import) and still have questions (lots of them, sorry!).

I've exported the IIF file and brought it into excel to clean it up and prepare for import.  The formatting was horrible with no specific area for street name, city, state or zip (just address line 1, 2 etc)  I've taken the time to create all those fields in excel to make the import easier.  I've now realized that when it comes to the details, I'm missing a lot of understanding on the best process, especially as it comes to keeping relationships between people, households and companies.

• For instance, I have several who were listed as John & Jane Doe - I've separated these out as Jane Doe & John Doe on the same line, but do they instead need to be listed on separate lines with the same household?
   
• The household implementation is a feature that I would like to use (I think), and I understand that I need to import the households separately from the individuals.  I'm unaware, however, how the individuals will become associated with the household post import - Do I tag each individual in excel with a household external identifier, or do I tag households with the external ID of the individual?  Or if the address on both match and they will automatically be linked?
   
• With companies, many (many!) of the individuals are associated with companies as well - how can these employee/employer relationships created via import of the data? - Matching ID's again or something else.
   
• I found in that second link the list of the fields that CC can populate, however, I'd be interested in knowing the method used to import some of those non-text based fields - For instance, 'Is Deceased,' - Should this be a 1, a Y, any character, or some other code?  How would I figure this out?  What about multiple choice fields (contact subtype, etc)?
   
• For now, my focus is on the people/families/companies who have contributed.  Soon, however, I would like to attempt to import the activities themselves into the database as well.  Anything I need to ensure I do now to make sure this is possible? (Dare I say, easy?)

A very useful tool would be an excel spreadsheet that has these configurations/options already created/demonstrated - this would be easy to create with stock info.  Amazing would be a tool in CiviCRM that could create such a beast on the fly with the custom fields an organization had already set-up.

Anyway, I'd appreciate any help anyone can provide!

Thanks!

Great - I assumed it would be something like that, but the info on the wiki/book didn't specify.  I tested some sample data on the demo server and was fairly happy with the results with a basic import.  In this case, I imported businesses and included the employee first and last name - this worked well and I could see how this will work when I'd use the external ID of the employee instead of the name (and thus linking to any other information about them, like home address).

One thing I noticed was that it didn't populate the individual with a work address tied to the business.  Of course, I could import this information again as a work address, but I'd rather it be linked to the business address as the master address directly (therefore just one place to make changes) - how would this be done?

Separate lines -each line needs the household name, then you can connect each to their household. if you also want to connect Jane to John then need another column. Usually best to import all the individuals to create them, then use another import that can just have 2 cols Ext ID PersonA and Ext ID Person B - and use that to create the relationship

As per my above re Jane and John, you need a line for each 'relationship'. Again in my view better to do the import just to create all the Indivs and Orgs and Households.

Then do a second/third/fourth import with just the Ext ID Contact A and Ext ID Contact B to create the relationships (civi import wizard lets you create these relationships - note the options are way down the bottom of the field selector)

I believe you can import 'activities' based on External ID - but at some point a major import of legacy data becomes easier through using 'civi migrate'

Hope some of the above helps.

Yes - So I tried to import with file I had created which included household name Head of household name and household members names all on the same line.

Once mapped, this worked rather well - Households were created as were individuals in that household with the links between them.  Unfortunately, the household address/home phone were not automatically provided to the residents, but this may not be possible. 

I was able to export the primary fields for these three, however, I wanted to see how the relationship fields were created so I attempted to export one household created that had two people in it with the relationships formed.  I recieved this error:

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constitue' at line 2, 1064
Additional Details:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT contact_a.id as contact_id, contact_a.sort_name  as `sort_name`, contact_a.first_name  as `first_name`, contact_a.last_name  as `last_name`, contact_a.gender_id  as `gender_id`, contact_a.household_name  as `household_name`, civicrm_address.id as address_id, civicrm_address.master_id as `master_id`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.master_id as `Home-master_id`, civicrm_value_constituent_information_1.id as civicrm_value_constituent_information_1_id, civicrm_value_constituent_information_1.marital_status_2 as custom_2  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) 
LEFT JOIN civicrm_location_type `Home-location_type` ON (  ) 
LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constituent_information_1.entity_id = `contact_a`.id  INNER JOIN civicrm_task_action_temp_9b5bd20822c4febbc27b2424a5402f29_1549 ctTable ON ctTable.contact_id = contact_a.id  WHERE  (  (  (  ( contact_a.sort_name LIKE '%bork%' ) OR  ( contact_a.nick_name LIKE '%bork%' ) OR  ( civicrm_email.email LIKE '%bork%' )  )  )  )  AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1  GROUP BY contact_a.id LIMIT 0, 10000 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') 
LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constitue' at line 2]
    [type] => DB_Error
    [user_info] => SELECT contact_a.id as contact_id, contact_a.sort_name  as `sort_name`, contact_a.first_name  as `first_name`, contact_a.last_name  as `last_name`, contact_a.gender_id  as `gender_id`, contact_a.household_name  as `household_name`, civicrm_address.id as address_id, civicrm_address.master_id as `master_id`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.master_id as `Home-master_id`, civicrm_value_constituent_information_1.id as civicrm_value_constituent_information_1_id, civicrm_value_constituent_information_1.marital_status_2 as custom_2  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) 
LEFT JOIN civicrm_location_type `Home-location_type` ON (  ) 
LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constituent_information_1.entity_id = `contact_a`.id  INNER JOIN civicrm_task_action_temp_9b5bd20822c4febbc27b2424a5402f29_1549 ctTable ON ctTable.contact_id = contact_a.id  WHERE  (  (  (  ( contact_a.sort_name LIKE '%bork%' ) OR  ( contact_a.nick_name LIKE '%bork%' ) OR  ( civicrm_email.email LIKE '%bork%' )  )  )  )  AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1  GROUP BY contact_a.id LIMIT 0, 10000 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') 
LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constitue' at line 2]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT contact_a.id as contact_id, contact_a.sort_name  as `sort_name`, contact_a.first_name  as `first_name`, contact_a.last_name  as `last_name`, contact_a.gender_id  as `gender_id`, contact_a.household_name  as `household_name`, civicrm_address.id as address_id, civicrm_address.master_id as `master_id`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.master_id as `Home-master_id`, civicrm_value_constituent_information_1.id as civicrm_value_constituent_information_1_id, civicrm_value_constituent_information_1.marital_status_2 as custom_2  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) 
LEFT JOIN civicrm_location_type `Home-location_type` ON (  ) 
LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constituent_information_1.entity_id = `contact_a`.id  INNER JOIN civicrm_task_action_temp_9b5bd20822c4febbc27b2424a5402f29_1549 ctTable ON ctTable.contact_id = contact_a.id  WHERE  (  (  (  ( contact_a.sort_name LIKE '%bork%' ) OR  ( contact_a.nick_name LIKE '%bork%' ) OR  ( civicrm_email.email LIKE '%bork%' )  )  )  )  AND (contact_a.is_deleted = 0) AND contact_a.is_deleted != 1  GROUP BY contact_a.id LIMIT 0, 10000 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') 
LEFT JOIN civicrm_value_constituent_information_1 ON civicrm_value_constitue' at line 2]"]
)


maybe worth trying a sample on demo - and pasting same sample on this ticket (anonymised data of course) if the errors are repeatable on demo

For posterity, I'll leave my boneheaded mistake here. 

In tinkering with my webhost settings, I turned on the ssl capability of my provider after I had installed 4.5.2. In my case, my webhost permits ssl though a bulk domain that all shared hosts would point their https traffic to. 

In my frustration, i wiped my database and dropped the tables & wiped the 4.5.2 version from the server and installed 4.5.3.  I had difficulty in getting the installation to complete as it kept giving an error that the server required an authentication the client didn't understand.  I finally figured out that the mysql database url used the same domain as the ssl url, and civicrm didn't have the proper authentication for secure communication and was unable to make this update with the server. 

I turned off the SSL feature and have a new install of 4.5.3 and everything works again!

Why many or the other things kept working is beyond me, but I hope this is the cause of all the errors i've been struggling with!

afaik there is no way yet to specify as part of an import that Contact B should share/inherit the address of Contact A. Would be a good feature to get added some time

For anyone who finds this useful in the future, this does work in 4.5+ (perhaps earlier too?)  I'll update the book if possible to include some notes on using 'Master address belongs to' via imports.  This is not mentioned in any documentation in the book or wiki that i could find and is not hard to use if you know how.  (Edit - Wiki & Book have now been update)

hey ben - i think if you can replicate this on the WP Demo site then you do probably need to report as a bug