Data cleaning for CRM imports

Salesforce import errors

Fix "MALFORMED_ID" on a Salesforce import

MALFORMED_ID means a Record ID field in your file is not a valid Salesforce ID. A real ID is exactly 15 or 18 characters, case sensitive, with a three-character object prefix. The classic cause is Excel: it reads a long ID as a number and switches it to scientific notation, or strips the leading zeros, so the value no longer matches any record. The fix is to restore the IDs to their exact original form before you import, not to retype them by hand.

Re-expand your IDs in Sift, free →

What the error looks like

Data Loader writes the failing rows to its error CSV with MALFORMED_ID in the error column, so you can see exactly which ID values Salesforce refused.

Why it happens

• Excel reads a long ID as a number. Once it decides the column is numeric, a value too long to display gets shown in scientific notation like 1.5E+15, and saving the CSV bakes that form in.
• Leading zeros get stripped. An ID like 0000627 looks like the number 627 to Excel, so the zeros vanish and the value is now the wrong length.
• The value ends up the wrong length. A valid Salesforce ID is exactly 15 or 18 characters; anything shorter or longer is rejected outright.
• Wrong object prefix. Every ID starts with a three-character prefix for its object (Accounts, Contacts, and so on); an ID mapped to the wrong object, or a mangled prefix, will not resolve.

The manual fix

1. Re-export the IDs as text from the source system, so you start from a copy where every digit is still present.
2. In Excel, import via Data → From Text/CSV and set the ID column to Text before the file loads, so Excel never treats the IDs as numbers.
3. Never double-click the CSV to open it; that path re-guesses the column type and mangles the IDs again the moment the file opens.
4. Confirm each ID is 15 or 18 characters with the right object prefix, then import.

The Sift fix

1. Drop your CSV or Excel file into Sift. It runs in your browser; the file is never uploaded.
2. Sift detects scientific notation in every column and re-expands the values where every digit is still present, restoring them losslessly (for example 4.47911123456E+11 back to 447911123456).
3. Anything it cannot safely restore, a display-truncated ID or a stripped-zero value of unknown width, goes on the needs-your-eyes list instead of being guessed at.
4. Review every change with a before/after diff, then export the clean file straight to the Salesforce import screen.

What Sift can't fix

This is the honest part most pages skip. If Excel display-truncated the ID, for example a 1.5E+15 that only carries a few significant digits, the rest of the digits are already gone from that file, and no tool can restore what is not there; anyone claiming otherwise is guessing. Sift re-expands only fully specified values and refuses to invent the missing ones. The real fix in that case is to go back to the source export while the original is still intact, and clean a fresh copy outside Excel. If the mangled file is still open in Excel, the underlying digits may still be there, so close it without saving and re-export rather than overwriting your only good copy.

Related guides