How Can We Help?

< Back
You are here:
Print
Table of Contents

Preamble

This procedure is for uploading, into CiviCRM from an Excel workbook, data about organisations and related individuals.

The two Contacts are usually an Organisation and an Individual but nothing stops them from being any two types of Contact. The key thing is that we want to upload a Relationship between the Contacts that has a Description in it (and this functionality is not available within Contacts>Import Contacts).

If the Organisation and the Individual are going to carry a tag (say Stuff) then it looks like we need to put the Stuff tag on both the Individuals and the related Organisations. To find all stuffy organisations, then select (in Contacts > aSearch) the Stuff tag and Contact Type of Organisation. To find all individuals who are contacts at stuffy organisations, then select (in  Contacts > aSearch) the Stuff tag and Contact Type of Individual.

Unless you already know what to do, in detail, then:

Assumptions

  • That you have an Excel workbook (called “Source.xlsx” in the following procedure) with one row per Contact TypeB at a Contact TypeA in a sheet of that workbook (called “Data” in the following procedure). For example, it may be that TypeB is an Individual and TypeA is an organisation so that Contact TypeB at a Contact TypeA is really a person employed by an organisation.
  • That there will be a relationship for every row of data that is (later) imported. If every TypeA were to be linked with one and only one TypeB then the number of rows imported for TypeA, TypeB and the relationship would be the same. Alternatively, if every TypeA had one or more TypeB’s, then the number of rows of TypeB would be the same as the number of rows of relationships, but both would be larger than the number of rows of TypeA’s.
  • That the number of TypeA rows that are imported will be less than or equal to the number of TypeB rows imported. If this is not true, then swap the meaning of TypeA and TypeB so that it is true. The procedure below is written on the basis of this assumption.

Procedure

Rearrange the source fields for importation

  • Munge the data in “Source.xls” so that it has the following columns (if there is any data that looks like these headings in your particular set of data) and, most importantly, that in each Part there are only as many occupied rows as the number of entities that you will later import. For example, if there are two people at the same organisation then the organisation’s information will appear only once (say on the first line) but each individual’s information will appear in full on each respective line. The identity of the organisation to which each belongs will be repeated for each person as that is the way that the importation will tie them together later.
    •  Part 1:
      • The string “<unique name for this importation> <name of data set> TypeA <n>” where n is 1 for the first organisation and 2 for the second, etc, and this will become the External ID (later).
      • Organisation Name (e.g. “ABC Radio National”).
      • Organisation Email.
      • Organisation Website.
      • Organisation Address.
      • Organisation Phone.
      • Organisation Fax.
    • Part 2:
      • The string “<unique name for this importation> <name of data set> TypeA <n>” for the matching contact from Part 1.
      • The string “<unique name for this importation> <name of data set> TypeB <n>” where n is 1 for the first individual and 2 for the second, etc, and this will become the External ID (later).
      • Individual First Name.
      • Individual Last Name.
      • Individual Email.
      • Individual Phone.
      • Individual Mobile.
      • Individual Address (not likely).
      • Individual’s Role at the contact (this will become the Relationship Description later).

Create the .csv file of TypeA for later import (see the sample at the end of this article)

  • Create another sheet in the “Source.xlsx” workbook and call it “TypeA”.
  • Copy and PasteSpecialValues the columns of Part 1 of the “Data” sheet into the “TypeA” sheet ensuring that row 1 has the heading names.
  • Create a new sheet in the “Source.xlsx” workbook.
  • Copy and PasteSpecialValues all of the contents of the “TypeA” sheet into the newly created sheet.
  • Move this sheet into a new workbook.
  • Sort the only sheet of the new workbook so that the headings are still at the top, but there are no blank rows.
  • Save the new workbook as a .csv file with a name containing the word “TypeA” and some indication of the nature of the importation that you are doing.

Create the .csv file of TypeB for later import (see the sample at the end of this article)

  • Create another sheet in the “Source.xlsx” workbook and call it “TypeB”.
  • Copy and PasteSpecialValues the columns of Part 2 of the “Data” sheet into the “TypeB” sheet ensuring that row 1 has the heading names.
  • Create a new sheet in the “Source.xlsx” workbook.
  • Copy and PasteSpecialValues all of the contents of the “TypeB” sheet into the newly created sheet.
  • Move this sheet into a new workbook.
  • Sort the only sheet of the new workbook so that the headings are still at the top, but there are no blank rows.
  • Save the new workbook as a .csv file with a name containing the word “TypeB” and some indication of the nature of the importation that you are doing.

Import in CiviCRM

Preparation

  • Each .csv file will be used in turn. Import any independent entities first. Then import any entity that relied upon a previously imported entity.
  • Run appropriate Reports or Dashlets and note or save the results so that you can use these later to confirm that your upload has been successful (counts of the number of each kind of affected Type of Contact is one useful metric).

Import the first .csv file

  • Select Contacts > ImportContacts.
  • Leave all fields at their defaults except:
    • Select Data Source to be “Comma-Separated Values (CSV)“.
    • Choose file to be the first of your .csv files.
    • Check the box called First row contains column headers.
    • Select Organisation, Household or Individual as appropriate.
    • Select Update.
    • Select Load Saved Field Mapping if appropriate.
    • Select Continue.
    • For each field, select the matching CiviCRM field noting that for some fields there will be two or three selections.
    • Select Continue.
    • Note the number of records that should be imported.
    • Select an appropriate tag in the list shown at Tag imported records.
    • Select Import Now.
    • <wait patiently>
    • Note the number of records imported and not imported.
    • Investigate any errors that show up, fix any data in CiviCRM or in the import files and rerun the import until there are no errors (this is safe to do as the import mode is Update).

Import the second .csv file

  • Select Contacts > ImportContacts.
  • Leave all fields at their defaults except:
    • Select Data Source to be “Comma-Separated Values (CSV)”.
    • Choose file to be the second of your .csv files.
    • Check the box called First row contains column headers.
    • Select Organisation, Household or Individuaas appropriate.
    • Select Update.
    • Select Load Saved Field Mapping if appropriate.
    • Select Continue.
    • For each field, select the matching CiviCRM field noting that for some fields there will be two or three selections (of great importance is the need to select an employee relationship for the linkage to the External ID of the organisation allowing the relationship to be created during the import).
    • Select Continue.
    • Note the number of records that should be imported.
    • Select an appropriate tag in the list shown at Tag imported records
    • Select Import Now.
    • <wait patiently>
    • Note the number of records imported and not imported
    • Investigate any errors that show up, fix any data in CiviCRM or in the import files and rerun the import until there are no errors (this is safe to do as the import mode is Update)

Create the relationships.csv file (see the sample at the end of this article)

  • Obtain a copy of “Relationships Generator.xlsm” from “Master Data⁩ ▸ ⁨OPERATIONS⁩ ▸ ⁨Knowledge Management⁩ ▸ ⁨Knowledge Management Project⁩ ▸ ⁨Development⁩” on M: drive.
  • Select Reports > Contact Reports.
  • Select Employee Relationships including Relationship ID to support imports.
  • Select Refresh Results.
  • Select Actions.
  • Select Export as csv.
  • <wait patiently>.
  • Rename the only sheet in the Excel workbook just created as “Relationships Extracted”.
  • Save the Excel workbook just created as “Relationships Extracted.csv”.
  • Follow the instructions contained in “Relationships Generator.xlsm” to create a “Relationships.csv” file.

Import the Relationships.csv file

  • Select API csv Import.
  • Leave all fields at their defaults except:
    • In Entity To Import select “Relationship”.
    • Choose file to be the third of your .csv files (the relationships.csv file).
    • Check the box called First row contains column headers.
    • Select Continue.
    • For each of the two fields, select the matching CiviCRM field:
      • Relationship ID
      • Relationship Description
    • Select Continue.
    • Note the number of records that should be imported.
    • Select Import Now.
    • <wait patiently>
    • Note the number of records imported and not imported.
    • Investigate any errors that show up, fix any data in CiviCRM or in the import files and rerun the import until there are no errors (this is safe to do as the import mode is Update).

Closure

  • Run appropriate Reports or Dashlets to confirm that your upload has been successful – you should see each organisation with its individuals and for each individual the role that they have at that organisation.

Good luck!

Sample .csv files (for the Journalists Media Lists)

Organisations.csv

External ID Organisation Name Website
Media2018 JOURNALISTS ORG 1 ABC
Media2018 JOURNALISTS ORG 2 ABC News http://www.abc.net.au/news/upload/

Individuals.csv

Organisation External ID Invidual External ID Individual First Name Individual Last Name Email Phone Mobile Role
Media2018 JOURNALISTS ORG 1 Media2018 JOURNALISTS INDIV 1 Tracy Bowden Bowden.Tracy@abc.net.au Journalist
Media2018 JOURNALISTS ORG 1 Media2018 JOURNALISTS INDIV 2 Larissa Romensky Romensky.Larissa@abc.net.au Journalist
Media2018 JOURNALISTS ORG 1 Media2018 JOURNALISTS INDIV 3 Jean Edwards edwards.jean@abc.net.au Journalist
Media2018 JOURNALISTS ORG 1 Media2018 JOURNALISTS INDIV 4 Stephanie Corsetti Corsetti.Stephanie@abc.net.au 03 5440 1731 0425 718 703 Journalist
Media2018 JOURNALISTS ORG 1 Media2018 JOURNALISTS INDIV 5 Philippa Mcdonald McDonal.Philippa@abc.net.au Journalist
Media2018 JOURNALISTS ORG 2 Media2018 JOURNALISTS INDIV 6 Hannah Walmsley Walmsley.Hannah@abc.net.au Journalist
Media2018 JOURNALISTS ORG 2 Media2018 JOURNALISTS INDIV 7 Stephanie Corsetti Corsetti.Stephanie@abc.net.au Journalist
Media2018 JOURNALISTS ORG 2 Media2018 JOURNALISTS INDIV 8 Peta Carlyon Carlyon.Peter@abc.net.au Journalist
Media2018 JOURNALISTS ORG 2 Media2018 JOURNALISTS INDIV 9 Zalika Rizmal rizmal.zalika@abc.net.au 0429 616 144 Journalist

Relationships.csv

Rel ID Role
3588 Journalist
3595 Journalist
3591 Journalist
3594 Journalist
3590 Journalist
3592 Journalist
3596 Journalist
3589 Journalist
3593 Journalist

Useful Excel formulas

While you are munging data, the following Excel formulas may be useful to isolate the components of a field that contains both the first name and the last name (make sure that you understand each function used):

  • Isolate the first name: =TRIM(PROPER(LEFT(B2,FIND(” “,B2,1)-1)))
  • Isolate the last name: =TRIM(PROPER(MID(B2,FIND(” “,B2,1)+1,99)))

Disclaimer

The name of the Knowledge Base article is in no way related to the film with a similar title.