+ Start a Discussion
PhantomPhantom 

How to export data to CSV file using Apex?

Hi all,

 

Im newbie in force.com, and i have a question, my question is that: i want to export list of object into CSV file using Apex. So please help me how to do this?

 

Thanks,

Best Answer chosen by Admin (Salesforce Developers) 
wongtwongt
Yes, set

contentType="text/plain"

 

All Answers

Nick34536345Nick34536345

Any reason you need to use Apex?

 

You can export data as CSV very easily, either using a report, or the data loader.

Message Edited by Nick34536345 on 01-28-2010 08:46 AM
sunil316sunil316

download Apex Data Loader.

there you get option to export and import the csv file. 

PhantomPhantom
Thanks for your support, i know Data Loader, but i want to develop my visualForce page to export data. Besides, Force.com has supported to export list of object data to txt file or not?
wongtwongt

I currently have the same requirements too. Try <apex:page ... contentType="text/csv#filename.csv" language="en-US">

 

(With the #filename.csv, it prompts the user to save/open the file. Otherwise, it loads into the browser as html.)

 

You'll also have to format the VF page as a clean csv text file, that means no pageblocktables or anything that might insert html formatting. For example:

 

 

<apex:page controller="csvController" cache="true" contentType="text/csv#filename.csv" language="en-US"> "Col A","Col B","Col C","Col D" <apex:repeat value="{!myList}" var="a"> "{!a.ColA}","{!a.ColB}","{!a.ColC}","{!a.ColD}" </apex:repeat> </apex:page>

 

 

 

PhantomPhantom

Thank Wongt,

 

And if customer want to export data object to text file, so Force.com can do that?

wongtwongt
Yes, set

contentType="text/plain"

 

This was selected as the best answer
PhantomPhantom

I have tried to do follow this code:

 

<apex:page controller="csvController" cache="true" contentType="text/csv#filename.csv" language="en-US">
"Col A","Col B","Col C","Col D"
<apex:repeat value="{!myList}" var="a">
"{!a.ColA}","{!a.ColB}","{!a.ColC}","{!a.ColD}"
</apex:repeat>
</apex:page>

 It work well when the data i selected < 1000, but when the data select > 1000, the system raise error: 

collection exceeds maximum size: 1001

because the limitation of List<SObject> that contains data. So please help me the solution to solve if in case the data i select > 1000? or have another way to export to CSV with the large data using Apex code?

 

Chirag MehtaChirag Mehta

With Spring'10 release there's no longer any limitation on List size. Give a try saving your apex with version 18.0

springeverspringever



With Spring'10 release there's no longer any limitation on List size. Give a try saving your apex with version 18.0

 

--->this method is not useful.when the data select > 1000, the system raise error.How can i do?

 

Regards,

krattankrattan

Hi,

 

Can you please tell me the workaround for exporting the csv. I am just unable to make it work

 

Thanks.

CodySechelskiSGCodySechelskiSG

You can use a list of lists and nested repeaters to get around the 1000 records in a list limit. Here's an example:

 

Controller

public class exportCSVController {

    public List<List<myClass>> myList {get;set;}
    
    public exportCSVController() {
        myList = new List<List<myClass>>();
        List<myClass> temp = new List<myClass>();
        
        for(Integer i = 0; i < 2500; i++){
            if(temp.size() < 1000){
                myClass m = new myClass();
                m.val1 = 'val1 ' + i;
                m.val2 = 'val2 ' + i;
                temp.add(m);
            }
            else{
                myList.add(temp);
                temp = new List<myClass>();
                myClass m = new myClass();
                m.val1 = 'val1 ' + i;
                m.val2 = 'val2 ' + i;
                temp.add(m);
            }
        }
        myList.add(temp);
    }
    

    public class myClass{
        public string val1 {get;set;}
        public string val2 {get;set;}
    }
}

 VisualForce Page:

<apex:page controller="exportCSVController" cache="true" contentType="text/csv#Export.csv" language="en-US">
    "Col A","Col B"
    <apex:repeat value="{!myList}" var="a">
        <apex:repeat value="{!a}" var="asub">
        "{!asub.val1}","{!asub.val2}"
        </apex:repeat>
    </apex:repeat>
</apex:page>

 This should allow you to have up to 10,00,000 records.

DNLZDNLZ

Is this generated CSV RFC4180-compliant ? And how can i insert \n or \r in the csv?

James_AdapxJames_Adapx

Why does the first column always have quotes in the field? How do you remove them? It does export to excel nicely..

 

 

    "Col A"Col B
        "val1 0"val2 0
        "val1 1"val2 1
        "val1 2"val2 2
        "val1 3"val2 3
Natavar GhodasaraNatavar Ghodasara
I had tried three methods. When i write in the page like contentType="application/vnd.ms-excel#ConsignmentSearchData.csv" or contentType="application/octet-stream#record.csv" or contentType="text/csv#filename.csv" it is giing me the code of the page in the cvs file.
Data Loader 5Data Loader 5
You may use a separate service for exporting data from Salesforce into CSV (https://skyvia.com/connectors/salesforce) - Skyvia. Besides it allows you rich functionality, and it's free.
sandeep madhavsandeep madhav
@Jame_Adapx: Did you got the solution??
David Roberts 4David Roberts 4

Here's an enhancement to Nishad Kallingal's solution at
https://success.salesforce.com/answers?id=9063A0000019RBmQAM

This example has a custom object on the account. I only wanted accounts that had custom objects so I filtered them in the controller.

[note the limit is in while testing the output to the screen. Put back the csv line when page is working]

Controller:
public with sharing class vwQuery2ExcelController {
 
    public List<Account> cs{get; set;}
    public vwQuery2ExcelController()
    {
        cs = new List<Account>();   
       for (Account c : [SELECT Account.id,Account.Name, (SELECT licence__c.Id, licence__c.Name FROM Account.Licences__r) FROM Account LIMIT 10])
       {   
               if (c.licences__r.isEmpty())   {}
               else 
               {cs.add(c);}
            //endif
       }
    }
    
}//vwQuery2ExcelController

vf Page:
<apex:page Controller="vwQuery2ExcelController" tabStyle="Account"  >
<!-- 
The following parameters have been removed from the apex:page line while testing.
Put back in to get the result exported...
readOnly="true" contentType="application/vnd.ms-excel#UserReport.xls" cache="true"
Report on a query and send to Excel -->
<!--from Nishad Kallingal -->
<!--https://success.salesforce.com/answers?id=9063A0000019RBmQAM -->
<!-- -->

 <apex:pageBlock title="Export Results" >
 
  <apex:pageBlockSection title="Accounts with custom object Licence__c" >
        <apex:pageBlockTable value="{!cs}" var="result">
             
            <apex:column value="{!result.ID}"/>
            <apex:column value="{!result.Name}"/>
            
               <apex:repeat var="lc" value="{!result.licences__r}">
               <apex:column value="{!lc.Name}"/>   
               </apex:repeat>            
            
        </apex:pageBlockTable>
        
         </apex:pageBlockSection>
         
    </apex:pageBlock>

</apex:page>
 

David Roberts 4David Roberts 4
There's a great solution for export...
Samuel Reyes in https://success.salesforce.com/answers?id=9063A0000019RBm
says use the code snippet at https://gist.github.com/DouglasCAyers/c733b60db61c290b95ff0047ab3d2432.
and https://douglascayers.com/2016/03/20/salesforce-easy-ways-to-export-data-as-csv/
Thanks to Samuel and Douglas!

Also, my query can be refined to only list accounts with contacts by using this format:
SELECT Account.Name, (SELECT Contact.Name FROM contacts) FROM Account WHERE Account.Id IN (SELECT Contact.accountId FROM Contact)
see: https://developer.salesforce.com/forums/?id=906F0000000Aiy4IAC and other answers.
 
David Nash 8David Nash 8
You can use server version of Excel Converter to convert to CSV using APEX. You can check it out here: https://www.coolutils.com/TotalExcelConverter
Deepika1007Deepika1007
Hello,
Please verify the steps to send Apex Result to Excel.
https://deepikamatam.blogspot.com/2019/07/export-apex-result-to-csv-file.html
 
David Roberts 4David Roberts 4
Use readOnly="true" contentType="application/vnd.ms-excel#UserReport.xls" cache="true" In your visualforce page header. Got this from Nishad Kallingal but I can’t find the link.
David Roberts 4David Roberts 4
Found it: https://success.salesforce.com/answers?id=9063A0000019RBmQAM
Nancy Everest --Nancy Everest --
Super helpful post that worked for me:
https://www.salesforcetutorial.com/how-to-export-data-in-csv-by-using-apex-code/