CSV file with random double quotes

2
votes

I have a CSV file that has a double quote character in some fields. When parsing with Python, it begins ignoring the delimiter in between these quotes. For instance:

5695|258|03/21/2012| 15:16:02.000|info|Microsoft-Windows-Defrag|shrink estimation, (C:)|36|"6ybSr: c{q6: |Application|WKS-WIN732test.test.local|http://schemas.microsoft.com/win/2004/08/events/event|0x0080000000000000|0|0||0|0|C:\Users\test\EventLog\win7-32-test-c-drive\Application.evtx
5770|258|03/24/2012| 04:21:02.000|info|Microsoft-Windows-Defrag|boot optimization, (C:)|36|00 00 00 00 d3 03 00 00 ae 03 00 00 00 00 00 00 22 b6 30 df 64 79 c7 f6 e2 6c 1c 00 00 00 00 00 00 00 00 00|Application|WKS-WIN732test.test.local|http://schemas.microsoft.com/win/2004/08/events/event|0x0080000000000000|0|0||0|0|C:\Users\test\EventLog\win7-32-test-c-drive\Application.evtx
5843|258|03/27/2012| 07:38:36.000|info|Microsoft-Windows-Defrag|boot optimization, (C:)|36|jbg54t5t"gfb:*&hgfh|Application|WKS-WIN732test.test.local|http://schemas.microsoft.com/win/2004/08/events/event|0x0080000000000000|0|0||0|0|C:\Users\test\EventLog\win7-32-test-c-drive\Application.evtx

As such, it reads everything between the two double quotes as a single field:

5695|258|03/21/2012| 15:16:02.000|info|Microsoft-Windows-Defrag|shrink estimation, (C:)|36|"6ybSr: c{q6: |Application|WKS-WIN732test.test.local|http://schemas.microsoft.com/win/2004/08/events/event|0x0080000000000000|0|0||0|0|C:\Users\test\EventLog\win7-32-test-c-drive\Application.evtx
                                                                                           ^

5770|258|03/24/2012| 04:21:02.000|info|Microsoft-Windows-Defrag|boot optimization, (C:)|36|00 00 00 00 d3 03 00 00 ae 03 00 00 00 00 00 00 22 b6 30 df 64 79 c7 f6 e2 6c 1c 00 00 00 00 00 00 00 00 00|Application|WKS-WIN732test.test.local|http://schemas.microsoft.com/win/2004/08/events/event|0x0080000000000000|0|0||0|0|C:\Users\test\EventLog\win7-32-test-c-drive\Application.evtx

5843|258|03/27/2012| 07:38:36.000|info|Microsoft-Windows-Defrag|boot optimization, (C:)|36|jbg54t5t"gfb:*&hgfh|Application|WKS-WIN732test.test.local|http://schemas.microsoft.com/win/2004/08/events/event|0x0080000000000000|0|0||0|0|C:\Users\test\EventLog\win7-32-test-c-drive\Application.evtx
                                                                                                   ^

(see the carets (^) in above example).

How do I get it to ignore the double quote?

CAVEAT: I do not want to read the entire file into RAM and replace the character. The solution must work while iterating through rows from the reader.

The delimiter is the pipe. I read it using standard CSV techniques and decode it with known encoding:

import csv
known_encoding = 'utf-8'  # for mwe, real code fetches for each file

with open(self.current_file.file_path, 'rb') as f:
    reader = csv.reader(f, delimiter='|')
    for row in reader:
        row = [s.decode(known_encoding) for s in row]
        # do stuff with data in row
2
pythoncsvdouble-quotes
Could you please tell what the desired delimiter is and provide the code you got so far? – albert
@albert added my code and delimiter (|) – Bob Dylan

2 Answers

4
votes

I guess your CSV files never contains quoted fields, so you can switch that off using the quoting parameter:

csv.reader(f, delimiter='|', quoting=csv.QUOTE_NONE)
2
votes

You can set quoting to csv.QUOTE_NONE as such:

import csv

with open('my_file', 'r') as f:
    csvreader = csv.reader(f, delimiter='|', quoting=csv.QUOTE_NONE)
    ....