Loading Data with Special Characters
If your data contains special characters, it is important to determine the best way to mass import/update the data via a data loader to ensure that the data stays clean and in its original form. Loading data with special characters into Salesforce using a data loader can be a little tricky. You may find that you have to try the process a few times before getting it to work. Have you ever experienced issues with losing the special characters when saving the spreadsheet to a .csv or trouble saving the spreadsheet as unicode via the text editor without losing the format? Do you find that you’re just not sure how to even begin loading special characters? Then you will find this blog helpful. I will take you through a step by step process for loading special characters via a data loader.
Saving Excel Spreadsheet
Once your spreadsheet is ready to be inserted into Salesforce, one of the first steps is to save it as a unicode text file (see image below). Unicode is the format needed for opening and preparing the file in the text editor and you can save your spreadsheet as any unicode text file option available to you.
Formatting Spreadsheet via Text Editor
You can also use any text editor to format your spreadsheet. For Macs running OSX, TextWrangler and TextMate are both great and easy to download and use. For Windows, some of the top text editors are Notepad++ and Sublime. Sublime is also available on Mac and Linux.
For this blog post, I’ll use TextWrangler to illustrate how this works, but you can use anything that has the ability to find tabs and replace them with commas. Once you open the text editor, open the .txt file you just saved and you will see the contents in the editor.
You will then need to remove the spacing between the characters so the file can be later converted to .csv without losing the formatting. To do this, click on Search → Find.
A Find pop up window should open up. Enter “\t” in the Find section and then enter a comma in the replace section and click on Replace All; “\t” represents a tab, as it’s a regular expression. This will replace all of the spaces with commas.
Your text will look consolidated with all the spaces removed and separated only by commas.
Now you can save your file as the Unicode 8 file, which is the format needed for maintaining the special characters in Salesforce.
Once your .txt file is saved as UTF 8, you can change your file’s extension to .csv by clicking on the file name and replacing .txt with .csv (see image below). You will then see a pop up window asking if you want to make this change and you can click on “Use .csv.”
Loading Spreadsheet via Data Loader
You can now use any data loader (e.g. Apex Data Loader, dataloader.io) to import your spreadsheet, the process below is showing Salesforce’s Data Import Wizard.
Once you launch the wizard and select your object (in this case Accounts and Contacts), you can just drag and drop in your .csv file (see image below). You must then select the character code, which is Unicode (UTF 8). Once you select this, click on next and double-check that the special characters look correct in the examples provided. If the special characters are in their original format, then you are all set to load this into Salesforce.
If there are other great processes for loading special characters that you would like to share, please feel free to comment below, on the Salesforce Success Community, on our Facebook page, or directly at me on Twitter @sylviacabral44.