Read and Insert records from a CSV file - Using Visualforce

10:07 PM

The Apex Data Loader is always there when you want to insert records into Salesforce from a CSV file. But, just in case if you don't want your users to install the Apex Data Loader and learn how to use it, then here is a simple example which tells you how to do the same using Visualforce.

Click here to view the demo.

UPDATE:
  • Added special character support [Ex: French characters]
  • Added TEST class coverage

Step 1:

Download the template from here. Save the file in your desktop. Upload the file into Static Resources with the name "AccountUploadTemplate".

Step 2:

Create an Apex Class named "FileUploader". Paste the code below and save it.


Step 3:

Create a Visualforce Page named "UploadAccounts". Paste the code below and save it.

Step 4:

Download the CSV file from here for test method coverage. Upload the file with the name 'testMethodCSVUpload'

Step 5:
Create an Apex Class as shown below for test coverage.


Screenshot:

Some pointers:
  • You can use only the standard template. Because, that's how we have done the mapping to the columns in excel and the fields in Salesforce. You can modify the mapping and use your own template.
  • Allowing the user to choose his own mapping is possible i believe, but may be a bit complex.
  • Also, we use a CSV file. So, you may have to use additional criteria if your data values itself have a comma in them (For ex: Billing Street = 'Mumbai, India ') . This would cause problems because Mumbai and India would be considered as seperate values because of the comma in between them.

61 comments

  1. Replies
    1. Hi all. My requirement is How to read .csv file from the salesforce document folder to help with BatchApex Job and load into the salesforce org?
      Can anyone help me?

      Thanks
      Rajesh Kumar
      Salesforce Developer

      Delete
    2. Hi Rajesh,

      Did you find any solution.If yes could you please help me, even i have the same requirement.

      Thanks,
      Surekha

      Delete
  2. I'm going to give this a try, thanks.

    ReplyDelete
  3. Hey this is a great idea... I was working on something like this too... will share my code too

    ReplyDelete
  4. Its gr8....Thanks a lot for the code hint!

    ReplyDelete
  5. and the test class?

    ReplyDelete
  6. Yes, very cool but would you also please advise a test class (at least for the CSV upload part)?

    ReplyDelete
  7. Can write code to read csv file from common folder and insert into contact object?

    ReplyDelete
  8. To read from common folder and insert into Contact object you may not need this code.. You can simply schedule the Apex Data Loader to run in batch mode, you can schedule it to pick a file from a defined location and upload into salesforce

    ReplyDelete
  9. I implemented this and it was great. How did you write the test case?

    ReplyDelete
  10. I have to read the .csv file data and send it to the pdf. but in that process . i am able to send to toString() from .csv . but is in html form. so please send the steps to convert the .csv data in to the .pdf format using apex and visual force in sales force. please help for this task. u may mail me also kichepuri@gmail.com

    ReplyDelete
  11. Nice bit of code. What's the maximum number of records we can insert like this? It looks like it would get caught by the 10,000 DML statement limit which means you couldn't process more than 10,000 in a single file - is that right?

    ReplyDelete
  12. This is great! I just tried it out and it works perfectly. If only the Salesforce docs made it as clear what needs to be done as you have done. Thank you so much.

    ReplyDelete
  13. nice thanks it's is very usefull

    ReplyDelete
  14. Great. Thanks, its working perfectly.

    ReplyDelete
  15. Excellent! This works like a charm. Any way to get info on how to write a test method for it? I'm stuck at 19% and struggling. Thank again for the great write up.
    mstarr7@gmail.com

    ReplyDelete
  16. wow... this is a big help for me. :) Thanks!

    ReplyDelete
  17. Could you post a test method for this please. I am having difficulty getting one up to 75%. Thanks!

    ReplyDelete
  18. Hi,

    Me too getting no error message,it just doesn't insert.It loads the browser when i click upload file,and returns the same page.

    ReplyDelete
  19. i am also facing same pbm..Pls any one can help me..

    Thanks

    ReplyDelete
  20. Hi

    Nice work
    I am getting the error "BLOB is not a valid UTF-8 string" when the csv file contains french accents (eg é,è, etc...)

    Is there a solution to bypass this error?

    Thanks

    ReplyDelete
    Replies
    1. Hello...any workaround to solve this? i´m having the same problem here
      Cheers!

      Delete
  21. Hello
    Any change to publish the test class for this code?

    Thanks!

    ReplyDelete
  22. For everyone trying to develop a test method for this here's what I've used. It's not 100% coverage but should get you close. Mine runs at 80% for my application. Hope this helps

    ________________________________________________

    @IsTest(SeeAllData=true)
    public class FileUploader_TestMethod
    { /* public pageReference fileAccess(){
    Document lstDoc = [select id,name,Body from Document where name = 'test'];

    System.Debug('DOC NAME :: '+lstDoc.name);
    System.Debug('DOCBODY :: '+lstDoc.Body);


    return null;
    } */
    public static testMethod void ReadFile() {

    Document lstDoc = [select id,name,Body from Document where name = 'accttest'];

    // System.Debug('DOC NAME :: '+lstDoc.name);
    //System.Debug('DOCBODY :: '+lstDoc.Body);
    FileUploader file=new FileUploader ();
    file.fileAccess();
    Blob content= lstDoc.Body;
    file.contentFile = content;
    file.ReadFile();

    file.nameFile=content.toString();
    String[] filelines = new String[]{};
    List accstoupload;

    accstoupload = new List();
    for (Integer i=1;i<filelines.size();i++)
    {
    String[] inputvalues = new String[]{};
    inputvalues = filelines[i].split(',');
    Equipment__c a = new Equipment__c();
    a.Name = 'R1111111111';
    a.Receiver_S__c = 'S1111111111';
    a.Receiver_Model__c = '311';
    a.Programming__c = 'Basic';
    a.Channel__c = 'ESPN';
    a.Satellite__c = '72.7';
    a.Satellite_Channel__c = '100';
    a.Output_Channel__c = '140';
    a.RecordTypeId = '01260000000Luri';

    accstoupload.add(a);
    try{
    insert accstoupload;
    }
    catch (Exception e)
    {
    ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. Please check the template');
    ApexPages.addMessage(errormsg);
    }

    }
    }
    }

    ReplyDelete
  23. Thanks! How can I change the visualforce so that it'll redirect to the parent record?
    I created a custom object and added a lookup field to Account. I also added a custom button in the related list.

    ReplyDelete
  24. I Will try the Test class of csv File uploading . it should be 61% of my application. any help me.
    my test clas code is

    @isTest(SeeAllData=true)
    public class FileUploader_TestMethod

    {
    //public static Blob contentFile{get;set;}
    //public static string nameFiles{get;set;}
    //static String[] filelines = new String[]{};
    static Account ac;
    static List accstoupload=new List();

    public static testMethod void TestCase() {

    {
    Test.startTest();
    ac=new Account();ac.Name='rakuten12345';
    insert ac;
    String resourceName = 'Csvfiles';






    StaticResource defaultResource = [Select id,name,Body From StaticResource where Name='Csvfiles'];
    Blob content= defaultResource.Body;
    String myCSVFile = defaultResource .Body.toString();
    System.debug('myCSVFile = ' + myCSVFile);
    FBR_FileUploader file=new FBR_FileUploader();

    file.contentFile = content;
    file.ReadFile();
    file.getuploadedAccounts();



    file.nameFiles=content.toString();

    String nameFiles= content.toString();


    String[] filelines = nameFiles.split('\n');

    accstoupload=new List();

    for(Integer i=1;i<filelines.size();i++)
    {
    String[] inputvalues = new String[]{};
    inputvalues = filelines[i].split(',');
    WorkMonthly__c a = new WorkMonthly__c ();
    a.Name = 'rakuten';
    a.Unit__c= 'ten';
    a.Date__c= System.today();
    a.ImportCount__c= double.valueOf('15');
    a.Account_Name__c = ac.id;

    accstoupload.add(a);
    try{

    insert accstoupload;


    System.assertEquals(1,accstoupload.size());
    Test.stopTest();
    }
    catch (Exception e)
    {
    ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. Please check the template');
    ApexPages.addMessage(errormsg);
    }
    }
    }
    }
    }

    ReplyDelete
  25. Hi

    Nice work
    I am getting the error "BLOB is not a valid UTF-8 string" when the csv file contains japanese accents . i want to japanse word in my csv file data (eg: 'プラディープ' etc...)

    Is there a solution to bypass this japanese data?

    ReplyDelete
  26. Hi,

    Appreciate your help.

    but in this one more requirement i have, if you can help me in this also

    Requirement is " In Apex data loader/import wizard, it is giving "Log File(Success file/Error File)" so can we have things in this functionality also ?

    Thanks in advance

    ReplyDelete
  27. Does anyone know if there is a limit on number of records that can be uploaded? Also how about governor limits?

    ReplyDelete
  28. Does any one know how to tailor this script to update existing records, provided the input file contains the ID. Any help would be greatly apprecated.

    ReplyDelete
  29. what's the workaround for data that contains COMMAS ? My code is failing if data has commas.

    ReplyDelete
    Replies
    1. Me too.Let me know if you got solution.Thanks

      Delete
    2. can any one help with the code, to parse a csv having double quotes and commas

      Delete
  30. Thanks for this code - this solved a problem I was having. I used this for inserting an opportunity and matching product lines to load Point-of-Sales files from third parties. Code below if someone needs something similar. Please send me improvement suggestions if you have them, still learning how to write clean code.

    public class FileUploader
    {
    public string nameFile{get;set;}
    public Blob contentFile{get;set;}
    String[] filelines = new String[]{};
    List oppsToUpload;
    List olisToAdd;
    Set oppsAdded = new Set();
    List opptysToUpdate;
    String pbName = [SELECT id, Name FROM PriceBook2 WHERE Name = 'Registered Partner Pricing'].id;
    List opptysinserted = new List();

    public Pagereference ReadFile()
    {
    nameFile=contentFile.toString();
    filelines = nameFile.split('\n');
    oppsToupload= new List();

    for (Integer i=1;i();

    for(Integer i=1;i getUploadedOpptys()
    {
    if (oppsToupload!= NULL)
    if (oppsToupload.size() > 0)
    return oppsToupload;
    else
    return null;
    else
    return null;
    }
    }

    ReplyDelete
  31. Hi,
    Nice post.
    How to handle the below error through code :-

    "BLOB is not a valid UTF-8 String"

    Thanks

    ReplyDelete
    Replies
    1. I think, you were trying to upload .xlsx file instead of .CSV File.

      Please change the file to csv and try.

      Delete
  32. Hi,
    Its really helped me allot.
    Mate can you help me the same functionality for getting update the same records which we export with Id's in one column.

    ReplyDelete
  33. Hi
    How to upload excel file,reading the data from excel and how to split the fields in excel records
    .

    ReplyDelete
  34. Hi,

    How to check for redundant data in the uploaded .csv file and displaying it?

    ReplyDelete
  35. Hi, What if instead of the custom Account object, I have a custom object with fields accepting different values (i.e. Employee object with employeeName, phoneNumber, startDate, salaryLevel (function) and so on..). How would I read these from csv into apex. I tried it and gets error that I cant split the different data types. I can only split if they are all of String values.

    ReplyDelete
  36. How to handle the un inserted reocord file?if i upload again it has to insert.can u help me out

    ReplyDelete
  37. Nice Post admin..
    Here i need to insert and update through single file which may have new records and old records.. Here we need to check based on the CCNumber(custom field) in my Custom Object. So can anyone help me regarding this...........

    Thanks in advance....

    ReplyDelete
  38. Nice work!

    I have a question: Is there any limitation with the number of columns and rows? I tried to import a csv with 7 columns or more and does not work but if it has 6 columns it works perfecly.

    Appreciate your help.

    Thanks!

    ReplyDelete
  39. hi,
    if we import the more than 50000 records.then we got view state exception.how to solve this issue when we using your code for importing records

    ReplyDelete
  40. hi,
    this is prakash.
    i have a question from your above code,i used your code to import records from .csv file.it worked.but in my file, i increase records upto 50000.I got view state exception.please give the code to slove the above problem is more than 50000 records with sloving hte view state exception...

    thanks
    prakash.

    ReplyDelete
  41. Hi ,
    I am not able to find apex class and vf page code.
    please let me know code of vf page and class.

    Regards,
    Gyanender Singh

    ReplyDelete
  42. Still there are glitches while reading special char <™>

    ReplyDelete
  43. Absolute genius. Thank you so much. I'm using it as a template for a version that imports leads captured at an exhibition. I need to reformat the data so that Salesforce accepts it. Just perfect!

    ReplyDelete
  44. I used this method (thank you!) but found it didn't handle xlsx fields that included "newline". The Blob must have some indication that delimits lines other than the \n char. Anyone know how to do this? I had to pre-process my spreadsheet before saving to csv to remove the line feeds.

    ReplyDelete
  45. This is great! How can we use this to insert Opportunity Product Lines on an Opportunity. We want to include the VF in the opp page layout.

    ReplyDelete
  46. Has anyone adapted this code to work with Date fields or Lookup fields?

    When I get to Line 26, I get an error if I have a field type of Date ("Error: Compile Error: Illegal assignment from String to Date at line 26 column 21"). This is because 3 lines up it is referring to a String. So how would I do the same thing with multiple field types? Picklist, Date, Number, Lookup, Text.

    Thanks!

    ReplyDelete
  47. HI Guys,
    we use a CSV file. So, you may have to use additional criteria if your data values itself have a comma in them (For ex: Billing Street = 'Mumbai, India ') . This would cause problems because Mumbai and India would be considered as seperate values because of the comma in between them.
    How to resolve that issue?

    ReplyDelete
  48. Hi Rajesh,

    Even I have the same requirement.. did you find any solution.If yes could you please help me how to resolve.

    Thanks
    Rekha

    ReplyDelete
  49. I wanted to read the data combination of header and values.
    Jan-20 Feb-20
    ABC 6,716 6,716

    In system, i wanted to create 2 records for Product ABC i.e 1 record with Month ->Jan and Quantity and other record with Month ->Feb and Quantity.

    Any suggestions how we can do in Apex..

    ReplyDelete