Using Scrbe to Work Through Legacy Data

Posted on: October 26, 2009 | By: SuperUser Account | Logan Consulting News

Data migration has many challenges but the most difficult is duplication of records. Using Accounts as an example, there are many different ways to input a company name. If there are more than one users entering new account information in a database, chances are you will find the same company multiple times. Example: Microsoft or Microsoft , Inc. or Microsoft Inc. or Microsoft Incorporated or Microsoft Corporation. (CRM 4.0 includes the ability to set rules which identify possible duplicates so the user can determine if a new record should actually be created.)

The best way I found to work through legacy data is to use the built in parse functions in SCRIBE.

1. STRIPCOMPANYSUFFIX: Removes all trailing punctuation and words in the following list: Inc, Incorporated, Corp, Corporation, Co, Company, Ltd, etc.

2. STRIPCOMPANY: Removes all punctuation, any trailing words in the following list: Inc, Incorporated, Corp, Corporation, Co, Company, Ltd, etc.

3. STRIP( SourceField, “A” | “N” | “P” | “W” | “O” ): Returns only the requested type of characters from text string: alpha, numeric, punctuation, whitespace, other/unknown
a. STRIP(“A”)
b. STRIP(“N”)

The source account name is parsed 4 ways and inserted into a new table. If there are existing accounts in CRM 4.0, those company names are extracted and parsed as well. Then simply compare parsed value to parsed value and create a series Success messages to log in the rejected source rows log. Always insert a unique ROW number in your source file so you can compare the results to the original file.

---

---