0

Currently I had built a file upload functionality in Lightning component which accepts the CSV file, read it as text(Each row in CSV is split using \n and each column in the row is split using ,). Then each columns in each row are validated in Javascript and if everything is fine,the data is saved in the backend. While uploading the data, if the file contains , in its data then it is taken as another column. Eg: X-36936,SS-341487,11-WT,414081,"LENDERS, THE HACK",2-Manage,,,,,,,,,,,,12/1/2013,3150,,0,,Dealing,2018,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Here "LENDERS, THE HACK" is a single column but it is taken as 2 columns (LENDERS --> 1st column and THE HACK --> 2nd column).Could anyone please help me how to resolve this issue?

  • I've updated your tags for you. Please feel free to edit further if these are not correct. – sfdcfox Dec 17 '18 at 7:05
1

There are many good sample provided which you can use and refer. For eg:

/**
 * Used to read a delimited file.
 */
public class SSSCsvReader {
    private String delim = ',';
    // the input data
    private String[] buffer;

    public SSSCsvReader(String data){
        this.buffer = data.split('\n'); 
    }

    public SSSCsvReader(String data, String delim){
        this.buffer = data.split('\n'); 
        this.delim = delim;
    }

    /**
     * Read and parse next available line.  Return null if end of stream.
     */
    public String[] readLine(){
        if(buffer.size() == 0)
            return null;
        String line = this.buffer.remove(0);
        String[] parts = new String[] {};
        while(line != ''){
            Integer next = 0;
            if(line.startsWith('"')){
                line = line.substring(1); // strip initial
                Integer quoteIndex = findQuote(line, 0);
                while(quoteIndex == -1){
                    if(buffer.size() == 0){
                        // EOT!
                        quoteIndex = line.length();
                    } else { 
                        // grab the next line
                        Integer skip = line.length();
                        line += '\n' + this.buffer.remove(0);
                        quoteIndex = findQuote(line, skip);
                    }
                }
                // advance to comma
                next = quoteIndex + 1;
                parts.add(line.substring(0, quoteIndex).replace('""', '"'));
            } else {
                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('(?<!")"(?!")');
    /**
     * Find next quote in the 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();
    }
}

It follow approach that if your string contains comma it will be in double quote and then it parse that.

SalesForce Apex CSV Parser

  • 1
    Excellent example, but the question appears to be phrased to mean "parse in JavaScript", not "parse in Apex." Feel free to leave your answer here though, in case it helps. – sfdcfox Dec 17 '18 at 7:07
  • @sfdcfox Ahh didn't notice that. Thanks, I will leave the answer here for others. – Tushar Sharma Dec 17 '18 at 7:31
1

You need to implement a stateful parsing engine. This means that your code needs to know if you're in "escape mode" or not. Fields surrounded by quotes have either , or " in them, so when you're in escape mode, you need to treat the comma as a normal comma, and "" as a normal ". Also, the leading and trailing " are not part of the field data and should be excluded from the final result.

So, basically, if your state engine is at FIELD_START, and the first character is ", then you need to switch to escape mode. From that point, read each character. If it is not ", add it to the output buffer. If it is a ", see if the next character is also ". If so, add one " and skip the next character. Otherwise, you're at FIELD_END.

Ideally, you should read RFC 4180 if you're interested in the minimum support you'll need to implement. Alternatively, consider just using an open source CSV parser like this one. A quick search for JS CSV browser will yield a lot of results if you don't like this particular script.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.