How to read csv with multiple quoted delimiters in single field?

user_3666302 0
Viewed 53

I'd like to be able to split a string which contains the delimiter quoted multiple times. Is there an argument to handle this type of string with the csv module? Or is there another way to process it?

text = '"a,b"-"c,d","a,b"-"c,d"'
next(csv.reader(StringIO(text), delimiter=",", quotechar='"', quoting=csv.QUOTE_NONE))

Expected output: ['"a,b"-"c,d"', '"a,b"-"c,d"']

Actual output: ['"a', 'b"-"c', 'd"', '"a', 'b"-"c', 'd"']

EDIT: The example above is simplified, but apparently too simplified as some comments provided solutions for the simplified version but not for the full version. Below is the actual data I want to process.

import csv
text = '"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-CD-0,"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-LS-0'
next(csv.reader(StringIO(text), delimiter=",", quotechar='"', quoting=csv.QUOTE_NONE))

Expected output

[
  '"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-CD-0',
  '"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-LS-0'
]

Actual output

[
  '"3-Amino-1',
  '2',
  '4-triazole"-text-0-"3-Amino-1',
  '2',
  '4-triazole"-CD-0','"3-Amino-1',
  '2', '4-triazole"-text-0-"3-Amino-1',
  '2',
  '4-triazole"-LS-0'
]
csv python
3 Answers

I'll only answer the first part of your question: there is no way to do this with the built-in csv module.

Looking at the CPython source code, quotechar option is only processed at the start of a field:

    case START_FIELD:
        /* expecting field */
        ...
        else if (c == dialect->quotechar &&
                 dialect->quoting != QUOTE_NONE) {
            /* start quoted field */
            self->state = IN_QUOTED_FIELD;
        }
        ...
        break;

Inside a field, there is no such check:

    case IN_FIELD:
        /* in unquoted field */
        if (c == '\n' || c == '\r' || c == '\0') {
            /* end of line - return [fields] */
            if (parse_save_field(self) < 0)
                return -1;
            self->state = (c == '\0' ? START_RECORD : EAT_CRNL);
        }
        else if (c == dialect->escapechar) {
            /* possible escaped character */
            self->state = ESCAPED_CHAR;
        }
        else if (c == dialect->delimiter) {
            /* save field - wait for new field */
            if (parse_save_field(self) < 0)
                return -1;
            self->state = START_FIELD;
        }
        else {
            /* normal character - save in field */
            if (parse_add_char(self, module_state, c) < 0)
                return -1;
        }
        break;

There is a check for quotechar while the parser is in the IN_QUOTED_FIELD state; however, upon encountering a quote, it goes back to the IN_FIELD state indicating we're inside an unquoted field. So this is possible:

>>> import csv
>>> import io
>>> print(next(csv.reader(io.StringIO('"a,b"cd,e'))))
['a,bcd', 'e']

But once the parser has reached the end of the initial quoted section, it will consider any subsequent quotes as part of the data. I don't know if this behaviour is to conform with any (written or unwritten) CSV specification, or if it's just a bug.

user_146370

The data is in a non-standard format and so any solution would need to be tested on the full dataset. A possible workaround could be to first replace ," characters with ;" and then simply split it on the ;. This could be done without using CSV or RE:

tests = [
    '"a,b"-"c,d","a,b"-"c,d"',
    '"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-CD-0,"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-LS-0',
]

for test in tests:
    row = test.replace(',"' , ';"').split(';')
    print(len(row), row)

Giving:

2 ['"a,b"-"c,d"', '"a,b"-"c,d"']
2 ['"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-CD-0', '"3-Amino-1,2,4-triazole"-text-0-"3-Amino-1,2,4-triazole"-LS-0'
user_49857330

If the structure is always the same with the comma sandwiched between an integer and the '"', you can use a regular expression:

import re

re.split('(?<=[0-9]),(?=")', text)
user_158730430
Related