+ Start a Discussion
bvramkumarbvramkumar 

Is processing a csv file possible in Apex?

Hi Pals,

 

Is it possible to parse/process file uploaded( for e.g. a .csv file) in apex and and fetch records? From what I know, when a file is uploaded from suppose a VF page, we will receive a Blob of that file. But there is no mechanism as such to parse that Blob and see what data is exactly is inside it. I know this I/O processing can be easily done in .Net/Java. But usually a .Net/Java solutions is not what a customer expects because of the extra hosting/costing/whatever the other reasons. So, I want to know if this possible in Apex at all ? I searched boards and also IdeaExchange but did not find any confident answer. Any help is very much appreciated.

 

Thanks

Vishnu

Best Answer chosen by Admin (Salesforce Developers) 
Damien_Damien_

Here is a method we use to help parse .csv files.

 

public static String[] processCSVFile(String fileName)
{
   StaticResource sr = [Select  s.Name, s.Id, s.Body From StaticResource s  where name =:fileName];
   blob tempBlob = sr.Body;
   String tempString = tempBlob.toString();
   tempString = tempSTring.replace('"', '');
   String[] stringArray = tempString.split('\n');
   return stringArray;
}

 

All Answers

Damien_Damien_

Here is a method we use to help parse .csv files.

 

public static String[] processCSVFile(String fileName)
{
   StaticResource sr = [Select  s.Name, s.Id, s.Body From StaticResource s  where name =:fileName];
   blob tempBlob = sr.Body;
   String tempString = tempBlob.toString();
   tempString = tempSTring.replace('"', '');
   String[] stringArray = tempString.split('\n');
   return stringArray;
}

 

This was selected as the best answer
cropzimcropzim

Here's another CSV parser that I use - http://blog.nicocrm.com/2011/03/06/parse-csv-file-in-salesforce-apex/ as it also handles embedded line breaks within a CSV field.  Works great and can be used when receiving CSV files from non-US locations that use delimiters other than comma (like semi-colons, tabs, pipes, ...)

bvramkumarbvramkumar

Thanks @Damien_. I thought same but did not implement that. I think I should try it. Thanks again!

bvramkumarbvramkumar

Thanks for the response @cropzim. The url you shared does not show any article though.

bvramkumarbvramkumar

The approach suggested by @Damien worked. But there is still an issue. It parses some csv files well but for some files it gives an error saying BLOB is not a valid UTF-8 string. I am looking for a way to resolve it. probably to look for UTF-8 decoding of blob.

cropzimcropzim
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 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]);
  }
}