Articles in this section

See more

Import Utility: Importing Individual Contacts Part II - Prepare Your Data

Avatar
Terri Bruce
Updated
Follow

Import Utility: Importing Individual Contacts Part II - Prepare Your Data

  1. The first step in importing data into the CRM is to prepare your data in a spreadsheet.
  2. This data could have been exported from another database system, exported from NonProfitEasy (to be updated and then re-imported), or could have been hand-entered into a spreadsheet. You could have only a handful of names/rows or you could have thousands.
  3. When preparing your spreadsheet, you can use our Individual Contact template or you can use your own spreadsheet - it doesn't matter which. If you use your own spreadsheet/don't transfer your data to our template, be sure to use our template as a guide for setting up your data. Our template not only has the appropriate column names and identifies the fields that can be imported, it has a row of instructions that details what data is required and what format certain fields needs to be in.
  4. For data sets larger than 2,000 rows, we recommend splitting the data into multiple batches to speed processing.
  5. Setting up your data requires high attention to detail. Small mistakes in the data file can create BIG problems in your CRM data - it can cause the import to fail, to over-write existing data in your system incorrectly, scramble your data, or create a lot of hand clean up. It is imperative you review every cell in your spreadsheet for accuracy. Below are details of the most common errors we see in data files, but this list is by no means exhaustive.
  6. SUPER IMPORTANT REMINDER: if you sort your spreadsheet at any point, be absolutely certain that you have grabbed all the columns and rows with data. True example of what can happen if you are not careful: client exported all data from their CRM, resorted the spreadsheet but did not include all the email address column. The email addresses became orphaned from the correct record (for example, Sally now had John's email address in her row). The data was then re-imported, using email address as part of the duplicate prevention settings for the import. Because the contacts now had a different e-mail address in the spreadsheet than what was in the CRM, the new records were not seen as matching existing records and a new record was created for every person. This resulted in over 10,000 duplicate records. In a few instances where the contact had no e-mail in the CRM and no e-mail in the spreadsheet (but same first and last name), those were seen as the same person and the import updated the existing record. However, the data in the field didn't belong to that person (due to sorting the spreadsheet and not including all the columns) so that the wrong mailing address was added to the existing record. We cannot stress enough this kind of mistake is easy to do if you do not pay very careful attention when sorting your data file. When it comes to importing, check and double check your data file before proceeding!
  7. When using the import utility to do a bulk update of data in NonProfitEasy, re-import the BARE MINIMUM data necessary for the update. Don't re-import data you aren't updating/changing (or you risk updating something you didn't mean to).
  8. If using the import utility to do a bulk update of data in the CRM, we strongly recommend using the NonProfitEasy ID field or the External Contact ID as the duplicate prevention setting (so be sure to include this in your original export from the CRM and keep it on the data file for re-import), rather than name, e-mail, and/or address.
  9. When setting up your spreadsheet:
    • Only .csv format is accepted.
    • Your file MUST have a header row (with column names).
    • Column headers can be called anything you want - they don't have to match the column headers in our template. However, the column headers in our spreadsheet exactly match the category and name of the field in the CRM that you will be mapping the data to. If you don't use our column names, be sure to use column names that easily identify what data is in each column.
    • Column headers must be unique (you cannot have two columns labeled "Phone Number" for example. You would need to make them slightly different, such as "Phone Number 1" and "Phone Number 2."
    • There can be NO blank rows between the first row with data and the last row with data in your spreadsheet. Any blank rows will cause the import to fail.
    • There can be NO returns (new line) in any cell. That is, in no cell can the data contain a hard or soft return (created by using alt + enter) or be on separate lines.
    • Do not create stray marks, entries, or formatting outside the rows or columns with data. For instance, highlighting an entire row can cause the system to read data (the code for the highlighting) in the blank/empty cells (even if you later remove the highlighting), which will cause an error when attempting to upload or import the file (see Import Utility: Importing Individual Contacts Part IV - File Upload for more information).
    • You can delete any columns without data (empty columns). This will help with the field mapping screen (less columns to wade through to get to the ones with data).
    • EVERY row that has data MUST have all of the required fields. For individual contact import, for example, every row MUST have a first and last name (these are the only two fields that are absolutely required for the import). In our screen shot below, row 3 and row 7 are missing Last Name. This will cause the import to fail.

      Screenshot_2018-02-12_13.37.22.png

    • You can only have ONE name/person per row. The First Name and Last Name field should contain only ONE name. In the screen shot below, Row 8 is set up incorrectly - there are two names in the First Name field. Those names need to be separated into two records. NOTE: for existing contacts, salutation set in import will override the default salutation style. 

      Screenshot_2018-02-12_13.37.47.png

    • To indicate that two contacts are Spouse/Partner, enter the first person's first and last name in the First Name and Last Name fields. Enter the second person's name in the Spouse First Name and Last Name fields (or the Partner First Name and Partner Last Name fields). Spouse/Partners are assumed to live in the same household (the same household check box/yes-no field will be set to yes automatically; there is no way to import ex-spouse/ex-partner or spouse/partners that do not live at the same address/in the same household). Any address entered as "Mailing Address" in the data file/imported will be added to both contacts' records as the home address (see the discussion about the address fields below).
    • If you include the spouse /partner's email address in your file but not given a type, it will default to personal email for the spouse/partner's contact record.

      Screenshot_2019-03-18_17.17.46.png

      Screenshot_2019-03-18_17.18.49.png

    • If you want to import data on the spouse/partner such as their birth date, ethnicity, tags, special comments, etc. then they should be entered into their own row on the spreadsheet in addition to including them in the spouse/partner name fields on their spouse's/partner's row. For instance, in this example, Marion is entered in the First Name and Last Name fields and Howard is entered in the spouse/partner first and last name fields on the same row as her. Howard is then ALSO entered on his own line/row as the main contact and his details are added in the appropriate fields (only enter the spouse/partner on their own row if you are importing additional data about that person beyond their name and relationship. If you have no other data to import about that person other than their name and relationship to the primary contact, then don't include them on their own line. Just include them in the spouse/partner name fields of the other contact).

      Screenshot_2019-03-18_17.25.47.png

    • If including e-mail address(es) for import:
      • You can import multiple email addresses per person.
      • You can import an email address without knowing if it is home or work (you do not have to set the "e-mail address type" field). The columns on our template are labeled work and personal, but that is just a convenience.

        As you can see from the screenshots below, you can assign any column with e-mail addresses in your spreadsheet the home or work type.

        Screenshot_2019-03-20_10.01.40.png

      • If you do not need to assign e-mail type (for instance, if you just have one email for each person and you don't know if it's a work or home e-mail address, then you can put all the emails in one column and not fill out the type field in the field mapping (Tab #3 of the Import Utility).
      • Whether or not the e-mail address being imported is work e-mail or home/personal e-mail is set per batch (not per contact) per column. That is, all e-mail addresses in that column are assigned the same type.
      • You can assign only ONE column of e-mail addresses as "primary". Setting the column to primary in the field mapping means that all emails in that column in your spreadsheet will be marked as the primary e-mail after import (this will over-ride the primary e-mail setting inside the system for any existing contacts. That is, if Sally Smith is already in the CRM with an e-mail of "sallysmith@gmail.com" marked as her primary email, and you import a record for her with an email of "smith.sally@example.com" and indicate that the e-mail in the import is the primary, after import the e-mail that will be marked as primary is the one that was imported (smith.sally@example.com).

        Screenshot_2019-03-20_10.01.43.png

      • There is no way to import shared/household e-mail addresses.
      • If you include an email address but do not include a value in the email communication preference column, then the value will default to "Not Set" for new contacts (contacts being created by this import) (contacts already in your system will keep their existing email communication preference setting). If you include an email address and DO want the contact's communication preference for email set to yes, be sure to include the email communication preference column for import and set the value to True or Yes.
      • For new contacts (that you are creating via the import), if you include an email address and set the communication preference for email to Yes, they will be auto-subscribed to any newsletter categories/lists you have set/configured as "auto-subscribe" categories. Otherwise, you will need to subscribe the contacts to your newsletter lists manually.
      • For existing contacts (that are already in your CRM and which you are updating via the import), they are auto-subscribed to newsletter categories when the communication preference is first set (and set to yes). Which means, if the contacts are already in your CRM with communication preference set to Yes or No, but with no email address on record, then adding an email via the import utility will not auto-subscribe them to your newsletter categories. If they are in your system with communication preference for email set to No and you set it to Yes via the import utility, they still will not be auto-subscribed to any newsletter categories. If the contacts exist in your system and their email communication preference is set to Not Set and you change it to Yes via the import utility, then they will be auto subscribed to newsletters (if they have an email on file or if you include an email address in the import).
    • For phone numbers:
      • The same rules as above apply to phone numbers - you will assign phone type by column, not by row/record. You can import multiple phone numbers per person. You can put all the phone numbers (of different types) in the same column and leave the "type" field unmapped when importing if you don't know each number's "type" (if no type is selected, then the phone number type will default to "phone").
      • Phone numbers can be in any format - parentheses around the area code (ex: (888) 111-2222), area code separated by dashes (ex: 888-111-2222), or no separations between parts of the phone number (8881112222). All of these will be accepted and can be mixed and matched within the spreadsheet. The system will strip out any non-numeric characters, so don't worry about formatting the phone numbers to be "pretty" as the system will just remove your formatting during the import (and re-apply CRM formatting when displaying the phone number in the contact record).
      • You MUST include a separate column that contains the country code for each phone number if the phone numbers are not U.S. If the country code field is not included (or is left blank for any record), then it will default to U.S.
      • If your phone number has an extension, you MUST put the extension part in a separate column. It cannot be included in the same field as the main part of the phone number.
      • There is no way to import shared/household phone numbers.
      • If you include a phone number but do not include a value in the phone communication preference column, then the value will default to "Not Set" for new contacts (contacts being created by this import) (contacts already in your system will keep their existing email communication preference setting). If you include a phone number and don't want the contact's communication preference for phone set to Not Set, be sure to include the phone communication preference column for import and set the value to No.
    • When importing addresses (Mailing and Other):
      • Every row with any part of an address must have an Address Line #1 as well. If Address Line #1 is not included for that record, then any other address fields that ARE included will be ignored/the partial addresses without Address Line #1 will not be imported.
      • If you include city, state, and country, but not zip code, the system will attempt to look up/auto enter the zip code for U.S. addresses (only for U.S. addresses).
      • If including zip code, make sure the zip codes are all 5 digits (for U.S. addresses). There is no separate field for a +4 code, but this can be included in the zip field separated by a hyphen (ex: 12345-1234).
      • The address fields in each block/address section (Mailing Address, Billing Address, Other address, etc.) work as a BLOCK/unit. All fields in that block/unit will be updated if you include any field from that block in the import. That is, if you include only Address Line #1 in the import and do not include city, state, zip in the import (or even in the field mapping), the city, state, and zip will still be updated (they will be updated to "nothing"/empty, wiping out any existing data in those fields if those records are already in your CRM). When updating existing addresses, be sure to include ALL of the fields in the block, not just the ones you want to update.
      • Both CounTY and CounTRY can be imported; be careful when mapping your fields to not map CounTY to the CounTRY field.
      • All parts of an address MUST be separated into separate parts. You CANNOT put the entire address (such as "54 Rowboat Lane, Salem, MA 01970) in one field. You must separate the address into Address Line 1, City, State, Zip, and Country. Each of these five parts must go in a separate column.
      • Imported mailing addresses will default to type "home" (versus uncategorized or work). Imported mailing addresses will overwrite the existing mailing address according to the following rules:
        • If your contacts are not already in the CRM or are already in database but do not already have a mailing address (mailing address field empty in the CRM record), then the mailing address in the import will be added to the mailing address field with the type "home".
        • If your contacts are already in the CRM and have an uncategorized (not set to either home or work address) mailing address in the database, then the mailing address in the import will be saved to the Other Address field with the type "home" and the existing mailing address in the CRM record will be preserved/left as is.
        • If your contacts are already in the CRM and have a "home" mailing address in Fundly CRM, the mailing address in the import will OVERWRITE the existing home-mailing address in your CRM. The new mailing address will be set to type "home."
        • If your contacts are already in the CRM and have a "work" mailing address in the CRM, the mailing address in the import will be imported to the Other Address field as a "home" address.
        • Be aware that it is impossible to use the import utility to bulk update the "home/work" field of an uncategorized mailing address. Importing the same address will just make a duplicate address in the Other Address field and that one will be set to "home" - you'll still have the original, uncategorized mailing address left in the CRM record.
        • Be aware that it is impossible to use the individual import to import work address (use the Organization Import to create work addresses for individuals/employees).
        • Be aware that it is impossible to use the import utility to import pure/uncategorized mailing addresses. All imported mailing addresses will be set as type "home."
      • If your contact has a spouse/partner listed in the import sheet, the spouse/partner will be given the same mailing address (marked as type "home") as the primary contact.
        • If you import the spouse partner in a different line (to include their contact details such as gender, race, birth date, etc.) as well as the same line (to create the spouse/partner relationship and same household indicator), be careful of listing different HOME addresses (by including different addresses in each person's mailing address field). The last inserted/added address will become the home address. So, for example, let's say that in Row 2, you include Jane Doe, Jane Doe's Mailing Address, and her husband John Doe. On Row 3, you have John Doe with a different Mailing Address than the one listed in Row 2 for the Does. What will happen is that first Jane and John will be added with Jane's address as their home-mailing address. Then the system will process row 3 (John's Row), and John's address will be added as the new home-mailing address and Jane's address will be moved to the Other Address field (as an uncategorized address) for both of them.
      • When importing Other Address(es) - these will be imported to the Other Address field as uncategorized (neither work nor home address).
      • If you include work information for the individual:
        • You MUST include the “Organization Is Contact” column and include a value for each record with work information/an organization name. The value True will create a new organization contact record. False will not create a contact record for the organization.
        • You cannot import work address via the individual contact import. If you want to import the organization's address (and set that to the individual's work address), you will need to complete an Organization Contact Import (see our tutorials on importing organizations).
      • When setting the communication preferences via import utility:
        • If you include a phone number in the data file but leave the Communication Preference for Phone Call field blank in the data file:
          • The system will default to "Not Set" for phone communication preference when creating a new contact.
          • The system will preserve the phone communication preference already in the system when updating an existing record
        • The above rules hold true for including an e-mail address and the send e-mail communication preference and if you include a mailing address and the send mail communication preference.
    • The following fields allow you enter multiple values per contact (separated by a comma):
      • Ethnicity
      • Contact Type
      • Contact Tag
    • The following fields do NOT allow you to enter multiple values per contact; you can only enter ONE value per contact:
      • Prefix
      • Suffix
      • Gender
      • Parenting Status
      • Marital Status
      • Housing Status
      • Employment Status
      • Income Level
      • Source of Contact
  10. A few tricks for ensuring your data is formatting correctly:
    • Search your first and last name column for "&" and/or "and" to make sure you only have one name in each column.
    • Sort your spreadsheet (being sure to grab all columns and rows) by first name. Look at the beginning rows and end rows to make sure everyone has a first name. Repeat for last name.
    • Sort by state to look for any state names or abbreviations that are incorrect/spelled wrong
    • Sort by phone number to see if you have any phone numbers with too many or too few digits
    • Sort by organization name and verify that every row with an organization name also has the "consider contact record" set to true or false (no blanks)
  11. Reminder: there can be no blank rows within the data set (if your spreadsheet has 100 rows, no row from 1-100 can be empty/blank. Blank rows must be after the last row of data.
  12. Reminder: do not enter any stray marks, formatting (including highlighting), or characters outside of the rows and columns that have your data or the computer will read this as import data and will try to import it (and will generate an error/your batch will not be imported).
  13. After setting up your data sheet, if you are using our template, make sure to DELETE the instructional row (row 2).
  14. Now you are prepared to begin importing your data. Proceed to the Part III tutorial to start setting up your import batch within the CRM.

Related articles

Comments

0 comments

Article is closed for comments.

Powered by Zendesk