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.”