CODEWITHSUNDEEP
javaregexcsv
bpl3792
bpl3792

Reputation: 37

I need a regex to split thousdands in a CSV file

I have 2 Regex that I've tested so far but only do part of what I want them to...

Here's an example of the data I would like to split...

Since it's an upload from a CSV most likely if the number is 1000 or greater it's going to have a comma inside of the quotes. Problem I'm running into is that when I do value.split(',') it splits in between the quotes. I would like to have a regular expression do this instead of a bunch of for loops and if statements. Any help would be greatly appreciated.

(I'm using Apex so that's why it's ' and not ")

Upvotes: 0

Views: 309

Answers (3)

bpl3792
bpl3792

Reputation: 37

        for(String line : lines){    
        i++;

        if(skipFirst && i <= 1) continue;
        if(isBlank(line)) return error('Line ' + i + ' blank');
        pattern regex=pattern.compile(',(?=([^"]*"[^"]*")*[^"]*$)');


            cells=regex.split(line);
            string tmp0=cells.get(1).replace('"','');


        if(cells == null || cells.size() < 2) return error('Line ' + i + ' is either blank or contains only one cell');
        code = cells.get(0);
        if(code != null) code = code.trim();
        try{
            //If the amount is empty or null, assume it is 0
            if(cells.get(1) == null || cells.get(1) == ''){
                amount = 0;
            }
            else{
              if(!cells.get(1).contains('"')){
                    amount = Decimal.valueOf(cells.get(1));
              }else{
                    string tmp=cells.get(1).replace('"','');
                    amount = Decimal.valueOf(tmp.replace(',',''));
              }

            }
        }catch(System.TypeException e){
            return error('Line ' + i + ' contains invalid amount');
        }
        values.put(code,amount);

    }

This post was for posterity since I did figure out a solution inside of salesforce using a regular expression...it is however long and probably not necessary.

Upvotes: 0

Michael
Michael

Reputation: 35351

String input = "27254,\"1,144.16\"";
List<String> data = new ArrayList<String>();
boolean inQuotes = false;
boolean escaped = false;
StringBuilder buf = new StringBuilder();
for (int i = 0; i < input.length(); i++){
    char c = input.charAt(i);
    if (escaped){
        buf.append(c);
        escaped = false;
    } else if (c == '\\') {
        escaped = true;
    } else if (c == '"') {
        inQuotes = !inQuotes;
    } else if (c == ',' && !inQuotes){
        data.add(buf.toString());
        buf = new StringBuilder();
    } else {
        buf.append(c);
    }
}
data.add(buf.toString());

Upvotes: 1

Sjoerd
Sjoerd

Reputation: 75679

Don't use a regex, use a CSV parser.

Upvotes: 2

Related Questions