Apex Code Development You need to sign in to do that
Don't have an account?
sp08830 CSV upload to update existing cases
Hi,
I am trying to develop a Visual Force page to upload a csv to update existing cases. I know we can do this with a Data Loader but we need it on a VF page. we are planning to release this tool for a small group users in our org so they can update case details from using CSV they receive from various sources. Looking for pointers or a sample code. Any help would be greatly appreaciated. thanks,
There's already a related list for Attachments on Cases... you could save yourself the trouble by using that existing feature.
I am sorry not sure if I understood your response.. I need to update case details via CSV upload, not sure how the related list can help me.
Ah got it, from your original message it just sounded like you wanted to upload a csv file to a case record. I'll see if i can find anything
sorry about the confusion. thanks for your help.
I did something like this before, and I can tell you it was an awful process. Apex does not have a native CSV parser, so you probably have to make one yourself. Try to google for java code samples to help you out.
thanks Scott.
Does any one know an alternate process instead of CSV parsing? As I mentioned above I need this tool for users who are not technical enough to run a data loader process.
This is a CSV parser I got from: http://blog.nicocrm.com/2011/03/06/parse-csv-file-in-salesforce-apex/ with some slight modifications by me.
Note that CSV needs to be in UTF-8 if you are using accented characters in the input; a discussion of this (context was inbound email handler) is given here: http://boards.developerforce.com/t5/forums/forumtopicprintpage/board-id/apex/message-id/93677/print-single-message/true/page/1
public without sharing class CsvReader { // Csv Reader -- courtesy of http://blog.nicocrm.com/2011/03/06/parse-csv-file-in-salesforce-apex/ // // Usage: Call Constructor with entire Csv Stream then call readLine to get array of string tokens for each line; first row will be header row. readLine returns null at end of stream private String delim = ','; private String[] buffer; // the input data for entire CSV file // ------------------------------ // Constructors // ------------------------------ public CsvReader(String data){ this.buffer = (data == null ? new List<String>() : data.split('\n')); } public CsvReader(String data, String delim){ this.buffer = (data == null ? new List<String>() : data.split('\n')); this.delim = delim; } // ------------------------------- // readLine - returns array of csv tokens as strings; reads through buffer, removing from buffer as each line is located in toto. Return null if end of stream. // ------------------------------- public String[] readLine(){ if(this.buffer.size() == 0) return null; String line = this.buffer.remove(0); // grab first part of stream up to newline; remove from buffer String[] parts = new String[] {}; // result list of tokens for one line while(line != ''){ Integer next = 0; if(line.startsWith('"')){ line = line.substring(1); // strip initial " Integer quoteIndex = findQuote(line, 0); // Look for closing " on same line while(quoteIndex == -1){ // not found, we must have a newline within a quoted token if(buffer.size() == 0){ // EOT! quoteIndex = line.length(); } else { // grab the next line and look to see if closing " can be found Integer skip = line.length(); line += '\n' + this.buffer.remove(0); quoteIndex = findQuote(line, skip); } } // we have a quoted token, advance to comma next = quoteIndex + 1; parts.add(line.substring(0, quoteIndex).replace('""', '"')); } else { // non-quoted token, token end is at delim next = line.indexOf(this.delim, next); if(next == -1) next = line.length(); // NB in Substring, "endindex" is the index of the character AFTER the last index to get parts.add(line.substring(0, next)); } if(next == line.length() - 1) // case of a terminating comma. parts.add(''); line = next < line.length() ? line.substring(next+1) : ''; } if(parts.size() == 0) // empty string - we still want to return something... parts.add(''); return parts; } static private Pattern quotePattern = Pattern.compile('(?<!")"(?!")'); // ------------------------------------------------- // Helper: findQuote - find next quote " in line private Integer findQuote(String line, Integer skip){ Matcher m = quotePattern.matcher(line); m.region(skip, m.regionEnd()); if(!m.find()) return -1; return m.start(); } static testmethod void testSplitCsvSimple(){ String line = 'abc,efg'; String[] splitted = new CsvReader(line).readLine(); System.assertEquals(2, splitted.size()); System.assertEquals('efg', splitted[1]); System.assertEquals('abc', splitted[0]); } static testmethod void testSplitCsvEOL(){ String line = 'abc,'; String[] splitted = new CsvReader(line).readLine(); System.assertEquals(2, splitted.size()); System.assertEquals('', splitted[1]); System.assertEquals('abc', splitted[0]); } static testmethod void testSplitCsvQuotedSimple(){ String line = '"abc,def"'; String[] splitted = new CsvReader(line).readLine(); System.assertEquals('abc,def', splitted[0]); } static testmethod void testSplitCsvQuoted(){ String line = '"abc,def",gh"i,"jk""l",""'; String[] splitted = new CsvReader(line).readLine(); System.assertEquals(4, splitted.size()); System.assertEquals('gh"i', splitted[1]); System.assertEquals('abc,def', splitted[0]); System.assertEquals('jk"l', splitted[2]); System.assertEquals('', splitted[3]); } static testmethod void testSplitCsvQuotedWithNewLine(){ String line = '"abc,def\nBoo\nBoo",Test'; CsvReader reader = new CsvReader(line); String[] splitted = reader.readLine(); System.assertEquals('abc,def\nBoo\nBoo', splitted[0]); System.assertEquals('Test', splitted[1]); System.assertEquals(null, reader.readLine()); } static testmethod void testSplitCsvQuotedWithEOT(){ String line = '"abc,def\nBoo'; CsvReader reader = new CsvReader(line); String[] splitted = reader.readLine(); System.assertEquals('abc,def\nBoo', splitted[0]); System.assertEquals(null, reader.readLine()); } static testmethod void testTabDelim(){ String line = 'abc\tdef'; CsvReader reader = new CsvReader(line, '\t'); String[] splitted = reader.readLine(); System.assertEquals('abc', splitted[0]); } static testmethod void testSemiColonDelim(){ String line = 'abc;def;'; CsvReader reader = new CsvReader(line, ';'); String[] splitted = reader.readLine(); System.assertEquals('abc', splitted[0]); System.assertEquals('def', splitted[1]); } static testmethod void testEmptyStrings(){ String line = ',,,,'; CsvReader reader = new CsvReader(line); String[] splitted = reader.readLine(); System.assertEquals(5, splitted.size()); for(String s: splitted){ System.assertEquals('', s); } } // make sure we still get a result even if the source is empty... static testmethod void testEmptyString(){ String line = ''; CsvReader reader = new CsvReader(line); String[] splitted = reader.readLine(); System.assertEquals(1, splitted.size()); System.assertEquals('', splitted[0]); } }Hi
i hope this code will hepl you try this.
Actually i upload the csv file in my custom object employee you can change wt ever u r scenario
i am sending controller and vf page just try it.
<apex:page standardController="Employee__c" extensions="uploadEmployee" sidebar="false" showHeader="false" > <apex:form > <apex:tabPanel > <apex:tab label="Employee" reRender="pb" styleClass="font" id="cl" > <apex:pageBlock title="Upload employee details"> <apex:pageMessages /> <table> <tr> <td> Step 1 : Choose a file </td> </tr> <tr> <td style="left:20%;position:relative;"> <apex:inputFile value="{!contentFile}" fileName="{!filename}" ></apex:inputFile> </td> </tr> <tr> <td> Step 2 : Click the Add Button </td> <tr> </tr> <td style="left:20%;position:relative;"> <apex:commandButton value=" Add " action="{!ReadFile}"/> </td> </tr> </table> </apex:pageBlock> </apex:tab> </apex:tabPanel> </apex:form> </apex:page> controller public class uploadEmployee { public blob body{set;get;} public string namefile{set;get;} public string filename{set;get;} public Id empid{set;get;} public Employee__c emply{get;set;} public Id eid{set;get;} public boolean displayPopup {get; set;} /*public void closePopup() { displayPopup = false; } */ public void showPopup() { displayPopup = true; } public uploadEmployee(ApexPages.StandardController controller ) { emply=new Employee__c(); eid=ApexPages.currentPage().getParameters().get('emplyid'); } /* public pagereference cancel() { displayPopup = false; pagereference p= new pagereference('/'+memid); return p; } */ public void data() { for(Employee__c employee:[select name,Company_Name__c,Emp_Email__c,id,Emp_Id__c,Emp_Number__c from Employee__c]) { strset.add(employee.Emp_Email__c); offmap.put(employee.Emp_Email__c,employee.id); } } public Blob contentFile{get;set;} String[] filelines = new String[]{}; List<Account> accstoupload; list<Employee__c > updlist=new list<Employee__c>(); list<Employee__c> inslist=new list<Employee__c>(); set<string> strset=new set<string>(); Map<string,id> offmap=new map<string,id>(); public string testval{set;get;} public Pagereference ReadFile(){ updlist.clear(); inslist.clear(); data(); try{ nameFile=contentFile.toString(); filelines = nameFile.split('\n'); accstoupload = new List<Account>(); testval=string.valueof(strset); for (Integer i=1;i<filelines.size();i++) { String[] inputvalues = new String[]{}; inputvalues = filelines[i].split(','); //strset.clear(); // offmap.clear(); string name=inputvalues[2]; // inputvalues[2]=inputvalues[0].trim().toUppercase()+inputvalues[4].trim().toUppercase()+inputvalues[5].trim(); testval=string.valueof(strset); system.debug('@'+testval); if(strset.contains(inputvalues[2])) { Employee__c emp= new Employee__c(id=offmap.get(inputvalues[2])); if(inputvalues[0]!=null && inputvalues[0]!='') { emp.Name=inputvalues[0]; } if(inputvalues[1]!=null && inputvalues[1]!='') { emp.Emp_Number__c=inputvalues[1]; } if(inputvalues[2]!=null && inputvalues[2]!='') { emp.Emp_Email__c=inputvalues[2]; } if(inputvalues[3]!=null && inputvalues[3]!='') { emp.Emp_Id__c=inputvalues[3]; } if(inputvalues[4]!=null && inputvalues[4]!='') { emp.Company_Name__c=inputvalues[4]; } if(inputvalues[0]!=null && inputvalues[0]!='') { emp.Emp_City__c=inputvalues[5]; } updlist.add(emp); //update off; } else { system.debug('########'+inputvalues[2]); Employee__c emp= new Employee__c(id=offmap.get(inputvalues[4])); emp.Name=inputvalues[0]; emp.Emp_Number__c=inputvalues[1]; emp.Emp_Email__c=inputvalues[2]; emp.Emp_Id__c=inputvalues[3]; emp.Company_Name__c=inputvalues[4]; emp.Emp_City__c=inputvalues[5]; //insert off; inslist.add(emp); } } try { if(updlist.size()>0) { system.debug('########'+updlist); update updlist; } if(inslist.size()>0) { system.debug('########'+inslist); insert inslist; } attachment att=new attachment(); att.name=filename; att.body=contentFile; att.parentid=eid; if(namefile!=null) insert att; displayPopup = false; ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.Info,'suceess'); ApexPages.addMessage(errormsg); pagereference pageref=new pagereference('/apex/VF_Account_Detail?id='+eid+'&emplyid='+eid); pageref.setRedirect(True); return pageref; } catch (Exception e) { ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. Please check the template or try again later'); ApexPages.addMessage(errormsg); } return null; } catch(exception e){ ApexPages.Message erormsg = new ApexPages.Message(ApexPages.severity.Info,'You must a Choose a Csv file to Upload'); ApexPages.addMessage(erormsg); return null; } } }@Eric, @Venkatesh, thanks a lot for your responses. I will try and let you know.
I think I am almost there. just got stuck with this. Not sure why, but I get this error with code listed below. Any idea
line 34 is : case c= new case(id=updlist.get(inputvaluess[0]));
public Pagereference UpdateCases() { updlist.clear(); fetchcases(); try{ nameFile=contentFile.toString(); filelines = nameFile.split('\n'); updlist = new List<Case>(); Integer fileSiz = filelines.size(); for (Integer i=1;i<filelines.size();i++) { String[] inputvaluess = new String[]{}; String[] inputvalues = new String[]{}; inputvalues = filelines[i].split(','); case c= new case(id=updlist.get(inputvaluess[0])); updlist.add(c); update updlist; } } catch (DMLException e) { ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured. Some message '); ApexPages.addMessage(errormsg); } return null; }never mind. I was using the list instead of the map where I am fetching the data.
Does any one know how to handle commas within the data? Say a value in billing address "New York, NY" is parsed as 2 different columns.
The CSV parser that I posted handles commas inside of " " and also line breaks inside of " "