I have commented many times, and in context of a lot of IT experience with similar tools, how clean and intelligent the PCO People import tool was designed. As I said elsewhere, our migration process was like a MasterCard commercial:
- Exporting data from CCB: MAJOR pain
- Reformatting and applying class/scope changes to CCB data for PCO import: Difficult
- Importing into PCO People: Pricelessly simple and painless!
There is another great use for the simplicity and intelligence of this import tool, which we have been using well beyond the migration — PCO People batch data updates.
The PCO People import tool is intelligent enough to:
- Know when to update data rather than create it, if the right fields are in the import data
- Update *only* the fields provided in the import, leaving all other data alone.
This means that, if you export all of your PCO People data into a CSV file, update the data you want, remove all the fields/columns you don’t want to change, and reimport, the import tool will update only those fields.
In order for the tool to import rather than create new records, you will want to leave two key index fields from the export — the First Name and Last Name fields.
A few use cases where this kind of batch update is really useful include: updating gender, membership status, Campus (which we have as a custom tab). Let me walk you through an example where we updated Gender:
- Within PCO People, click on the People menu, then the
menu, select Export People from the menu, and then Generate and send CSV. Because PCO wants to run these exports as a lower priority (and not take performance away from user interaction), they queue up the export and send the user an email to download the CSV file when it is available — usually takes only a few minutes. - When you get the email, click on the link to download the CSV file. Since you may generate many of these over time and the default filename can a bit cryptic, It’s a good practice to rename the file for the purpose you want — in this case, let’s rename the file gender_update_test.csv. I typically date the file for archiving, such as gender_update_060117.csv for the process I did on June 1st, 2017.
- Open the CSV file in Excel or Google Sheets — you should be able to directly open the file, and it will format correctly, which each field as a column in the spreadsheet. For this update (Gender) we will need the indexes (First Name, Last Name) and Gender fields, so you can delete all columns other than these three. It’s important to leave the first row as headers (“First Name”, “Last Name”, “Gender”) and NOT change the spelling, capitalization, etc of these header values — PCO will use these to automatically associate to the existing fields in PCO people.
- Update the gender values as needed in the spreadsheet, using PCO’s values (single upper case letter M or F for Male and Female).
- Bonus points: since you are in Excel or Sheets, you do have the build in macro, filtering and other features of these tools available to you, which can really make this process even easier (Easier? is that even possible? Sure!).
- Filtering: In Excel/Sheets, highlight all 4 columns, then click the Data menu, then Filter. This will give you a simple method to sort, show/hide data as needed. For example, since we are doing Gender, we are probably focusing on the records that don’t have Gender. So, now that filtering is enabled, click on the down arrow next to the Gender header, click on Select All to clear all checks, then click on Blanks to show just the records with no Gender.
- Sorting: Rather than just filtering down to the records without Gender, how about we remove all records that have a Gender already, so we are only importing the ones we want to update? To do that, clear the filter (if you set it) by clicking on the down arrow again, and clicking Select All, which will remove the filter. Then, click on the Sort Ascending or Descending button, which will sort all of the rows by the Gender value. Now you can simply highlight and delete all records with M or F in the Gender field, reducing this to the records you want to update.
- Sorting 2: Since we are updating Gender, it’s likely that a lot of the first names will give us a clue to the Gender — its likely that Harold will always be Male and Susan will always be Female. So, let’s now do a sort on First Name by clicking on the down arrow next to First Name, then clicking on Sort Descending. Now you should be able to review vertically — all of the Bob’s, then all of the Brenda’s, etc. the Chris’ and Carmen’s you will have to look up elsewhere since the name isn’t obvious ?
- Macros — stay tuned for my upcoming Double Bonus blog
- Bonus points: since you are in Excel or Sheets, you do have the build in macro, filtering and other features of these tools available to you, which can really make this process even easier (Easier? is that even possible? Sure!).
- Once you have your spreadsheet updated the way you want, you may have some records that you were not able to update or find a Gender for the person. You can either leave those in the spreadsheet (import tool will see the data as the same and ignore it) or re-sort by Gender, find the rows with empty Gender, and delete them.
- To save the results as a CSV:
- Excel: Click on the File menu, then Save As. Be sure that the File Format is set to Comma Separated Values (.csv).
- Google Sheets: Click on the File menu, then Download As, then Comma Separated Values.
- For both of these, you will be asked for a file name and location to place it. For our purposes, you will want to save it as the same name and location, overwriting the original file.
- If there are any issues with the CSV file, the import tool will tell you.
- Back in PCO People, click on the menu again, selecting Import People, then Start New Import. When prompted, choose or drop your updated CSV file.
- You will be prompted to associate your CSV fields with the PCO People fields — assuming you left the headers alone, this will automatically be done for you. Click on Preview Import, and PCO People will build an assessment preview for you, letting you know if this import will create new Profile (which it shouldn’t), how many Profiles will be updated and any errors, Be sure to review the errors to correct any issues and update your CSV accordingly. For example, if you mistyped one of the Genders as G rather than F, the preview will tell you that the G value is not on the list or allowed, etc. If so, you can correct the spreadsheet, and repeat steps 6 – 8 above.
- Once you are happy with what the preview is telling you, click on Finish Import. The import will complete, and when finished will display what it did.
- If you don’t like the results of the import, click on Undo in the upper right to reverse the changes.
That’s it! Once you understand the export —> modify —> import process, this tool becomes a powerful way to make numerous changes at once.
Important — Please keep in mind that any changes made directly in PCO people (web interface, any of the apps) between your export and reimport may be overwritten by your import. This is another good reason to delete any columns you don’t intend to update — its unlikely that the First Name or Last Name will change, and other than that the import will only modify the Gender, so if someone is updating their Phone Number, or email address while you are export/importing, their updates will not get overwritten.
Photo by Brendan Church on Unsplash