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.
The following formulas are available:
Case - Upper
- Sets all the letters in a field to upper case.
Case - Lower
- Sets all the letters in a field to lower case.
Country Match - 2 Character
- Converts country to ISO 2 character name.
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
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
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.
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.
- 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: 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)
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: JAB Consulting
Result: Jab Consulting
State Match - Long
- Updates all US states & Canadian provinces to their long form names.
State Match - Short
- Updates all US states & Canadian provinces to their ISO two-character abbreviation.
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.
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.