Import has the ability to normalize input data before uploading to your CRM, a process called Transform.
- You can select a variety of pre-built formulas designed to address common data standardization needs.
- Transform is available for the Insert, Update, and Upsert operations.
In step 1 of the Import module, map your fields and then select the three vertical dots at the far right of each row for the fields you want to transform. In the menu that appears, select Transform.
Transform Formulas
The following formulas are available:
Case - Upper
- Sets all the letters in a field to upper case.
Input: ma
Result: MA
Case - Lower
- Sets all the letters in a field to lower case.
Input: MA
Result: ma
Country Match - 2 Character
- Converts country to ISO 2 character name.
Input: Germany
Result: DE
Note: United Kingdom or UK will be changed to GB as this is the ISO format.
Country Match - 3 Character
- Converts country to ISO 3 character name.
Input: United States
Result: USA
Note: United Kingdom or UK will be changed to GBR as this is the ISO format.
Country Match - Long (Full Name)
- Converts country to its long form name.
Input value: CA
Result: Canada
North America Phone Standardization
- Formats a phone number match the format of the Salesforce user interface, e.g. (XXX) XXX-XXXX. Will also standardize an abbreviation for extension to x.
Input: 212-555-1212
Result: (212) 555-1212
Input: +1 212.555.1212
Result: (212) 555-1212
Input: 2125551212 ext155
Result: (212) 555-1212 x155
Note: This is a North American phone fix. DO NOT use with phone numbers outside North America. If fewer than 10 characters are returned after stripping alpha characters and punctuation, DemandTools will return the original input data.
Propercase Name
- Propercases a "Name" field, typically a Company/Account Name or First and Last Name.
- Instead of just upper casing the first letter of each word, applies logic to handle common prefixes (e.g. Mc and Mac) and abbreviations.
- ONLY attempts to proper case when the input data is all in upper or lower case.
- If the input data is already proper cased (combination of upper and lower case, e.g. eBay), changes will not be made.
- Applies logic regarding the prefix “Mac.”
- Does not capitalize the first letter after “Mac” if the word is 5 letters or fewer.
- Applies logic to identify abbreviations.
- When there is no punctuation between the letters, and the word contains NO VOWELS, the word is assumed to be an abbreviation and all letters will be uppercased, e.g. BCBS, GM, JFK.
Input: MARY
Result: Mary
Input: mcdonald's corp.
Result: McDonald’s Corp.
Input: HEWLETT PACKARD
Result: Hewlett Packard
Input: JJ ENTERPRISES
Result: JJ Enterprises
Input: Validity, Inc.
Result: Validity, Inc. (already proper-cased so NO changes made)
Input: EBAY
Result: eBay (cases eBay in the preferred format since this value is in the ProperCaseOverrides.txt file)
Note: The following words WILL NOT be capitalized: and, at, by, for, of, or, to, it.
Propercase Name (Input Case)
- Same as Propercase Name, except this formula propercases all data regardless of existing case.
Input: Mary
Result: Mary
Input: mary
Result: Mary
Input: JAB Consulting
Result: Jab Consulting
State Match - Long
- Updates all US states & Canadian provinces to their long form names.
Input: OH
Result: Ohio
Input: Conn
Result: Connecticut
State Match - Short
- Updates all US states & Canadian provinces to their ISO two-character abbreviation.
Input: Florida
Result: FL
Input: Ma
Result: MA
Input: Cal
Result: CA
Zip Code Clean
- Attempts to reformat US zip codes, adding a leading zero if needed. The primary purpose is to fix zip codes where the leading zero was truncated on import.
Input: 2043
Result: 02043
Note: This is JUST for US zip codes, specifically those in states that typically have a leading zero. May cause unwanted results with non-US postal codes.
