searching for bad data

Jennifer Reager

I'm still new-ish to my organization and recently found that there are many records that have been imported where special characters came over as "??". For example, Renée is Ren??E. Yuck!


I realize that the translation error is originating in the export file we receive, so that side needs to be addressed. But in the meantime, I've tried to query on "contains ??" and it treats it basically like a wild card. Any suggestions?


Bonus question which I think I already know the answer to : is there a way to find records that have been entered in all caps, other than exporting and perusing spreadsheets?

Alan French

Found a Knowledgebase article which says it's not possible to query directly for wildcard characters (I was hoping BB might have provided a way to escape a wildcard, but apparently not...)


The article gives instructions on how to create a query of addresses containing wildcard characters using global change - I've not tried it out but I can't see any reason that it wouldn't work for names as well.


As for the caps, Query is case-insensitive so unless you have SQL Server I think you might have to export everything

Lawrence Rush

I think that the best way would be to export all of the data. It should be relatively easy then to do a find and replace in excel and re-import the corrected data.

Rachel Slager 2

Unfortunately on the ?? I'm not aware of a workaround other than exporting and doing search in excel.  The other issue with that is you will have to know which special characters were being overwritten so that you can correct them.  When you do your find replace, replace the ?? with ?? but select Options then Format, then Fill, then replace all.  Now you can sort by format so that all cells filled with color are at the top for easier review.

 

Gracie Schild

My first instinct would be to find some other kind of query that will find these folks (added after date x, constituent code = y, or import id greater than z). Or send a "mailing list" to a mail house and have them run an NCOA on it - my guess is there will be a big list of "bad addresses" returned to you. If you've included the Constituent ID on the mailing list, you can use those for a query that pulls up that group with all the places where the error occurrs (Addressee and Salutation, Name, Primary Contact, etc.). I know I wouldn't have time to sort through a mass of records in Excel, but I could always find a few minutes a day to work down a query result and click through to the records individually to repair them. 


And beyond that, I'd make the creator of the original file do the work. Just always keep the constituent ID with the data. On a slightly different note, I switched to "Import ID not blank" for an attribute query that wasn't pulling all the records we expected - previously, I had pulled on Attribute Description not blank" then discovered that some constituents had been given the attribute with either ?? or nothing at all in the Description (Naming Details, in this case, that hadn't yet been confirmed). There's always another way in to the data!