Integration: Upload excel sheet’s data into salesforce


Some of my cool developer friend asked me, Is it possible to upload data from excels sheets into salesforce Objects using apex & visual-force pages? Hence I come up with a solution which, I going to describe in this post in details.

Feel free to post comments and ask anything related to it.

Import data from excel into salesforce:

  • No Need to upload file in salesforce org.
  • No Need to using any external on premise connectors.
  • No Need of any Salesforce Rest API or SOAP API.

Import Data to Salesforce from Excel Output

 

~ Apex Class Code (Controller) ~

/*
    Copyright (c) ajay-gupta.com
    All rights reserved.
    Redistribution and use in source and binary forms, with or without
    modification, are permitted provided that the following conditions
    are met:
    1. Redistributions of source code must retain the above copyright
       notice, this list of conditions and the following disclaimer.
    2. Redistributions in binary form must reproduce the above copyright
       notice, this list of conditions and the following disclaimer in the
       documentation and/or other materials provided with the distribution.
    3. The name of the author may not be used to endorse or promote products
       derived from this software without specific prior written permission.
    
    THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR
    IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
    OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
    IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, 
    INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
    NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
    DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
    THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
    (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
    THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/**
 * Author :     Ajay Gupta
 * ClassName :  uploadDataTosObject
 * Description : This class used to insert the data into salesforce.
 *               
 **/
public with sharing class uploadDataTosObject {
   
    List<Account> forInsert = new List<Account>();
    public Integer size {get;set;}
    String myString {get;set;}
    public Boolean status {get;set;}
    public String message {get;set;}
    
    public uploadDataTosObject(){
        myString= '';
        size = 0;
        status= false;
    }
     public PageReference parseData(){
        parseJSONString();
        if(forInsert.size()>0){
            try{
               
                size = forInsert.size();
                status = true;
                insert forInsert ;
                }catch(Exception e){
                    status = false;
                    message = e.getMessage();
            }
        }
        return Null;
    }
    
    public void parseJSONString() {
    String jsonStr = myString;
    forInsert = new List<Account>();
    JSONParser parser = JSON.createParser(jsonStr);
    while (parser.nextToken() != null) {
        if (parser.getCurrentToken() == JSONToken.START_ARRAY) {
            while (parser.nextToken() != null) {
                if (parser.getCurrentToken() == JSONToken.START_OBJECT) {
                    Account acc = (Account)parser.readValueAs(Account.class);
                    forInsert.add(acc);
                    System.debug('Debug'+ acc.active__c);
                    String s = JSON.serialize(acc);
                    system.debug('Serialized invoice: ' + s);
                    parser.skipChildren();
                    }
                }
            }
        }
    } 
}

 

~ Visualforce Page Code (View) ~

<!--
    Copyright (c) ajay-gupta.com
    All rights reserved.
    Redistribution and use in source and binary forms, with or without
    modification, are permitted provided that the following conditions
    are met:
    1. Redistributions of source code must retain the above copyright
       notice, this list of conditions and the following disclaimer.
    2. Redistributions in binary form must reproduce the above copyright
       notice, this list of conditions and the following disclaimer in the
       documentation and/or other materials provided with the distribution.
    3. The name of the author may not be used to endorse or promote products
       derived from this software without specific prior written permission.
    
    THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR
    IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
    OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
    IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, 
    INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
    NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
    DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
    THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
    (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
    THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
-->

<!--
    Author :     Ajay Gupta
    PageName :  importExcelData
-->
<apex:page showHeader="false" controller="uploadDataTosObject" apiVersion="36.0"> 
    <head>
        <title>IMPORT EXCEL DATA</title>
        <meta name="viewport" content="width=device-width, initial-scale=1"/>
        <link href="//netdna.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css"/>
        <style type="text/css">            
            .btn1,.btn1:hover{text-decoration:none}.upload-drop-zone{height:200px;border-width:2px;margin-bottom:20px;color:#ccc;border-style:dashed;border-color:#ccc;line-height:200px;text-align:center}body{background-color:#f3f5f6}.upload-drop-zone.drop{color:#222;border-color:#222}.tooltip-inner{text-align:center;-webkit-border-radius:0;-moz-border-radius:0;border-radius:4px;margin-bottom:6px;border:1px solid #ffd37f;background-color:#fffcdd;font-size:14px;color:#333;min-width:200px}.footer{left:0;bottom:0;width:100%;padding:2em;background-color:#f3f5f6;border-top:1px solid #fff;z-index:2;text-align:right;box-sizing:border-box;box-shadow:0 0 12px 0 rgba(0,0,0,.15);font-size:.9em}.btn1{background:#3498db;background-image:-webkit-linear-gradient(top,#3498db,#2980b9);background-image:-moz-linear-gradient(top,#3498db,#2980b9);background-image:-ms-linear-gradient(top,#3498db,#2980b9);background-image:-o-linear-gradient(top,#3498db,#2980b9);background-image:linear-gradient(to bottom,#3498db,#2980b9);-webkit-border-radius:4;-moz-border-radius:4;border-radius:4px;color:#fff;padding:10px 20px}.btn1:hover{background:#3cb0fd;background-image:-webkit-linear-gradient(top,#3cb0fd,#3498db);background-image:-moz-linear-gradient(top,#3cb0fd,#3498db);background-image:-ms-linear-gradient(top,#3cb0fd,#3498db);background-image:-o-linear-gradient(top,#3cb0fd,#3498db);background-image:linear-gradient(to bottom,#3cb0fd,#3498db)}
        </style>
        <script src="//code.jquery.com/jquery-1.10.2.min.js"></script>
        <script src="//netdna.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
    </head>
    <body>
        <br/>
        <br/>
        <div class="container">
            <div class ="row">            
                <div class="col-md-6">
                <h2>Excel Data Import Wizard <br/> <small>You can import up to 10,000 records at a time.</small></h2>
                </div>
                <div class="col-md-6">
                    <div class="pull-right">
                        <h5><strong>Ajay Gupta</strong></h5>
                        <br/>
                        <h5>contact@ajay-gupta.com</h5>
                        <p><a href= "http://www.ajay-gupta.com/contact/" target="_blank"> Write to us for any ideas or issues </a></p>
                    </div>
                </div>
            </div>
             <hr/>
           
          <div class="panel panel-default">
            <div class="panel-heading"><strong>Import Data</strong> <small>Excel file upload</small></div>
            <div class="panel-body">
              
              <h4>Select format to parse the excel into JSON or CSV format and parsed data will be used in Apex Class</h4>
              <div class = "pull-right">
                  <select name="format">
                    <option value="csv"> CSV</option>
                    <option value="json" selected="selected"> JSON</option>
                  </select>
              </div>
              <!-- Standar Form -->
              <br/> <br/>
              <h4>Select files from your computer
                <sup style= "font-size:13px;">
                    <a href="#" data-toggle="tooltip" title="Choose the file containing the data you want to import. You can import up to 10,000 records at a time."> ? </a>
                </sup>
            </h4>
                <div class="form-inline pull-right">
                  <div class="form-group">
                    <input type="file" name="xlfile" id="xlf"/>
                  </div>
                </div>
                <br/>
                <br/>
              <!-- Drop Zone -->
              <h4>Or drag and drop file below <sup ><a href="#" data-toggle="tooltip" title="Choose the file containing the data you want to import. You can import up to 10,000 records at a time."> ? </a></sup></h4>

                <br/>
                <br/>
                  <div class="upload-drop-zone" id="drop">
                    Just drag and drop any excel file here
                  </div>
                  <div style="margin:20px;">
        

                    <button class="btn1" onclick="initiateInsertion();" >Start Import</button>

                    </div>
              <div class = "panel panel-info">
                   <div class = "panel-heading">
                      <h3 class = "panel-title">Output</h3>
                   </div>
                   <apex:form >
                    <apex:actionFunction name="passStringToUploadDataTosObject" action="{!parseData}" rerender="output123">
                        <apex:param name="data" value="" assignTo="{!myString}" />
                    </apex:actionFunction>
                    
               
                    <apex:outputPanel id="output123" rendered="{!status}">
                    Status : {!IF(status,'true', 'false')}
                        <div class="alert alert-success fade in">
                    
                            <a href="#" class="close" data-dismiss="alert">×</a>
                    
                                <strong> Success! </strong>  <span class="label label-inverse"> {!size} </span> Rows inserted successfully.
                    
                        </div>
                  </apex:outputPanel>  
                    
                </apex:form>
                   <div class = "panel-body">
                      <pre id="out">
                      </pre>
                   </div>
                   
                   
                </div>
                
            </div>
          </div>
        </div> <!-- /container -->
        <div class="footer">
          <div class="container">
            <p class="text-muted text-center">All content copyright <a href="http://www.ajay-gupta.com/" target="_blank"> Ajay Gupta</a> © 2016. All Rights Reserved. </p>
          </div>
        </div>
        
                                

        <script src="https://cdnjs.cloudflare.com/ajax/libs/es5-shim/4.5.7/es5-shim.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>
        <script>
            $(document).ready(function(){
                $('[data-toggle="tooltip"]').tooltip();
            });
            </script>
        <script>
            var X = XLSX;
            
            function fixdata(data) {
                var o = "", l = 0, w = 10240;
                for(; l<data.byteLength/w; ++l) o+=String.fromCharCode.apply(null,new Uint8Array(data.slice(l*w,l*w+w)));
                o+=String.fromCharCode.apply(null, new Uint8Array(data.slice(l*w)));
                return o;
            }
            
            function get_radio_value( radioName ) {
                var radios = document.getElementsByName( radioName );
                for( var i = 0; i < radios.length; i++ ) {
                    if( radios[i].checked || radios.length === 1 ) {
                        return radios[i].value;
                    }
                }
            }
            
            function to_json(workbook) {
                var result = {};
                workbook.SheetNames.forEach(function(sheetName) {
                    var roa = X.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
                    if(roa.length > 0){
                        result[sheetName] = roa;
                    }
                });
                return result;
            }
            
            function to_csv(workbook) {
                var result = [];
                workbook.SheetNames.forEach(function(sheetName) {
                    var csv = X.utils.sheet_to_csv(workbook.Sheets[sheetName]);
                    if(csv.length > 0){
                        result.push("SHEET: " + sheetName);
                        result.push("");
                        result.push(csv);
                    }
                });
                return result.join("\n");
            }
            
            function to_formulae(workbook) {
                var result = [];
                workbook.SheetNames.forEach(function(sheetName) {
                    var formulae = X.utils.get_formulae(workbook.Sheets[sheetName]);
                    if(formulae.length > 0){
                        result.push("SHEET: " + sheetName);
                        result.push("");
                        result.push(formulae.join("\n"));
                    }
                });
                return result.join("\n");
            }
            
            function process_wb(wb) {
                var output = "";
                switch(get_radio_value("format")) {
                    case "json":
                        output = JSON.stringify(to_json(wb), 2, 2);
                        break;
                    case "form":
                        output = to_formulae(wb);
                        break;
                    default:
                    output = to_csv(wb);
                }
                
                 
                if(out.innerText === undefined) out.textContent = output;
                  else out.innerText = output;
                
                if(typeof console !== 'undefined') console.log("output", new Date());
            }
            
            function initiateInsertion(){
              
              //alert(document.getElementById('out').innerText);
              passStringToUploadDataTosObject(document.getElementById('out').innerText);
            
            }
            
            var drop = document.getElementById('drop');
            function handleDrop(e) {
                e.stopPropagation();
                e.preventDefault();
                
                var files = e.dataTransfer.files;
                var f = files[0];
                {
                    var reader = new FileReader();
                    var name = f.name;
                    reader.onload = function(e) {
                        var data = e.target.result;
                        var arr = fixdata(data);
                        var wb = X.read(btoa(arr), {type: 'base64'});
                        process_wb(wb);
                    };
                    reader.readAsArrayBuffer(f);
                }
            }
            
            function handleDragover(e) {
                e.stopPropagation();
                e.preventDefault();
                e.dataTransfer.dropEffect = 'copy';
            }
            
            if(drop.addEventListener) {
                drop.addEventListener('dragenter', handleDragover, false);
                drop.addEventListener('dragover', handleDragover, false);
                drop.addEventListener('drop', handleDrop, false);
            }
            
            var xlf = document.getElementById('xlf');
            function handleFile(e) {
                var files = e.target.files;
                var f = files[0];
                {
                    var reader = new FileReader();
                    var name = f.name;
                    reader.onload = function(e) {
                        var data = e.target.result;
                            var arr = fixdata(data);
                            var wb =  X.read(btoa(arr), {type: 'base64'});
                            process_wb(wb);
                    };
                    reader.readAsArrayBuffer(f);
                }
                return false;
            }
            
            if(xlf.addEventListener) xlf.addEventListener('change', handleFile, false);
            </script>
    </body>
</apex:page>

 

 

21 Comment

  1. krishan gopal says: Reply

    Hi Ajay,

    Subject: Excel Data Import Wizard : Not working

    I’m trying to use Excel Data Import Wizard but it’s not accepting any .CSV file on drag and drop and neither uploading any data in salesforce.

    Here I want to request to make it generic module by giving Object Picklist at visualforce page, first user will select right object and then upload related data.

    Thanks
    Krishan

  2. Krishan Gopal says: Reply

    Hi Ajay,
    Thanks for repling but my problem with the existing code is, Starts Import button is not working it’s not uploading any data in salesforce.
    And it’s not accepting any .CSV file on drag and drop also.

    My .csv file contain all the fields required to create an account in Salesforce, please help.

    Thanks
    Krishan

    1. Krishna can you please share your code & csv file data here?

  3. Krishan, I have checked your code and file that you have sent me through email, everything seems fine just cvs file that you have used to upload data. Change Account-CSV-Import-Format file format from cvs to excel then try. It will definitely work.

  4. Krishan Gopal says: Reply

    Hi Ajay, Now I change my excel format to .xlsx but code is still not working, output will show data and after Start Import button click no account is inserted in to the salesforce.

    1. Krishan, I have checked it is working, field name is incorrect, replace Owner with “OwnerId”. Please check below screenshots.

      Screenshot 1:

      After upload excel file

      Screenshot 2:

      After click on Start Import Button

  5. Krishan Gopal says: Reply

    Hi Ajay,
    Can you please share your Apex Class and Visualforce Page code over the mail kg.yadav@exicom.in you are using.

    Thanks
    Krishan

    1. I have shared the code over your email address.

    2. Hi Krishan Gopal,
      Even am facing similar issues like yours , have you sorted it out?.Can you Please mail me the template of the .xlsx sheet that you used to import records if you have sorted your issue.

      my email-id: anandj0792@yahoo.co.in

  6. Hi AJAY,

    Excellent post! I am still self-coaching coding process, your post is definitely a good study.

    I just met the same issue as Krishan. First, the CSV selection in the dropdown list is not recognize any .csv file, it only accepts .xml file, but not inserts the file. After upload my test file, hit Start Import, it has nothing happened. Just like your snapshot 1.

    Question:
    1. Is this app accept any unicode format? Like Asian characters in China or JP?
    2. Could you help check with this reported issue?

    Thanks,
    Helen

    1. Yes Helen, it does support the unicode formats.

  7. Krishan Gopal says: Reply

    Hi Ajay,
    How we lock the screen during data uploading, please suggest. So that user can’t interupt data upload and don’t make other upload request while one is already in progress.
    and it’s better if you can show a progress bar to the user with lock screen with percentage of data uploded in to the system.

    Thanks
    Krishan

  8. Hi Ajay ,

    When i created this page i am not getting myString value in controller ?

    1. Just change the Mystring variable to Public

  9. Hello Ajay,

    Nice post. I just want to make one modification .

    How to read an excel from specific row?

    Ex : In my scenario the excel that i use first three rows are merged . How can i overcome this situation ?

    Looking forward to hear from you

    Thank You,
    Vamshi

  10. Hello Ajay,

    Nice post. I just want to make one modification .

    How to read an excel from specific row?

    Ex : In my scenario the excel that i use first three rows are merged . How can i overcome this situation ?

    Looking forward to hear from you

    Thank You,
    kumar

  11. Great Job Ajay!!!

  12. Hi Ajay,

    I’m trying to use Excel Data Import Wizard but it’s not accepting any .CSV file on drag and drop and neither uploading(Start import) any data in salesforce.

    Can you please help me.

    Thanks

  13. Gyanender Singh says: Reply

    Hi Ajay,

    Using this code can we insert data directly in Account Object ?
    Please let me know the process.
    I have created one excel file and using this code import that excel file but not able to create account records.

    Thanks,
    Gyanender Singh

  14. Ajay,
    Any idea why the param value for the actionFunction is not passing over to the controller? I found where the rerender parameter on the tag needs to be set which it is, but the value isn’t getting over to the controller.

    Thanks.
    NB

  15. Resolved. Thanks.

Leave a Reply