Python Forum
replace string inside double quotes
Thread Rating:
  • 2 Vote(s) - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
replace string inside double quotes
#1
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()
Reply
#2
Could just be me, but your input looks exactly like your output.
If it ain't broke, I just haven't gotten to it yet.
OS: Windows 10, openSuse 42.3, freeBSD 11, Raspian "Stretch"
Python 3.6.5, IDE: PyCharm 2018 Community Edition
Reply
#3
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']]
Reply
#4
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?
Reply
#5
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
Reply
#6
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()
Reply
#7
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']
"As they say in Mexico 'dosvidaniya'. That makes two vidaniyas."
https://freedns.afraid.org
Reply
#8
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!  Smile

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()
Reply
#9
(Apr-25-2017, 08:18 PM)jmpatx Wrote:
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)
Reply
#10
(Apr-26-2017, 05:08 AM)nilamo Wrote: 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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Need to replace a string with a file (HTML file) tester_V 1 699 Aug-30-2023, 03:42 AM
Last Post: Larz60+
  Replace string in a nested Dictianory. SpongeB0B 2 1,147 Mar-24-2023, 05:09 PM
Last Post: SpongeB0B
  Replace with upper(string) WJSwan 7 1,545 Feb-10-2023, 10:28 AM
Last Post: WJSwan
  Need help on how to include single quotes on data of variable string hani_hms 5 1,886 Jan-10-2023, 11:26 AM
Last Post: codinglearner
  Find and Replace numbers in String giddyhead 2 1,197 Jul-17-2022, 06:22 PM
Last Post: giddyhead
  Replace String in multiple text-files [SOLVED] AlphaInc 5 7,962 Aug-08-2021, 04:59 PM
Last Post: Axel_Erfurt
  Replace String with increasing numer [SOLVED] AlphaInc 13 4,919 Aug-07-2021, 08:16 AM
Last Post: perfringo
  Remove single and double quotes from a csv file in 3 to 4 column shantanu97 0 6,926 Mar-31-2021, 10:52 AM
Last Post: shantanu97
  Two types of single quotes Led_Zeppelin 2 1,863 Mar-15-2021, 07:55 PM
Last Post: BashBedlam
  How to replace on char with another in a string? korenron 3 2,294 Dec-03-2020, 07:37 AM
Last Post: korenron

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020