My data has comma in the value of the column which is also a delimiter, how to read it by csv.reader in python

Question

This is how my data looks,

"id","text"
"416752", "i's ** This year’s theme is \"Each for Equal\".**  When: Friday March 6th Time: 10:00am-12:00pm  Where:  HQ 155 Gordon Baker Road, 5th floor, Halifax/Dartmouth Boardoom   Please note there are 2 shifts - one to log your attendance for the event and the other to log your donation, if applicable.    Structure of the Event  10h00am - 10h15am:   Welcome, cupcakes, coffee, social media picture opportunity (#eachforequal)  10h15am – 10h25am:   Intro from WIA, IWD, theme introduction, agenda, community initiative: Dress for Success  10h30am – 11h15am:   Keynote Speaker (Shasta Townsend)  11h30am – 12h00pm:   Panel Stories (Mike Sharun, Dennis Hoffman, Barbara Dahan and Nikki Matarazzo)     **"

Below is the logic i have tried:

import csv
import codecs
file_path=r'C:\data.csv'
f = codecs.open(file_path, encoding = "utf8", errors ='replace')
csvreader = csv.reader(f, delimiter=',')
for row in csvreader:
    print(row)

Output:

['416752', 'i\'s ** This year’s theme is \\Each for Equal\\".**  When: Friday March 6th Time: 10:00am-12:00pm  Where:  HQ 155 Gordon Baker Road', '5th floor', 'Halifax/Dartmouth Boardoom   Please note there are 2 shifts - one to log your attendance for the event and the other to log your donation', 'if applicable.     Structure of the Event    10h00am - 10h15am:   Welcome', 'cupcakes', 'coffee', 'social media picture opportunity (#eachforequal)  10h15am – 10h25am:   Intro from WIA', 'IWD', 'theme introduction', 'agenda', 'community initiative: Dress for Success  10h30am – 11h15am:   Keynote Speaker (Shasta Townsend)  11h30am – 12h00pm:   Panel Stories (Mike Sharun', 'Dennis Hoffman', 'Barbara Dahan and Nikki Matarazzo)     **"']

Instead of two values, i am getting more than that as my data is having many commas.

Answer 1

The space after the comma is the problem. Use skipinitialspace=True to remedy this. Also recommend using built-in open. With your updated data, you also need doublequote=False and escapechar='\\'.

Double quotes within a string is default-escaped by doubling the character, e.g. "Say, ""Hi!""", but your example uses a backslash to escape, e.g. "Say, \"Hi!\"". The two additional options disable doubling of the quote, and to use the escape character instead.

import csv

file_path = 'data.csv'
with open(file_path, encoding='utf8', newline='') as f:
    csvreader = csv.reader(f, skipinitialspace=True, doublequote=False, escapechar='\\')
    for row in csvreader:
        print(row)

Output:

['id', 'text']
['416752', 'i\'s ** This year’s theme is "Each for Equal".**  When: Friday March 6th Time: 10:00am-12:00pm  Where:  HQ 155 Gordon Baker Road, 5th floor, Halifax/Dartmouth Boardoom   Please note there are 2 shifts - one to log your attendance for the event and the other to log your donation, if applicable.    Structure of the Event  10h00am - 10h15am:   Welcome, cupcakes, coffee, social media picture opportunity (#eachforequal)  10h15am – 10h25am:   Intro from WIA, IWD, theme introduction, agenda, community initiative: Dress for Success  10h30am – 11h15am:   Keynote Speaker (Shasta Townsend)  11h30am – 12h00pm:   Panel Stories (Mike Sharun, Dennis Hoffman, Barbara Dahan and Nikki Matarazzo)     **']

Answer 2

As is mentioned in this question Why is the Python CSV reader ignoring double-quoted fields? you'll need to add the skipinitalspace param so that csv.reader will understand the quotes.

import csv
import codecs
file_path=r'C:\data.csv'
f = codecs.open(file_path, encoding = "utf8", errors ='replace')
csvreader = csv.reader(f, delimiter=',', skipinitialspace=True)
for row in csvreader:
    print(row)