Reading a csv file with some lines having wrong encoding and return incorrect lines to a user

user_3543200 0
Viewed 586

I have some users uploading csv files to be ingested. In Python 2, I was able to open the file in binary, pass it to a unicodecsv.DictReader, and if some rows had an encoding issue, like an invalid Unicode character because the customer used CP1251 or something, I could log those rows and return exactly which rows had an issue.

With py3.7, it doesn't seem that I can do this -- the csv module requires the file to be decoded, and if I instead pass it a generator like (line.decode('utf8') for line in my_binary_file), I can't make it throw an exception only for the bad lines and keep going after. I tried using unicodecsv, even though it hasn't seen a commit in over four years and doesn't technically support py > 3.5, and it doesn't seem to work either -- the iterator just stops after the bad row.

I can see two ways around this, neither of which is appealing:
1) decode the file line by line beforehand and find bad lines, which is wasteful, or
2) write my own CSV parser which allows skipping of bad lines, which seems like asking for trouble.

Can I do this another way?

For reference, here's example code that worked in py2:

def unicode_safe_iterator(reader):
    while True:
        try:
            yield True, next(reader)
        except UnicodeDecodeError as exc:
            yield False, 'UnicodeDecodeError: %s' % str(exc)
        # uncomment for py3:
        # except StopIteration:
        #     return

def get_data_iter_from_csv(csv_file, ...):
    reader = unicodecsv.DictReader(csv_file)
    error_messages = []
    line_num = 1
    for valid, row in unicode_safe_iterator(reader):
        line_num += 1
        if not valid:
            error_messages.append(dict(line_number=line_num, error=row))
        else:
            row_data = validate_row_data(row)  # check for errors other than encoding, etc.
        if not error_messages:
            # stop yielding in case of errors, but keep iterating to find all errors.
            yield row_data
    if error_messages:
        raise ValidationError(Errors.CSV_FILE_ERRORS, error_items=error_messages)


data_iter = get_data_iter_from_csv(open(path_to_csv, 'rb'), ...)
python-unicode csv unicode python-3.x python
1 Answers

Here is a workaround. We read the file as byte stream, split it at new lines and try to convert lines into utf8 strings. If it is failed, try to convert the improper parts into cp1251 string. Therafter you can use io.StringIO to imitate a file open.

import csv, io

def convert(bl):

    rslt=[]
    done=False
    pos=0
    while not done:
        try:
            s=bl[pos:].decode("utf8")
            rslt.append(s)
            done=True
        except UnicodeDecodeError as ev:
            abs_start, abs_end= pos+ev.start, pos+ev.end
            rslt.append(bl[pos:abs_start].decode("utf8"))
            rslt.append(bl[abs_start:abs_end].decode("cp1251",errors="replace"))
            pos= abs_end
            if pos>= len(bl):
                done=True

    return "".join(rslt)


with open(path_to_csv,"rb") as ff:

    data= ff.read().split(b'\x0a')
    text= [ convert(line)  for line in data ]

text="\n".join(text)
print(text)

rdr= csv.DictReader(io.StringIO(text))

It can be done at once, not line by line, too:

with open(path_to_csv,"rb") as ff:  
    text= convert( ff.read() )

rdr= csv.DictReader(io.StringIO(text))
user_97957200
Related