Commas and double-quotes in CSV files

Question

I have a csv file with commas and double-quotes. Double quotes, however, are only used when there are commas inside the string. Like this:

AAA, BBB, "ACB,BCA", 123, "1,987"
DDC, CHA, ACH;HDC, "2,34", 192

I don't want to parse the commas in the double quotes and would like to have the following output:

x1    x2    x3       x4    x5
AAA   BBB   ACB,BCA  123   1,987
DDC   CHA   ACH;HDC  2,34  192

In short, ignore the commas for values with double-quotes and consider the commas for values without double-quotes.

I am using this to merge multiple CSV files into a single CSV file, while adding some columns referring to the file name:

with open(outfile, "wb") as outfile:
    writer = None
    for input_filename in filenames:
        with open(input_filename, "rb") as infile:
            reader = csv.DictReader(infile, quotechar='"', delimiter=',')
            if writer is None:
                field_names = ["index1"] + ["index2"] + reader.fieldnames
                writer = csv.DictWriter(outfile, field_names)
                writer.writeheader()
            for row in reader:
                row["index1"] = input_filename[1:10]
                row["index2"] = input_filename[10:20]
                writer.writerow(row)

My specific solution to reading the CSVs is:

csv.DictReader(infile, quotechar='"', delimiter=',')

but that clearly does not work.

Any suggestions?

EDIT: a better example below:

the csv file I'm trying to open is structured like this:

x 1,x 2,x 3,x 4,x5 AAA, “BB,B”, CCC, DDD, EEE AA1, B;B2, CC3, DD4, EE5

The parsers I've tried so far, csv.DictReader, pd.read_csv or csv.reader seem to read the ";" in B;B2 as a row breaker which messes all the following columns.

The following code solves the "BB,B" problem, but still breaks B;B2 into a new row

csv.reader(fileObject, quotechar='"', delimiter=',',
             quoting=csv.QUOTE_ALL, skipinitialspace=True)

"that clearly does not work" - doesn't it? What does it do instead? DictReader requires headers, have you tried providing those? Otherwise you'd get only one row, {'AAA': 'DDC', ' "ACB': ' ACH;HDC', '987"': None, ' BBB': ' CHA', 'BCA"': ' "2', ' 123': '34"', ' "1': ' 192'}, from that input. — jonrsharpe
I think fieldname is optional for the reader, but as I write it places most of the cases into the right columns. Except the ones in x3 column. It fits the ""ACB,BCA" into x3, but skips to row when it sees ACH;HDC. — tan
it is optional if the first row is the headers; you didn't show that in your example. Could you edit to give a minimal reproducible example; for example, just printing the read rows is probably sufficient without showing the details of the output writer. — jonrsharpe

Sagar Thatte Sagar Thatte · Accepted Answer · 2017-05-01T11:28:38

The below code snippet still worked for me and did not break on the ';' symbol

csv.DictReader(fileObject, quotechar='"', delimiter=',',
                 quoting=csv.QUOTE_ALL, skipinitialspace=True)

Has worked for me.

This is the output I received:

{'x1': 'AAA', 'x2': 'BBB', 'x4': '123', 'x5': '1,987', 'x3': 'ACB,BCA'}
{'x1': 'DDC', 'x2': 'CHA', 'x4': '2,34', 'x5': '192', 'x3': 'ACH;HDC'}

Commas and double-quotes in CSV files

1 Answers