QUESTION:
When inserting data in to Salesforce using DemandTools, I am noticing that leading zero's are not being retained. How do I retain the leading zero's?
ANSWER:
Data entered into an excel spreadsheet that looks like a number will be treated as a number by default. As such, leading zeroes will be truncated when inserting or updating records in MassEffect. When looking for matching records using Discovery -> Find/Report ID's, if the data in Salesforce contains leading zeroes then the input file must be formatted to also retain the leading zeroes, otherwise matches will NOT be detected.
Common examples of data that this affects are zip codes, social security numbers, serial numbers etc.
To retain the leading zero use xls or xlsx as the input source. Format the column as "text" PRIOR to inputting the data, that way the data will be seen as text vs. a number and the leading zero(s) will not be truncated. To confirm that excel is storing the data as text, check to make sure the data is left justified in the column and that there is a small green triangle in the upper left hand corner of the cell.
For EXISTING data in a column that needs to be reformatted as text:
- Copy the existing data and paste into notepad
- Delete the existing data from the column (or create a new column)
- Format the EMPTY column as text
- Paste Special "Values" from notepad back into the column
The data should now be left-justified with the green triangle in the upper left hand corner indicating that excel sees it as text vs. a number.
NOTE: To fix zip codes that lost the leading zero during a previous import, use the MassImpact formula ZipCodeClean. More information on this and other MassImpact formulas can be found in HERE
