Bottom Page

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()
metulburr wrote Apr-21-2017, 08:59 PM:
removed formatting
Quote
#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
Quote
#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']]
Quote
#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?
Quote
#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

Quote
#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()
Quote
#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
Quote
#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()

Quote
#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)

Quote
#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)
buran likes this post
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  converting string object inside a list into an intiger bwdu 4 178 Mar-31-2020, 10:36 AM
Last Post: buran
  Correct syntax for a variable inside a quotes: MP4Box command JonnyDriller 2 302 Feb-02-2020, 01:22 AM
Last Post: JonnyDriller
  Python Parameter inside Json file treated as String dhiliptcs 0 346 Dec-10-2019, 07:28 PM
Last Post: dhiliptcs
  Cannot Remove the Double Quotes on a Certain Word (String) Python BeautifulSoup soothsayerpg 5 1,318 Oct-27-2019, 09:53 AM
Last Post: newbieAuggie2019
  Replace string in many files in a folder metro17 8 632 Oct-16-2019, 06:46 PM
Last Post: ndc85430
  open, read and replace a string in a file Reims 0 347 Oct-02-2019, 01:30 PM
Last Post: Reims
  Read each line, replace string and save into a new file igormonteiro 2 586 Sep-15-2019, 01:24 PM
Last Post: buran
  Is it possible to have an apostrophe inside { } in an f-string? Exsul 6 1,028 Sep-05-2019, 12:37 AM
Last Post: Exsul
  replace white space with a string, is this pythonic? Skaperen 1 421 Jun-18-2019, 11:36 PM
Last Post: metulburr
  Replace a list or string element with a random one.. pianistseb 3 512 May-09-2019, 08:24 AM
Last Post: buran

Forum Jump:


Users browsing this thread: 1 Guest(s)