You need to sign in to do that
Don't have an account?

How can I export a report as a CSV (ideally scheduled to email or FTP)
I need to export a report as a CSV to an FTP site or email it on a daily or weekly basis.
I have a Report. I need to get the report out of Salesforce somehow into CSV format.
Ideally, this would be scheduled.
I'm a developer so I can do developer stuff. However, I don't know APEX and I don't know VisualForce. I can get someone to do that stuff if we need to.
Thanks!
Here's an example schedulable class that might be used to email a file. Note that the last five lines need to be ignored or stripped; I'll leave this as an exercise to the reader.
You could also build a SOQL query, execute it, then build the CSV yourself, but that means the system is hard-coded and less flexible. Of course, you could build an interactive configuration (perhaps by the use of a custom object or custom settings), which would help work around that particular limitation of flexibility, but that is by far more complex.
All Answers
There's two ways you can accomplish this:
1) Apex Code. This is ideal, because you can have the code run weekly, scheduled inside your salesforce.com instance. No visualforce necessary in this case, because the results could be emailed or submitted to an HTTP server (not FTP, because Apex Code can't yet do that).
2) Export from salesforce.com remotely. Set up a cron job on the server to extract the data. Have the script/program log in programmatically to salesforce.com (API should be okay, or just use the normal login.salesforce.com portal). Then, call your report using the following HTTP request:
A C, C++, Perl, or PHP programmer should be able to accomplish this in just a few lines of code. You could even use a shell script with curl or some other utility. At that point, it's just a matter of setting up a cron job. The downside here is you would need to validate that the server is up and accepting requests, and delay execution until such time (i.e. maintenance).
Both sides have some pros and cons, but I think that a scheduled Apex Code class would be preferential to an external pull mechanism; you don't have to worry about logging in, sessions expiring, passwords changing, or most any other problem associated with external integrations.
Hi
I just came across this post while researching an issue with scheduled emailed reports. Could you give a bit more info on how you would implement option #1 - i.e. using Apex to extract data from a report and put into CSV. As far as I know this isn't possible, but I'm hoping you know something I don't!
Thanks
Sam
Here's an example schedulable class that might be used to email a file. Note that the last five lines need to be ignored or stripped; I'll leave this as an exercise to the reader.
You could also build a SOQL query, execute it, then build the CSV yourself, but that means the system is hard-coded and less flexible. Of course, you could build an interactive configuration (perhaps by the use of a custom object or custom settings), which would help work around that particular limitation of flexibility, but that is by far more complex.
Awesome, the ?csv=1 parameter is a new discovery for me!
Thanks very much, this will be extremely useful.
Sam
I am amazed how it solved your problem?
getContent() method isn't supported by Apex scheduler !!!
Please let me know how it solved the problem? I am running into same, so...
Great Solution !! Really helped me I only require the CSV part and CSV=1 worked like wonders for me.. Thanks for sharing.. Much appreciated...
Yes you are right, it cannot be done for Scheduled Apex.
Can you please let me know if you have been able to achieve it? Any info will be appreciated
If I run these code through Developer console I'm able to recieve attachment properly.
i.e.
If I schedule this class I'm recieving the attachment as like this.
Note: A report is there in my org with id 00O90000008euMX
Please help..
Thanks,
Naveen
I tried this code, but the report that is sent in email is empty.
A report is there in the org with the ID
When I run the report on the instance, it has some data as a result. Not sure what am I missing.
Thanks
Jyoti
Best,
Sam S.
"Scheduler: failed to execute scheduled job: jobId: 7071100000glzc3, class: common.apex.async.AsyncApexJobObject, reason: Callout from scheduled Apex not supported."
Any ideas on how to modify this code so it works after the critical updates are activated on January 8th? Appreciate any help!
Custom Object
Label: Exporter
Object Name: Exporter__c
Record Lable: Exporter Name
Record Data Type: Text
Custom Object Fields
Attachment_Name__c - Text(255)
Email_Body__c - Text(255)
Email_Recipients__c - Long Text Area(1000)
Email_Subject__c - Text(255)
Frequency__c - Picklist (Weekly, Monthly)
Monthly_Day__c - Picklist (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, last)
Report_ID__c - Text(18)
Once you've created the custom object, you can then create a custom Tab to access the records.
Apex Class
Apex Test Class
You'll need to schedule the Apex Class as a batch job - I would recommend setting the batch job to run every day, regardless of what business requirements you have around the schedule. Your administrators can then create new Exporter records and set the exact schedule they want from there. Note that the Attachment Name should include .csv at the end and the Email Recipients should be on one line separated by commas.
Do I need it if I have a unique Report_Name field in the Exporter object and use that instead?
When creating a new Exporter record, the Report ID field should be filled in the with ID for the report you want to auto-run in Salesforce. To find the report's ID, open the report in Salesforce, copy the last bit from the URL, and paste that portion into the Report ID field on your new Exporter record.
For example, say the URL for your report is below. The report ID is the last portion in bold.
https://na12.salesforce.com/00OU0000002CuN3
thumps up! ;) i simply copy paste your code for my developer edition ^^ but i didnt receive an Email. Our System is "german" .. maybe that is the problem? maybe the time code ? In your Example i should get the report at 00:00 am right ?
I'd first verify it's not your email deliverablility settings. Go to Setup > Email Administration > Deliverability and verif that the Access Level is set to All email. I usually forget to turn this on myself, so hopefully that's the issue.
I am facing an issue that when an email is received, I am unable to open the attachment - the attachment doesn't seem to be a csv file. Any idea?
And, I would recommend to use the sendEmail out of the loop - just for the sake if you have more than 1 record to be iterated.
I tried your solution with the custom object and the scheduled apex class, on execution of the scheduled job i'm getting the following error "Callout from scheduled Apex not supported." from the getContent() call.
Is there any workaround for this?
But definitely you can try Queueable Apex: More Than an @future that will solve your problem.
https://developer.salesforce.com/blogs/developer-relations/2015/05/queueable-apex-future.html
Hope this helps.
Donot forget to mark the solution as best answer as it helps others !
Please advise. Thanks!
I have created an admin managable custom object for this. Without the custom object, a developer would need to go into the code and update any of the items, such as the report ID, email subject, or email recipients. With the custom object added, an admin just needs to edit a record.
Custom Object
Label: Exporter
Object Name: Exporter__c
Record Lable: Exporter Name
Record Data Type: Text
Custom Object Fields
Attachment_Name__c - Text(255)
Email_Body__c - Text(255)
Email_Recipients__c - Long Text Area(1000)
Email_Subject__c - Text(255)
Frequency__c - Picklist (Weekly, Monthly)
Last_Ran__c - Date/Time
Monthly_Day__c - Picklist (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, last)
Report_ID__c - Text(18)
Once you've created the custom object, you can then create a custom Tab to access the records.
Apex Class
Apex Test Class
You'll need to schedule the Apex Class as a batch job - I would recommend setting the batch job to run every day, regardless of what business requirements you have around the schedule. Your administrators can then create new Exporter records and set the exact schedule they want from there. Note that the Attachment Name should include .csv at the end and the Email Recipients should be on one line separated by commas.
Have you tried running a debug log for yourself when the scheduler is set to run? Hopefully that should give you some insight into what's going wrong. I would also check to verify both the Frequency and either the Weekly Days or Monthly Day fields are filled in. If any are blank, the code won't pick up on the Export record.
If you're still having trouble, paste in the debug log from the scheduler and I can take a look.
OrgWideEmailAddress[] owea = [select Id from OrgWideEmailAddress where Address = 'salesforce@xyz.com'];
email.setOrgWideEmailAddressID(owea.get(0).id);
You'd also need to schedule the batch job to run every hour one the backend. Note that Salesforce limits each org to a maximum of 25 batch jobs, so you may want to do one every 3 or 4 hours and then update your Time picklist to match.
This scheduler works great but now I am working on enhancing this feature to NOT send an email if there is no record in the report. Has anyone already done this ?
TIA
1. Create an Integer custom field in the Exporter__c object that holds the number of lines in the csv when there is nothing to report. Then count the number of lines in the csv blob and if they match do not send the email.
2. Create a long textarea field in the Explorer__c object that holds a SOQL query. It would imitate the report type and filters/ranges of the report. Run the query and if it returns zero rows do not send the email.
On reading more about the reportResults class - https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_analytics_report_data.htm. I used something as below -
// default the value to false so that we send the email for every report
boolean doNotRun = false;
// get the report information from the exporter object and then find the metadata information
Reports.reportResults results = Reports.ReportManager.runReport(e.Report_ID__c);
//Reports.ReportMetadata rm = results.getReportMetadata();
system.debug('report name --- '+e.Name);
Reports.ReportFactWithSummaries factDetails =(Reports.ReportFactWithSummaries)results.getFactMap().get('T!T');
system.debug('in the if condition -- '+factDetails.getAggregates()[0].getLabel());
// check the value of the first aggregate. this will be zero if the aggegate is on Record Count or will be 0.00 if aggregated on the dollar amount
if(factDetails.getAggregates()[0].getLabel() == '0' || factDetails.getAggregates()[0].getLabel() == '0.00'){
doNotRun = true;
}
If you have a user working in Lightning Experience, you have to append the URL Parameter isdtp=p1 to the report URL to avoid lightning redirection, which results in an html redirect page output instead of the report results.
It should be trivial to join all the columns in a line back into a string but pipe delimited.
Similarly it's simple to convert between blob and string.
Hi Great people,
Just a small question , on code give by kory108 is it possible to attach multiple reports as attachments in same email
eg: all reports in a "Single Report folder" need to be send as attachments in single email .
I have a similar requirement and the solution which kory108 gave is very much helpful except it is working for single report ,
Can anyone help in this regards ? , Suggestions are also most welcome.
@Sulabh-how did you manage to extract csv file type?
@Colin-thanks for posting update on how to export to xls file type! Thinking I will try this to determine impact
I have implemented your code in my org its working fine when i scheduling the batch class its throwing error Too many callouts
below line is throwing
Blob content = report.getContent();
attachment.setBody(content);
Any thing i am doing wrong
Thanks & Regards,
satya
we are facing one problem when user is reciving the email thier the owner name is displaying but mail address it showing who has scduled the batch class.
The user should receive the mail from the record owner with his/her mail address
any body has any thoughts how to reslove this problem.
Thanx in advance
satya
Does anyone know what's going on? We have enabled Lightning for some but not all users recently, hoever the users that got this error are not currently LEX enabled. Not sure if this matters.
I haven't taken the time yet to re-write the code to support Lightning as we have an integration user that we have permanently on Classic.
Hopefully this is of some help.
For the API call to work you will also have to create a Remote Site Setting for your Salesforce instance.
I am completely new to development. I have the same requirement a report on a scheduled day shold be emailed in csv format
I followed your steps created an object, tab and then created an apex class. I am getting the below error
Email_Recipients__c, Frequency__c, Weekly_Days__c, Monthly_Day__c,
^
ERROR at Row:1:Column:120
No such column 'Weekly_Days__c' on entity 'Exporter__c'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.
Have you created this Weekly Days custom filed in Exporter custom object?
Weekly_Days__c - Multi-Select Picklist(Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
So i have a report generated from an custom object, question
1) How can i repurpose this code, should i create the fields as you have created in ExPLOrer object or even though i havea custome object i need to send the report on i should create another object with the above fields
2) Should i be creating a class and test class and also run the job, in that case what value for dates should i out if i wan to check to getthe mail immeidiatly
please help.
Create a new custom object with above mentioned field ,you can use @kory's apex class
Regards,
satya
please advice
also why would i query on user when i am sedning an email to non salesforce user please guide?
You should create the your report and the Exporter__c custom object in your production org. Then create a developer sandbox to create the same apex class and test class as Kory108 gives (Exporter and TestExporter). In the apex test class put your own report id into it, nothing else needs changing.
Make an outbound changeset in the sandbox and put the two apex classes in it, deploy it to the production org. (you need to amend Deployment Settings to accept changesets from the sandbox).
Once deployed in the production org. go to the Exporter tab and create a new Exporter record similar to Kory108 screenshot, put the report id, recipient email, day(s) of week to run etc.
In the production org. go into setup, find the Apex Classes page, then click the Schedule Apex button and schedule the Exporter apex class to run daily at your preferred time.
Create a custom object as well :-
I used your code :-
Class :-
I schedule the job , its run successfully
Im able to get mail , in attachment no data.
EXcel sheet Body :-
Why im not getting data in CSV file .what mistake im doing it ,Please help .Thanks In advance
This error coming in prod right ? I assume this error will not come every time if you schedule i mean to say some time you will receive the excepted attchment i have faced this type of issue in my production org
*************************************
ApexPages.PageReference report = new ApexPages.PageReference('/' + e.Report_ID__c + '?csv=1');
Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
attachment.setFileName(e.Attachment_Name__c+'.csv');
Blob content = Test.isRunningTest() ? Blob.valueOf('UNIT.TEST') : report.getContent();
{
attachment.setBody(report.getContent());
attachment.setBody(Blob.valueof(report.getContent().toString()));
attachment.setContentType('text/csv');
************************************
I suspect that the use of PageReference.getContent() to set the body of the attachment is causing the issue. Generally speaking PageReference is used in the visualforce page context emitted to a browser and this doesn't appear to be a valid use case for PageReference.
As an alternative, I recommend to create a visualforce email template and try to create the email by referencing the template instead.
> Visualforce Template Creation: https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_compref_messaging_emailTemplate.htm
> Referencing email templates in apex: https://help.salesforce.com/articleView?id=000188017&type=1
Let us know if you have any query .
We are able to export the report even in Lightning using the API but while exporting the xlxs file also contains the report name and all the filter criteria conditions as a header. Is there any way to remove them and display only the data in the report.
Thanks
Mallikharjun
https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_download_excel.htm (https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_download_excel.htm" target="_blank)
I am not aware of any parameters that can be added.
does this work for lightning now?
I need some inofrmation,Could we schedule the report for import into excel(csv) from salesforce at specific time.?
Thanks.
I believe, the timing is controlled by the Scheduled Apex Job, so it would be the same for all reports that are ran by this class.
Quote Fabien - "In additon to best answer by sfdcfox:
If you have a user working in Lightning Experience, you have to append the URL Parameter isdtp=p1 to the report URL to avoid lightning redirection, which results in an html redirect page output instead of the report results."
The problem it generates is solved, adding the following values to the URL:
where should you replace "REPORT_ID" for the ID of you report
So I am wondering being able to use this functionality to be able to run a report based off the user clicking a custom button, that the report would then be run, and generate the csv drop out to be placed on a secure external URL for further prcessing.
The use case is to be able to export all relative client investment policies, Related to a singular client, to an external reporting tool, for disbursement of the client.
Would the apex coding be able to facilitate such a condition?
we are looking for it
ApexPages.PageReference report = new ApexPages.PageReference('/' + e.Report_ID__c + '?excel=1');
Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();
attachment.setFileName(e.Attachment_Name__c+'.xls');
Thanksin Advance
So you can get your Salesforce reports to CSV format and then export them to FTP (or another file storage). Follow this link to learn more: https://skyvia.com/data-integration/integrate-salesforce-ftp