I'm pairing this recipe with another because this process requires its own separate thought pattern.

Bad data creeps into any database system over time. It's what you decide to do with that bad data that really matters. Ignore? Fix by hand (manually)? Start over? You'll likely do a little bit of each of these depending on what you're dealing with to be honest.

Whether the data started out bad to begin with, was brought in from a former system migration or bad data came in over time with Event Registrations / Workflow Forms, it's important to understand how you can clean up some of this data quickly and easily with some simple understanding of the SQL Command tool build inside of Rock and your old friend Excel / Google Sheets. Yes, in the Rock world we try to keep Excel in the application tray and not disturb it, but sometimes, it is useful to dust off this application for such a time as this!

Let's go through an example of bad data together and hopefully this process will give you some ideas of other areas in your Rock database that you can utilize this method to clean up as well.

Someone from the community posed a question on how to fix many things at once in Rock. Specifically how to update a bunch of family names with a superfluous "The " in the family name or in Rock the Group Name field since Family is just a type of Group. I've used this technique in a previous life/career and have had great success with it in Rock for cleanup and migration of data purposes. This simple yet effective method can help you clean up plenty of simple data errors in mass all with a little assist from Excel.

You can do the same thing with Person records and correct the First Names that have a husband and wife combined with an '&' or 'and'. With that example you'd want to select some additional data to ensure that you are correcting the first name field to the appropriate person that the record applies to, ie. should this particular person record have the first name of the husband or the wife?

STEP 1

Find your inaccurate data by creating your simple select in the SQL command. This example finds families (GroupType) that have "The " in the Name field of the group. Rock by default does not have anything other than the surname and family in this field. Such as "Murphy Family", not "The Murphy Family". You can prove this out by going to your Group Viewer page and updating the GroupId parameter in the URL to a family that you know may have bad data in it. For this example I'll reference my own family and edit the Group Name temporarily, adding the "The " in order to show you how this can be fixed for many families at once.

SELECT
    g.Id,
    g.Name
FROM
    [Group] g
WHERE g.GroupTypeId = 10 --Family
    AND g.Name like '%The %'

The query above got the results I would've expected. But it should be easy to fix just one record by going to the Group viewer page and editing that one group's name field right? But what if you returned 7K+ records that were inaccurate? How would you fix them all at once without having to edit each group name field manually?

STEP 2

Export your results when you're satisfied with them to Excel from the results grid of the SQL Command tool in Rock. Save this as not only a backup of the original data in case you need to resort to the changes, but also to be used in order to change these bad values en masse.

STEP 3

A) Cleanup your data in Excel quickly by doing a find / replace on the entire column of data.

B) Format an Update command in order to correct a single record first. Once you know how to properly correct 1 record, then you'll be able to leverage Excel in order to correct many quickly and efficiently

UPDATE [Group]
	SET Name = ' '
WHERE Id = 63 -- change this GroupId to any that you found in your orig selection query to test your UPDATE logic
	AND g.GroupTypeId = 10 --Family

STEP 4 - In your Excel file create a formula column that Concatenates the Update command you made but selects the fields that you need to key off of or update in the command.

Ex:

=CONCAT("UPDATE [Group] SET Name = ","'",B2,"'"," WHERE Id = ","'",A2,"'")

STEP 5

Once your Excel formula has the proper Update command formatting that you'd expect with your single record example, you can now copy that formula down the sheet to create a massive set of row SQL Command Update commands.

STEP 6

Simply copy this column of formatted UPDATE commands and paste them into the Rock SQL Command Tool. Be sure to flip your SQL Command to an UPDATE process first. Run the full set of commands.

! IMPORTANT ! SQL UPDATE Commands are possibly irreversible. It is important to make a backup of your database and/or save an original export of the select statement with Id's if you have to go back to the original data state.
Note: Updating records via a SQL Command UPDATE will not populate the Person or Group History change timeline.

The output after running the command will and should reflect the number of UPDATE command rows that you pasted into the tool from Excel.


Be patient depending on the number of records/rows that you are updating. The time that it takes to complete is dependent on your instance and database CPU/RAM resources. If a massive list fails simply run the list in chunks, i.e. top half, then bottom half. Split further if needed. I've successfully used this technique to correct upwards of 11K rows at once with a very low powered instance before. You may also adjust the timeout setting on the SQL Command block prior to running your update to be sure. However if the select statement returned what you needed the Update statement will also likely run at just under the same rate.

STEP 7

Re-run your original select to see if any that you previously updated squeaked through. Now use this method to find other variations and minor data cleanup needs on group, person, and other entities in RockRMS! An Excel sheet template is included as an attachment on this recipe, feel free to adjust to your needs.

After doing a few of these massive cleanup activities you'll feel a bit like this, a keyboard Rock Star!: