There are several obvious hurdles that you encounter when utilizing Microsoft Excel to inspect, map and manipulate the data from a source system into Salesforce. They are:
- UTF Characters – If you are in the Americas and the source data has any UTF characters among the otherwise US-English-looking data, Excel will convert them to square root and math symbol gibberish. The German name “Krüger” turns into “Kr√ºger”. The French name “Geneviève” turns into “Genevi√®ve”. Asian characters are even worse. The problem is that when you double-click on a CSV to edit it, Excel inspects a small sample and incorrectly concludes that the appropriate character set is the desktop’s default character set. For most users, this results in lots of ugly corrupt data. You can get around this by being diligent and doing a manual “Text Import” in Excel every time you intend to edit there. But sooner or later you’ll forget.
- Escape characters are unreliable – If the data includes any long text fields such as the body of an email message, editing anything in those rows will be inconvenient at best and much more likely result in corrupted data. Excel cannot adequately deal with very large fields that happen to have CSV-sensitive data such as double and single quotes in it. A proper, dedicated CSV editor will have no hiccups with the escapes, but Excel does. It’s a wonderful tool that was not designed for this.
- Not the tool, but the approach, lends itself to a one-shot load straight into production. This is a huge problem. Although Data Loader will tell you which rows failed (and there will be lots) to load and why, it gets extremely tedious to fix the broken data and make a new do-over upload. Since the entire approach lacks structure and rigor, the result winds up being something like “98% is good enough”. Later on, you’ll find something extremely important was left behind in that missing 2%.
- And what about attachments? For Customer Support data migrations in particular, these are extremely important. In all such systems, customers send in screenshots and pictures to show what they are seeing. If you are transferring several years of support case data into Salesforce, there could be tens of thousands of attachments to be migrated. Disregarding those annoying small email signature images, thousands of important images must still be transferred and correctly linked to the migrated cases.