Handling unwanted (standalone) double quotes in a .csv - Python

user_8340742 0
Viewed 476

I am trying to read an incorrectly formatted .csv file using Python's csv module. The messy CSV looks like this:

"name","age","place","date"
"Jack","23","perth, australia","12aug
"Jackie","44","delhi, india","9dec
"Neel","12","austin, texas","1aug
"David","77","fresno, ca","21june

You'll notice that the second double quote is missing at the end of each row (the date column).

When I try to trivially read in this file using the following code, I get:

import csv
import os
temp = csv.reader(open('/Desktop/test csv/quote_test.csv', "r"), delimiter=',')
for row in temp:
    print(row)

['name', 'age', 'place', 'date']
['Jack', '23', 'perth, australia', '12aug\nJackie"', '44', 'delhi, india', '9dec\nNeel"', '12', 'austin, texas', '1aug\nDavid"', '77', 'fresno, ca', '21june']

Which was expected because it reads the last element of the current row and the first element of the next row (until it finds a ", matching the last ,") as one single entry - skipping all newline chars.

My question:- Is there a better way to deal with such messy csv's in Python, where we can get at least the right number of expected columns for every row (that is, in this case, is there a way to get python to still account for the newline character), and then deal with entries like <"12aug>, <"9dec> etc. separately? If not, what other ways can one make better sense of such data using python?

csv validation python-3.x python
3 Answers

You could first check if the lines end with " before passing the data to the csv reader, and append an " if it doesn't exist:

import csv
import os

with open('/Desktop/test csv/quote_test.csv', "r") as f:
    data = f.read().splitlines()
for i, line in enumerate(data):
    if not line.endswith('"'):
        data[i] = line + '"'      
data = "\n".join(data)
       
temp = csv.reader(data, delimiter=',')
for row in temp:
    print(row)
user_115492790

Since the data is poorly formatted, the csv module will have a hard time parsing it the way you want.

Best thing to do is to pre-process the file to clean up the data. In this case, all you need to do is add the missing trailing double quote.

import csv
import os

with open('/Desktop/test csv/quote_test.csv', 'r') as f:
    data = [f'{line.strip()}"' if not line.strip().endswith('"') else line.strip() for line in f.readlines()]
    temp = csv.reader(data, delimiter=',')
    for row in temp:
        print(row)

Note this code strips the newlines in order to append the double quotes to the end of the line. The newlines aren't added back (since they're not essential) -- but you could easily add them back if needed.

user_147225620

The problem is that the csv specification explicitely allows for quoted fields to contain new lines. Said differently, you file is not a CSV file and the Python CSV module cannot process it, whatever the configuration.

That means that a pre-processing is required. If you are sure that all lines but the first lack a double quote at the end, you can consistently add it after reading the headers. If you want to be more tolerant (maybe a later file would have that damned quote) you could add it only if the line has an even number of quotes (if a field contained a quote, it should be doubled). I would use a simple generator to fix the file:

def quote_adder(t):
    for line in t:
        line = line.strip()
        if (len([c for c in line if c == '"']) % 2) != 0:
            line += '"'
        yield line

with open('/Desktop/test csv/quote_test.csv', "r") as fd:
    for row in csv.reader(quote_adder(fd)):
        //process row

If process row is print(row) it gives as expected:

['name', 'age', 'place', 'date']
['Jack', '23', 'perth, australia', '12aug']
['Jackie', '44', 'delhi, india', '9dec']
['Neel', '12', 'austin, texas', '1aug']
['David', '77', 'fresno, ca', '21june']
user_35452730
Related