Uploading CSV Files in WP Issues CRM

WP Issues CRM expects upload files to be in .csv or .txt format or a similar format that includes rows of values separated by a delimiter.

  • It will not accept files labelled with extensions other than .csv or .txt.
  • With .csv files, you probably will have smooth sailing without touching any settings.
  • Usually, you will need to set the delimiter to “Tab” for a .txt file.
  • The enclosure character wraps around fields that might include a delimiter — for example, an address line in a comma delimited file would appear as “123 Main St, Apt 1” — the enclosure with double-quotes prevents the uploader from splitting the address line into two fields at the comma.
  • You will rarely need to set the escape character. That is the character used to indicate that a character that would otherwise be taken as an enclosure should be read as literal. Back-slash is standard, but is often present in files when it is not intended as an escape character. An example would be when the enclosure character is a single quote and a name has an apostrophe in it (also represented by a single quote).
  • In case of doubt, view your file in any text reader to identify the delimiter (character between fields), enclosure (character surrounding some multi-word fields) and escape (read-next-as-literal character).
  • You will rarely need to change the line length setting. Increase it to accommodate large text fields (for example the body of an email). The default setting is 5000 characters simply to catch file conditions where a delimiter is missing and the uploader is reading fields as merged.
  • WP Issues CRM saves all data in the UTF-8 character encoding. Several common character encoding translations are offered. Note that the standard MySQL implementation of UTF-8 does not support emoticons. These will be rendered as question-marks.

In this initial upload step, you should not need to worry about any individual field formatting issues. WP Issues CRM loads all data to an initial staging file in which all fields are treated as plain text with a maximum length of 65,535.

Tip: If there is a mysterious upload failure, the first thing to try is loading your file into your favorite spreadsheet and spitting it back out as a .csv file. Sometimes files include formatting glitches that a spreadsheet load will resolve.uploadparse

Alternative Layouts for Upload Files

The WP Issues CRM Upload function can upload csv-type files in many layouts. It requires a .csv ( or .txt ) formatted file, but using that basic format, there are a number of possible file layouts that it will accept for uploading different kinds of data.

This document explains the possibilities. You don’t need to plow through this unless you are trying to do something unusual. In most cases, the steps should make sense intuitively.

WP Issues CRM gives you step-by-step control over how your data is uploaded. In the examples below, the column headers chosen are just illustrative — you can map any column header to the appropriate WP Issues CRM field in the Map Fields step. You can also upload a file without column headers. Tip: At the Map Fields stage, you can see the underlying data in a confusing or system-supplied column header by hovering over the column header.
Adding Constituent Records

If you were starting from scratch, uploading the file below would create four new constituents.

fname, lname, city, dob
jack,jones,belmont,1990-01-01
john,jones,belmont,1991-01-01
jane,jones,belmont,1992-01-01
jill,jones,belmont,1993-01-01

When you are starting from scratch or know that you are adding constituents that are new to your database, you can “Run Express” and bypass the validation and matching stages.
Updating Constituent Records

Suppose you had a file of updated addresses for the same people. Jack Jones has moved from Belmont to Cambridge. Upload this file and select matching by first name, last name and date of birth. The addresses will be updated provided you give them the same address type as in the upload step. (Note that addresses will never be updated unless you uncheck “Protect Primary Constituent Data” at the Set Defaults stage.)

You could, instead, define all the new addresses as work addresses and then the people would all have two addresses, regardless of the protection setting.

fname, lname, city, dob
jack,jones,Cambridge,1990-01-01
john,jones,belmont,1991-01-01
jane,jones,belmont,1992-01-01
jill,jones,belmont,1993-01-01

WP Issues CRM will also accept a file that is a mix of updates and additions.

fname, lname, city, dob
jack,jones,Cambridge,1990-01-01
john,jones,Boston,1991-01-01
jane,jones,belmont,1992-01-01
jill,jones,belmont,1993-01-01
millie,murphy,New York,1963-01-01

Based on the match criteria you define in the Define Matching stage, WP Issues CRM will decide whether to add a new record or update an existing record. At the Set Defaults stage, you can elect not to update records that match (so just bypassing them, if for example, you thought they were just duplicates). Similarly, you can elect not to add records that don’t match (in effect, bypassing them as errors).
Adding Activity Records

As you do updates and additions of constituent records, you can, at the same time add activity records for the constituents.

fname, lname, city, dob, activity_type, activity_date, activity_issue
jack,jones,Cambridge,1990-01-01,1,2015-05-06,843
john,jones,Boston,1991-01-01,1,2015-05-06,843
jane,jones,belmont,1992-01-01,1,2015-05-06,843
jill,jones,belmont,1993-01-01,1,2015-05-06,843
millie,murphy,New York,1963-01-01,1,2015-05-06,843

In the example above, each of the constituents would get an activity added of type 1 on the date May 6, 2015 with respect to issue #843. The issue number is just the postID of a WordPress post (Issues in WP Issues CRM are just posts). Note that the issue must be open for activity assignment — it should be set to “Always appear in issue dropdown”. If not, the record will bounce as invalid. In this example, all the records had the same activity_type, activity_date and activity_issue — however, they could all be different.
Creating New Issues

You can create new issues as you upload data:

email,post_title
jack@jones.com,”Free Jim Jones”
john@jones.com,”Free Jim Jones”
jane@jones.com,”Free Jim Jones”
jill@jones.com,”Free Jim Jones”
milliemurphy@hotmail.com,”Save the trees”

The file above might be exported from your email client — a set of emails received on a given date — four on one subject, one on a different subject. Unless the titles matched existing titles, the upload would create two new issues with the titles “Free Jim Jones” and “Save the Trees.” You would be required to confirm new issue creation at the Set Defaults stage. If the title already existed, you would just create a new activity.

Whether or not the titles existed, this example would create an activity record for each email received.

Note that you could include in your upload the content of the email itself or some other content as the content of the new issue/post to be created. Or, you could go back in through WordPress or WP Issues CRM to fill out content for the post later.

Tip: If you are including content for new issue creation, repeat it on each record (as would normally happen if you downloaded a collection of emails). Among a set of records with the same title, it is not predictable which record the content will be taken from.

In this example, the only identifier supplied is email address. If the email addresses were not in the database, new constituents would be created with those email addresses. If the emails were already in the database, the new activity records would be added to the appropriate constituents.

Tip: Matching is not sensitive to email type, but the update process is. So, if the email address existing on the database is a “personal” email and you specify “work” email in Set Defaults for the upload, then the matching constituent will be updated with the new activity, but will also pickup a “work” email address record with the matching email. So, it’s good practice not to use an unnecessary multitude of email types.
Using Internal Identifier for Linking

If you wanted, for example to add phone numbers for your database using an external service, you could download your database (by entering a blank search) and then send it out for phone number addition. When the file comes back with phone numbers added, you could apply the purchased phone numbers to your database, by linking on ID (which is included in the standard WP Issues CRM download file). You could just delete all the other columns in the file and upload a file with just two columns.

id,phone
1,617-999-9999
2,617-999-9998
3,617-999-9999
4,6179999999
12312,6179999999

Note that in the 4th row, the phone number is formatted differently. That would not create a validation error. WP Issues CRM discards non-numeric characters for phone numbers, so the 3rd and 4th rows look the same. The fifth row has an out-of-sequence ID, also not a problem, provided there is a constituent with that ID.
Mixed Record Types

WP Issues CRM needs a consistent set of columns to map, so every line of the input file needs to have the same columns. The following would NOT work:

fname, lname, city,
activity_type, activity_date, activity_issue
jack,jones,Cambridge,
1,2015-05-06,843
john,jones,Boston,
1,2015-05-06,843

However, WP Issues CRM can handle files in which the columns are consistent, but for the constituent fields onlysome rows have blank values.

For example, if fname, lname, dob are the match variables, and there were no matches for jack and jane, the following file would create just two new constituent records combining across identical fname, lname, dob combinations.

fname, lname, dob, gender, occupation, employer
jack,jones,1980-01-01,,janitor,”Google, Inc.”
jack,jones,1980-01-01,m,,”Google, Inc.”
jack,jones,1980-01-01,m,janitor,””
jane,jones,1982-01-01,,executive,”Apple, Inc.”
jane,jones,1982-01-01,f,,”Apple, Inc.”
jane,jones,1982-01-01,f,executive,””

The result would be equivalent to:
fname, lname, dob, gender, occupation, employer
jack,jones,1980-01-01,m,janitor,”Google, Inc.”
jane,jones,1982-01-01,f,executive,”Apple, Inc.”

In the constituent creation process for unmatched records, records with the same identifiers are grouped together and the maximum values are taken for constituent fields. Note that the grouping process does NOT apply to address, phone, email or constituent details. So, for example, the following would create only one constituent record, but with an address record including only the city:
jane,jones,1984-01-02,”123 main st”,,””
jane,jones,1984-01-02,””,belmont,””
jane,jones,1984-01-02,””,””,02748