Wednesday, February 5, 2014

Mass upload/insert non-English characters to Salesforce

We can mass upload / insert data to Salesforce easily using Data Loader or other API tool. But, if we get the data in Excel file with non-English characters, non-English characters will be come ?? if we save it to CSV file directly from Ms Excel.

To able to upload in non-English characters, the file should be in UTF-8 and here is a step by step procedure that needs to be done since it is a limitation of Excel to save non-English character to csv file.

[ MS Excel ]
1. Open the *.xlsx file from MS Excel
2. Click menu > Save As
3. Put any name for the file and choose "Unicode Text (*.txt)" for "Save as type :" and Click Save

[ MS Notepad ]
4. Open the unicode text file using Microsoft Notepad. Some characters can appear as a box, however, this is because Notepad cannot display some unicode characters, so you can ignore this at this point.
5. It must be the tab delimitered file. Replace tabs with comma (,)
- select tab character by dragging the character between two column headers and press Ctrl+C
- Replace all tab characters with comma using Replace function (Ctrl+H)
6. In notepad, Click Save As
7. Put a filename and change Encoding: to "UTF-8"
8. Click Save.

[ Windows File Explorer]
9. Change the file extension from "*.txt" to "*.csv". The file icon should be changed to MS Excel now. If it is not changed, check Windows Folder options. (Windows Start menu > Control Panel > Folder Options > View tab > uncheck "Hide extensions for known file types"). This will show the file extensions. Try changing the file extension again.

[ MS Excel ]
10. Open this CSV file from Excel and verify if the data is okay.
*** Even if you find any problem in this step, you should NOT save the file from Excel. It will cause the encoding problem!! Just check the data only in Excel. If you see a data problem at this stage, fix the data from the original spreadsheet and repeat the steps again. Or, you can change it from Notepad but should ensure that the file is saved as UTF-8 format.

You can download sample csv file with non-English characters saved in UTF-8 encoding here.


Post a Comment

| ,