function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
radanradan 

inbound email processing and csv attachments

I am developing an Apex class to create records by parsing a .csv file.  

I was expecting a CSV file to appear as a textAttachment in the email object. The MIME type for a CSV file is (according to RFC 4180) is text/csv. This actually appears as a binaryAttachment with MIME type application/vnd.ms-excel. I am saving the file as CSV (comma separated values) in Excel

 

Is there a way to get a CSV file as a text attachment (whose body is then a Apex String instead of a blob)?  

Any pointers would be greatly appreciated.

 

Thanks.  

 

 

JimRaeJimRae

Just process it as a binaryAttachment instead.

Take the blob body of the CSV binary attachment and extract it to a string object, then process it.

It will work fine.

 

 

radanradan

Jim -  Thanks for yor suggestion.

        I tried doing - 

         String content = ba[j].toString();   //to get the contents as a string. ba is the binaryAttachment

 

      I got:

          "Messaging.InboundEmail.BinaryAttachment:[body=Blob[1297], fileName=....,

                    mimeSubType=application/vnd.ms-excel]"

   

      not what I was looking for. I tried to typecast (String)(ba[j) - that did not work either.

      How do I extract the blob content to a string? 

 

 

     Thanks. 

radanradan

Jim - my mistake. I should have done

                ba[j].body.toString()

 

         It works as expected. Thanks for your help! 

 

JimRaeJimRae

This is how I do it:

 

 

global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope) { Messaging.InboundEmailResult result = new Messaging.InboundEmailresult(); Messaging.InboundEmail.TextAttachment[] tAttachments = email.textAttachments; Messaging.InboundEmail.BinaryAttachment[] bAttachments = email.BinaryAttachments; String csvbody='';

 

if(bAttachments !=null){ for(Messaging.InboundEmail.BinaryAttachment btt :bAttachments){ if(btt.filename.endsWith('.csv')){ csvbody = btt.body.toString(); // process csv body here } }

}

}

 

 

 

RCJesseRCJesse

Radan,

 

I am starting to work on essentially the same project, except I am parsing a csv file to create leads. I'm new to Apex and can essentially understand it but don't know enough to start writing it yet. Was wondering if you could help me out at all? I am going to be working on it later today, just going to start to learn how to parse the string and create a lead.

 

Thanks,

Jesse

radanradan

I can help you. It is straight forward once you get the hang of it :) 

Please bear in mind the complete CSV processing is a lot more complicated that it appears on the surface as you can have embedded quotes, commas and text spanning mutiple lines. 

 

RCJesseRCJesse

Awesome, thank you. I'm hoping to avoid some of those complications because my fields are all pretty basic with no formatting. I might even be able to get my imput csv files tailered for this.

 

There is some sample code on developer site that is for creating a task from a email I was planning on following that as a guide to see what i need to do. My basic thinking is: get the first n elements from the string, found by counting commas, and get them into temp variables so i can use lets say the email to check for an existing lead. If one does not exist then create a new lead with the temp variable data and insert it into the database; follow through with that flow until the end of the string is reached.

 

Asides from the parsing for any complicated formating which i may be able to avoid is there anything i am missing in my thinking? When "inserting" or "importing" to the database do you code a trigger for any assignment rules you want to use or will leads created via the apex code automatically use the active lead assignment rule?

radanradan
I think you have the idea.  Once you check for the existence of records and do the db insert, the existing triggers should continue to function as expected. Please post any specific questions/issues you run into. 
RCJesseRCJesse

So I'm working on the first part of parsing the string for my field data.

It looks like I am going to use the indexOf String method to find the first occurance of a comma (,) then take that returned index value and use the substring String method to get the string from my starting index to -1 the index of the first occurance of the comma.

 

I would have to set integer variables that would get constantly updated so that my startindex and endindex would start at 0 and then constantly be updating based on the return values of substring method.

 

But what if there is a blank value. For instance in my test csv file which i view in excel what if there is a blank entry. In the actual csv file would it look like this: "data1,data2,,data4"

Does that mean if I try to get the substring from indexof comma+1 (after data2) to indexof comma-1 (before data4) and they are the same index that I will get an error or will it just be a usuable null value?

 

Oh man its been a long time since I've done any coding, but its coming back slowly :)

 

Am I going in the right direction or am I making it harder than it needs to be?

Message Edited by RCJesse on 04-22-2009 04:06 PM
Message Edited by RCJesse on 04-22-2009 04:08 PM
RCJesseRCJesse

Since the csv file has been converted to a string how do i detect a new line? Because the csv file doesnt have a comma between the last field for one row and the first field of the new row right? So how do i delineate a new line in the string variable? Is there a newline character i can search for? 

 

 

 

radanradan

There are multiple approaches to parsing the string -- depending on the assumptions you can safely make about the format of input CSV. For example, you can use split to tokenize the string. In the general case just looking for ',' alone will not cut it since you can have commas embedded inside a text field.
   For example: 
  "this is a long description, with commas, in the text"
 In this case the CSV exporter will enclose the text inside double quotes.  
In the general case you have to write a tokenizer that will walk through the string looking at the characters. This is a simple  finite state machine.
Based on what you are saying you don't need this level of generality since you mentioned you have control over the input CSV. 

 

RCJesseRCJesse
After converting to the string do /n/r characters still exist in the data? Could I just parse the string looking for that, although I wouldnt even know how to do that. I don't see something of a StringTokenizer in Apex.
radanradan

just invoke 
 split('\n',0)  on the body string to split along new line boundaries.

now you will have strings that correspond to individual lines that you can parse to get the field values. 

I hope this helps. 

RCJesseRCJesse

Thanks for the tip, it now works almost perfectly. Now i just have to get my Round Robin assignment rule to work correctly. For some reason it is not assigning these newly created leads, also happens when i use the lead import wizard.

 

I suppose i could just call a dummy update on the newly created lead but I feel like I shouldn't have to if the assignment rule would just be called when it was supposed to be.

hokusaihokusai
do you have any interest in posting this code? 
ehrenfossehrenfoss

I had the same need and ended up writing a function to do it. This does
not handle newlines. You might want to check the code sharing page to
see if anyone has updated it.
http://wiki.developerforce.com/index.php?title=Code_Samples

 

	public static List> parseCSV(String contents,Boolean skipHeaders) {
List> allFields = new List>();

// replace instances where a double quote begins a field containing a comma
// in this case you get a double quote followed by a doubled double quote
// do this for beginning and end of a field
contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');
// now replace all remaining double quotes - we do this so that we can reconstruct
// fields with commas inside assuming they begin and end with a double quote
contents = contents.replaceAll('""','DBLQT');
// we are not attempting to handle fields with a newline inside of them
// so, split on newline to get the spreadsheet rows
List lines = new List();
try {
lines = contents.split('\n');
} catch (System.ListException e) {
System.debug('Limits exceeded?' + e.getMessage());
}
Integer num = 0;
for(String line : lines) {
// check for blank CSV lines (only commas)
if (line.replaceAll(',','').trim().length() == 0) break;

List fields = line.split(',');
List cleanFields = new List();
String compositeField;
Boolean makeCompositeField = false;
for(String field : fields) {
if (field.startsWith('"') && field.endsWith('"')) {
cleanFields.add(field.replaceAll('DBLQT','"'));
} else if (field.startsWith('"')) {
makeCompositeField = true;
compositeField = field;
} else if (field.endsWith('"')) {
compositeField += ',' + field;
cleanFields.add(compositeField.replaceAll('DBLQT','"'));
makeCompositeField = false;
} else if (makeCompositeField) {
compositeField += ',' + field;
} else {
cleanFields.add(field.replaceAll('DBLQT','"'));
}
}

allFields.add(cleanFields);
}
if (skipHeaders) allFields.remove(0);
return allFields;
}
Message Edited by ehrenfoss on 09-15-2009 08:56 AM
Marty Y. ChangMarty Y. Chang

Hello, I've started to try to address this issue with some code that's available here:

 

"IETF RFC 4180-compliant CSV Reader for Salesforce"

http://frombelvideres4thfloor.blogspot.com/2010/10/ietf-rfc-4180-compliant-csv-reader-for.html

 

Any feedback or suggestions for improvement will be taken into account.

mariagusmariagus

HI,

 

I read your post and not sure if this can also help you in the future, if you have to deal with a huge CSV file.

 

Some weeks ago I had to process a CSV file and SalesForce didn't allow me because of its size. The system stopped to me all the time and returned the error "Regex too complicated" At the end I could find to way to process

 

Finally I developed a Batch Apex process with a custom Iterator. For futher information: http://developer.financialforce.com/customizations/importing-large-csv-files-via-batch-apex/