The requirement I'm working on requires to read a CSV file with temperature measurements. But Fahrenheit or Celcius symbols are causing exceptions when converting csv blob file to string using toString(). I ran into this issue many time in the past but it's because of mysterious special characters that get inserted because of copy-paste. This time i inserted these symbols from excel insert tool box, it didn't work. I queried some data points with these symbols in my salesforce org and pasted in csv. It didn't work either. Please let me know your thoughts. Thanks.
1 Answer
Somehow, your users are inserting into their source spreadsheet a character that the spreadsheet can't encode as a valid UTF-8 character upon SAVE. SFDC only accepts UTF-8 characters. ASCII 00-FF maps to UTF-8 asis and the degree symbol °
is ASCII B0
. So you would expect to be fine.
But, if the user is copy-pasting the combined degree+C symbol ℃
(UTF-8 = U+2103
) into Excel and then saving as CSV, that is not in ASCII 00-FF and won't save as UTF-8 by Excel. Same issue with ℉
which is UTF-8 U+2109
Who knew there were such UTF-8 characters? Cool.
MSFT Excel (through 2013, last version I have checked) will not save CSV files as UTF-8. Stackexchange discussion on VF uploads of CSV files here.
Example: ℉
does not save as e2 84 89
but instead saves as 3f
= ?
Open Office does have a save as CSV with UTF-8 encoding but your org may not use Open Office.
Google Docs does have a save as CSV with UTF-8 encoding but your org may not use Google Docs and anyway, as a cloud solution, having to save the file in Google Docs to your desktop and then upload to SFDC is a user bummer.
-
Re encoding the file in notepad++ has a side effect. Even though we are looking at a csv, when we re encode, it is actually changing the format to unicode text. When I duplicate the csv, both the original and copy are changed to unicode text. I even pasted UTF-8 encoded text in the individual cells, but still getting the blob error. I don't understand why it would throw blob exception when the text was encoded into UTF-8 and pasted into the cells. Will the text loose its properties when I paste it in csv? How can I import ℉ now?– AvinashDec 28, 2015 at 21:22
-
If using MSFT Excel, "save as CSV" will never save chars pasted in as UTF-8 as UTF-8.– cropredyDec 28, 2015 at 21:49
-
I'm not saving xlsx file as csv. I open a csv and paste the UTF-8 encoded text into the cells. Even that is not working..– AvinashDec 28, 2015 at 22:01
-
as I said, MSFT Excel will never, ever save UTF-8 encoded chars in a .csv extension– cropredyDec 28, 2015 at 22:02
EcnodingUtil.base64Encode
(documentation)?00B0
= ASCIIB0
. MSFT Excel won't save CSV as UTF-8 but this typically is a problem only with asian languages or eastern european character sets when it comes to SFDC