replace string inside double quotes

[jmpatx]

I am trying to replace commas with a "^" in a string that is already quoted. Below you can see input and output. However my code does not replace the string. 

Any help would be appreciated.

Input:

Output:
Id,Category,Description,Date
1,Test,Red Cars,02/12/2017
2,Test,Blue Cars,03/01/2017
3,Test,"Green, big cars",01/05/2016

Output should be:

Output:
Id,Category,Description,Date
1,Test,Red Cars,02/12/2017
2,Test,Blue Cars,03/01/2017
3,Test,"Green^ big cars",01/05/2016

import csv

ifile = open('C:/Users/jpilon/Documents/test.csv', 'r')
reader = csv.reader(ifile,delimiter=',')
ofile = open('C:/Users/jpilon/Documents/test_new.csv', 'w')
writer = csv.writer(ofile, delimiter=',')


findlist = ['"*,*"']
replacelist = ['"*^*"']

rep = dict(zip(findlist, replacelist))

def findReplace(find, replace):
   s = ifile.read()
   s = s.replace(find, replace)
   ofile.write(s)

for item in findlist:
   findReplace(item, rep[item])

ifile.close()
ofile.close()

[sparkz_alot]

Could just be me, but your input looks exactly like your output.

[snippsat]

You don't use reader that csv module makes.
In line 15 you read() all in as string.

So read in and make a nested list,then can replace values and keep csv structure.

import csv

with open('in.csv') as f:
    reader = csv.reader(f, delimiter=',')
    cars_info = [i for i in reader]

Test:

>>> cars_info
[['Id', 'Category', 'Description', 'Date'],
 ['1', 'Test', 'Red Cars', '02/12/2017'],
 ['2', 'Test', 'Blue Cars', '03/01/2017'],
 ['3', 'Test', 'Green, big cars', '01/05/2016']]
>>> cars_info[3][2]
'Green, big cars'

>>> cars_info[3][2] = "Green^ big cars"
>>> cars_info
[['Id', 'Category', 'Description', 'Date'],
 ['1', 'Test', 'Red Cars', '02/12/2017'],
 ['2', 'Test', 'Blue Cars', '03/01/2017'],
 ['3', 'Test', 'Green^ big cars', '01/05/2016']]

[jmpatx]

Thanks snippsat. I am new to Python, so forgive my ignorance. I understand what you are doing, I guess where I am lost is how to apply it to a global find and replace in my code. 

If I were certain that a column 3 could potentially have the double quotes, then how would I replace any that met that criteria?

[nilamo]

Using the csv module is probably the way to go, since it'll handle the quotes for you.  But as usual, a regular expression also works:

>>> text = '''
... Id,Category,Description,Date
... 1,Test,Red Cars,02/12/2017
... 2,Test,Blue Cars,03/01/2017
... 3,Test,"Green, big cars",01/05/2016
... '''
>>> import re
>>> regex = re.compile(r'("[^",]*),([^",]*")')
>>> print(regex.sub(r'\1^\2', text))

Id,Category,Description,Date
1,Test,Red Cars,02/12/2017
2,Test,Blue Cars,03/01/2017
3,Test,"Green^ big cars",01/05/2016

[jmpatx]

The regular expression seemed to work on my example data, but when I tried a larger file with more columns, it did not replace the comma within the quotes.

Was the regular expression someone pointing to column 2 only?

import re

with open('file.csv') as f:
    s = f.read() + '\n'  # add trailing new line character

regex = re.compile(r'("[^",]*),([^",]*")')

s1 = (regex.sub(r'\1^\2', s))

print(s1)

f=open('file.csv',"w")
f.write(s1)
f.close()

[wavic]

Just use csv module

import csv

with open('/tmp/input.csv', 'r') as in_file:
    data = csv.reader(in_file, delimiter=',')
    for row in data:
        print([col.replace(',', '^') for col in row])

Output:
['Id', 'Category', 'Description', 'Date']
['1', 'Test', 'Red Cars', '02/12/2017']
['2', 'Test', 'Blue Cars', '03/01/2017']
['3', 'Test', 'Green^ big cars', '01/05/2016']

[jmpatx]

I did finally figure out how to read the csv and write new values to csv using my test file. Thanks for all your help! 

Now when I try this on a 1 gig csv file, I run into memory error. I know there are ways to do this in chunks, but that should be a question in a new thread.

import csv

new_rows_list = []

# Read File
f1 = open('in_file', 'r')
reader = csv.reader(f1, delimiter=',')
for row in reader:
    new_row = ([col.replace(',', '^') for col in row])
    new_rows_list.append(new_row)


# Write File
f2 = open('out_file', 'w')
writer = csv.writer(f2)
writer.writerows(new_rows_list)
f2.close()
f1.close()

[nilamo]

import csv

new_rows_list = []

# Read File
f1 = open('in_file', 'r')
reader = csv.reader(f1, delimiter=',')
for row in reader:
    new_row = ([col.replace(',', '^') for col in row])
    new_rows_list.append(new_row)


# Write File
f2 = open('out_file', 'w')
writer = csv.writer(f2)
writer.writerows(new_rows_list)
f2.close()
f1.close()

Don't store the whole file in memory, just work on it line-by-line:

import csv

with open("in_file", "r", newline="") as f1:
    reader = csv.reader(f1, delimiter=",")
    with open("out_file", "w", newline="") as f2:
        writer = csv.writer(f2)
        for row in reader:
            new_row = [col.replace(",", "^") for col in row]
            writer.writerow(new_row)

[snippsat]

Don't store the whole file in memory, just work on it line-by-line:

Yepp better.
Can also write it like this,one with is enough.

import csv

with open("in.csv") as f1,open("out.csv", "w", newline="") as f2:
    reader = csv.reader(f1, delimiter=",")
    writer = csv.writer(f2)
    for row in reader:
        new_row = [col.replace(",", "^") for col in row]
        writer.writerow(new_row)