+ Start a Discussion
Saad Ahmad 27Saad Ahmad 27 

Import Quote Line Item CSV using Apex & VisualForce Page

My requirement is to be able to import quote line items into a quote using a CSV. I have written the following code but I'm unable to get it working. The code shows no errors but when I try to import a CSV, it doesn work. Thank you in advance for any guidance you can provide:

APEX Code:
public class importDataFromCSVController {
	public Blob csvFileBody{get;set;}
	public string csvAsString{get;set;}
	public String[] csvFileLines{get;set;}
	public List<QuoteLineItem> quoteLineItemList{get;set;}
    public List<Quote> quoteNumberList{get;set;}
  	public importDataFromCSVController(){
    
    csvFileLines = new String[]{};
    quoteLineItemList = New List<QuoteLineItem>(); 
  	}
  
  	public void importCSVFile(){
      List<String> allProductCodes = new List<String>();
      List<String> allQuoteNumbers = new List<String>();
      Map<String, Id> quoteNumber = new Map<String, Id>();
      
      //Query for Products that have matching Product Code
      Map<String, Id> productCode = new Map<String, Id>();
      for (Product2 p : [SELECT Id, Name from Product2 where Name in :allProductCodes LIMIT 1]) {
          productCode.put(p.Name,p.Id);
      }
      
      //Query for Quotes that have matching Quote Number
      Map<String, Id> quoteNum = new Map<String, Id>();
        for (Quote q : [SELECT Id, QuoteNumber from Quote where QuoteNumber in :allQuoteNumbers LIMIT 1]) {
            quoteNum.put(q.QuoteNumber, q.Id);
      }
      
      // Set the Product Id for each Product Code  
      for(Integer j=0;j<quoteLineItemList.size();){
          QuoteLineItem qli1;
          Id Productid = productCode.get(allProductCodes[j]);
          if (Productid != null) {
              qli1.Product2.Id = Productid;
          }
      // Set the Quote Id for each Quote
          for (Integer z=0;z<quoteNumberList.size();){
              Quote q1;
              Id quoteNumberId = quoteNum.get(allQuoteNumbers[z]);
              if (quoteNumberId != null) {
                  q1.Id = quoteNumberId;
              }
          }    
      }
       try{
           csvAsString = csvFileBody.toString();
           csvFileLines = csvAsString.split('\n'); 
            
           for(Integer i=1;i<csvFileLines.size();i++){
               QuoteLineItem lineItem = new QuoteLineItem() ;
               string[] csvRecordData = csvFileLines[i].split(',');
               allQuoteNumbers.add(csvRecordData[0]);
               allProductCodes.add(csvRecordData[1]);
               lineItem.Quantity = decimal.valueof(csvRecordData[2]);
               lineItem.UnitPrice = decimal.valueof(csvRecordData[3]);
               lineItem.Global_Discount__c = decimal.valueof(csvRecordData[4]);                                                                    
               quoteLineItemList.add(lineItem);   
           }
        insert quoteLineItemList;
        }
        catch (Exception e)
        {
            ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. Please try importing the file again');
            ApexPages.addMessage(errorMessage);
        }  
  }
}

Visualforce Page Code
<apex:page controller="importDataFromCSVController">
    <apex:form >
        <apex:pagemessages />
        <apex:pageBlock >
            <apex:pageBlockSection columns="5"> 
                  <apex:inputFile value="{!csvFileBody}"  filename="{!csvAsString}"/>
                  <apex:commandButton value="Import Quote Line Items" action="{!importCSVFile}"/>
            </apex:pageBlockSection>
        </apex:pageBlock>
        <apex:pageBlock >
           <apex:pageblocktable value="{!quoteLineItemList}" var="acc">
              <apex:column value="{!acc.Quote}" /> 
              <apex:column value="{!acc.Product2}" />
              <apex:column value="{!acc.Quantity}" />
              <apex:column value="{!acc.UnitPrice}" />
              <apex:column value="{!acc.Global_Discount__c}" />
        </apex:pageblocktable>
     </apex:pageBlock>
   </apex:form>
</apex:page>

 
Best Answer chosen by Saad Ahmad 27
Ravi Dutt SharmaRavi Dutt Sharma
Line 47 seems to be the problem when you are converting the blob into String. I think you cannot directly convert the csv file from Blob to string using the toString method. Can you put a debug and check output of csvAsString
 
csvAsString = csvFileBody.toString();

Instead of converting using toString, try using below code:
 
String nameFile = blobToString( csvFileBody,'ISO-8859-1');

public static String blobToString(Blob input, String inCharset){
    String hex = EncodingUtil.convertToHex(input);
    System.assertEquals(0, hex.length() & 1);
    final Integer bytesCount = hex.length() >> 1;
    String[] bytes = new String[bytesCount];
    for(Integer i = 0; i < bytesCount; ++i)
        bytes[i] =  hex.mid(i << 1, 2);
    return EncodingUtil.urlDecode('%' + String.join(bytes, '%'), inCharset);
}


 

All Answers

Ravi Dutt SharmaRavi Dutt Sharma
Line 47 seems to be the problem when you are converting the blob into String. I think you cannot directly convert the csv file from Blob to string using the toString method. Can you put a debug and check output of csvAsString
 
csvAsString = csvFileBody.toString();

Instead of converting using toString, try using below code:
 
String nameFile = blobToString( csvFileBody,'ISO-8859-1');

public static String blobToString(Blob input, String inCharset){
    String hex = EncodingUtil.convertToHex(input);
    System.assertEquals(0, hex.length() & 1);
    final Integer bytesCount = hex.length() >> 1;
    String[] bytes = new String[bytesCount];
    for(Integer i = 0; i < bytesCount; ++i)
        bytes[i] =  hex.mid(i << 1, 2);
    return EncodingUtil.urlDecode('%' + String.join(bytes, '%'), inCharset);
}


 
This was selected as the best answer
Saad Ahmad 27Saad Ahmad 27
Thanks Ravi. I really appreciate your help.That did the trick and I don't get any error message anymore. Once I click the upload button, the page refreshes, however none of the products get added to the quote. I'm trying to see if I have missed something, however if you do spot something, please let me know! Thanks again for all your help.
Ravi Dutt SharmaRavi Dutt Sharma
Just to see if any error is coming from the backend, can you please remove the try catch block and then check.
Saad Ahmad 27Saad Ahmad 27
Thanks Ravi. Did that but don't really see any error. 
Ravi Dutt SharmaRavi Dutt Sharma
The QLI items that you are creating on line 51 - those records do not have a Quote Id associated to them. QuoteId is a mandatory field on QLI, you must associate a QLI to a quote before inserting it.
Saad Ahmad 27Saad Ahmad 27
Thanks! That makes sense. I will update the code and hopefully that should fix it. Thanks for your help Ravi. I’m pretty new to this so apologies for asking a lot of basic questions.
Ravi Dutt SharmaRavi Dutt Sharma
That's perfectly fine. Good luck.
Saad Ahmad 27Saad Ahmad 27
Hey Ravi - I've been trying for a couple days now but haven't been able to figure this out on how would I go about associating a Product2, PricebookEntry and Quote to QLI before inserting. Would you be able to help. Thanks a lot in advance.