[SalesForce] How to correctly read CSV file, BLOB not UTF-8 string

I have a CSV file which I am trying to read using Apex, the file looks like this:

COLUMN1;COLUMN2;COLUMN3;COLUMN4;COLUMN5;COLUMN6;COLUMN7
VALUE1;VALUE2;VALUE3;VALUE4;VALUE5;VALUE6;VALUE7;

The file is stored as an attachment which I query like this and try to read:

Attachment csvFile = [SELECT Body FROM Attachment WHERE ParentId =: myFileId LIMIT 1];
String csvContent = csvFile.Body.toString();

This returns BLOB is not a valid UTF-8 string

I've also tried encoding it like this:

Blob csvBlob = csvFile.Body;
String csvContent = EncodingUtil.base64Encode(csvBlob);

But this brings back random characters in the csvContent variable, likely because its been encoded.

Can anyone advise the correct way to "decode" this?

Best Answer

For the bytes you shared, the text came out as:

STAMP_DATE;CUSNO;FIRSTNAME;LASTNAME;COMPANYNAME;ROWTEXT1;ROWTEXT2;ROWTEXT3;CUSTOMER_TYPE;CUSTOMER_TITLE;CUS

Which means that the problem isn't at the beginning. What's going on is that you've got a stray byte somewhere that isn't UTF-8 compatible, and the upper range of ASCII characters (0x80-0xFF) have a different meaning in Unicode (mostly invalid combinations, like 0x80 0x00 would be invalid).

Without seeing the entire file, we really have no idea what's broken, but if I had to guess, it's most likely a character that has some sort of decoration (e.g. Ñ), which in ASCII are one byte, but in Unicode are two (or more) bytes. When invalid characters appear in a Unicode string, Apex code fails with that exception, as it cannot understand ASCII/ISO-8859-1 encoding except in some special circumstances.

If your file is in ISO-8859-1 format, you'll first need to convert it to be Unicode compliant. Most likely, that means opening it up in a text editor (not Notepad, but a good one, like Programmer's Notepad or Notepad++), change the encoding to Unicode, upload the new file to Salesforce, and try again. Or, you can try to decode it using something like in this answer. Your results may vary.

Related Solutions

[SalesForce] ‘BLOB is not a valid UTF-8 string’ error

While it does not appear to be clearly documented, having scanned a number of forum posts about this. The Blob type only supports UTF-8 encoded strings, you must ensure that the file you're uploading complies with this encoding, otherwise you will get this error in cases where you have special characters.

To ensure your file is UTF-8, for example in Windows Notepad make sure that you select to save as Unicode UTF-8 in the Save As dialog, other applications will likely have a similar option (for command line this answer might be of use).

[SalesForce] Processing a json api response using JSONParser class

There is the DeserializeUntyped method that comes to the rescue in these more complex cases. Below is an example of one possible way how to tackle this. Based on the example for VALUE1 you should be able to work out the rest:

List <sObject> distributions = new List <sObject> ();

// Deserialize the JSON to a map of key => value
Map <String, Object> root = (Map <String, Object>) JSON.deserializeUntyped(jsonStr);

// Get the List of objects from VALUE1
List <Object> value1 = (List<Object>) root.get('VALUE1');

// Go through each of the objects
for (Object obj : value1)
{
    // Cast the object to a map of key => value
    Map <String, Object> objectMap = (Map <String, Object>) obj;

    // Create a Distribution__c sObject
    sObject distribution = new Distribution__c();

    // For each of the keys in the VALUE1 objects
    for (String key : objectMap.keySet())
    {
        // Assuming all the keys from the JSON match the API field names by appending __c
        // Copy the values from the JSON object into the sObject
        // Missing fields will be set to NULL
        distribution.put(key + '__c', objectMap.get(key));

        // add the sObject to the list
        distributions.add(distribution);
    }
}

// do something with the list of distributions

Best Answer

Related Solutions

[SalesForce] ‘BLOB is not a valid UTF-8 string’ error

[SalesForce] Processing a json api response using JSONParser class

Related Topic