+ Start a Discussion
VinuVinu 

Creating a CSV file from salesforce and sending through the Email

Hi,

 

I need to construct a CSV file with APEX and am a bit new to this cloud computing.

Do you have any sample code I could get started with that, or a resources that you can point me to develop and generate a  CSV File to send through the Email??

 

Can this be achieved in salesforce by any Way?

nasknask

1)first  query the records

2) create header row containing all coloumn names :

string excelHeader = 'Name,Id,Created Date, createdby\n';

3) iterate your records and create strings from respective fields from records, depending on the above format add '\n' to each row.  

eg : record1 = 'Name1,Record id 1, 12/12/2012, ashok1 \n';

        reord2 =  'Name2,Record id 2, 12/12/2012, ashok 2\n'

etc in for loop keep adding thses strings to final string.

4) created a blob object assign this content to blob send as attachment in mial or store as document.

VinuVinu

Hi Ashok,

 

Up to point number 2 i can,

But at the 3rd point im stumped to go further,

Can u explain little bit more, If possible??

 

Thanks!!

nasknask

Please see the working code. paste this system log and execute. replace the email test@test.com with your email.

Please mark this as solution if this has helped.

 

List<Account > acclist = [Select id,name , CreatedDate , lastModifiedDate from Account limit 10];
string header = 'Record Id, Name , Created Date, Modified Date \n';
string finalstr = header ;
for(Account a: acclist)
{

       string recordString = a.id+','+a.Name+','+a.CreatedDate+','+a.LastModifiedDate +'\n';

       finalstr = finalstr +recordString;

}

Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(finalstr);
string csvname= 'Account.csv';
csvAttc.setFileName(csvname);
csvAttc.setBody(csvBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] toAddresses = new list<string> {'test@test.com'};
String subject ='Account CSV';
email.setSubject(subject);
email.setToAddresses( toAddresses );
email.setPlainTextBody('Account CSV ');
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});

adflintadflint

nask wrote:

Please see the working code. paste this system log and execute. replace the email test@test.com with your email.

Please mark this as solution if this has helped.

 

List<Account > acclist = [Select id,name , CreatedDate , lastModifiedDate from Account limit 10];
string header = 'Record Id, Name , Created Date, Modified Date \n';
string finalstr = header ;
for(Account a: acclist)
{

       string recordString = a.id+','+a.Name+','+a.CreatedDate+','+a.LastModifiedDate +'\n';

       finalstr = finalstr +recordString;

}

Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(finalstr);
string csvname= 'Account.csv';
csvAttc.setFileName(csvname);
csvAttc.setBody(csvBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] toAddresses = new list<string> {'test@test.com'};
String subject ='Account CSV';
email.setSubject(subject);
email.setToAddresses( toAddresses );
email.setPlainTextBody('Account CSV ');
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});


Great Solution!  I added text qualifiers so that any Account names with commas wouldn't cause the csv file to be formatted incorrectly.

 

List<Account > acclist = [Select id,name , CreatedDate , lastModifiedDate from Account limit 10];
string header = 'Record Id, Name , Created Date, Modified Date \n';
string finalstr = header ;
for(Account a: acclist)
{
       string recordString = '"'+a.id+'","'+a.Name+'","'+a.CreatedDate+'","'+a.LastModifiedDate +'"\n';
       finalstr = finalstr +recordString;
}
Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
blob csvBlob = Blob.valueOf(finalstr);
string csvname= 'Account.csv';
csvAttc.setFileName(csvname);
csvAttc.setBody(csvBlob);
Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
String[] toAddresses = new list<string> {'test@test.com'};
String subject ='Account CSV';
email.setSubject(subject);
email.setToAddresses( toAddresses );
email.setPlainTextBody('Account CSV ');
email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});

 

 

CevicheroCevichero

Awesome. Thanks for sharing the code!

Krishna1317Krishna1317

Hi,

 

Awesome. It worked for me. Can you please throw some light on any governor limits that you encountered if any?

Arash TeimoupoorArash Teimoupoor
whAT ARE THE STARTING LINES FOR THE APEX CLASS FOR THIS, ESPECIALLY IF THE QUERY IS GOING TO RETURN LARGE NUMBER OF RECORDS(50000 OR MORE)? CAN YOU ADD THE STARTING LINES TO THE SAMLE CODE AS WELL PLZ?
Niran N 4Niran N 4
Can anyone share code if the same is to be achieved using a Visualforce Component to render the file as .csv in a <messaging:attachement> tag in Visualforce email template....
Robin BarnwellRobin Barnwell
Please could I ask, rather than attaching the CSV to an email, could it be put onto an FTP server?
Nathan HincheyNathan Hinchey

@Robin Barnwell: relevant link (https://salesforce.stackexchange.com/questions/57731/how-to-send-and-receive-data-from-salesforce-to-a-ftp-server)

(NOTE: this thread is not about that question, though -- in the future you'll probably get better response just asking a new question)

Robin BarnwellRobin Barnwell
It's OK I solved this.  I used VF to create the required CSV page.  Then wrote a Selenium script to open the page and save to the required location.

The Salesforce product is really weak in this area.
Clayto63Clayto63
hi, this is great code. Could I ask for any advice on how to put this into a class to call once a day. thanks, ian