13
votes

I suspect this is a common problem, but I counldn't seem to locate the answer. I am trying to remove all commas from a csv file and replace them with colons. I would normally use sed or vi for this, but I need to use a purely python implementation. Here is what I have come up with so far:

import csv

with open("temp.csv", mode="rU") as infile:
    reader = csv.reader(infile, dialect="excel")    
    with open("temp2.txt", mode="w") as outfile:
        writer = csv.writer(outfile)
        for rows in reader:
            for parsed_item in rows:
                parsed_item = rows.replace(',', ':') # I can't do this with a list!
                writer.writerow(parsed_item)

Can anyone help me out with how to do this? Thanks in advance for your help.

6
I see from the answers that their are two interpretations of your question. Some think you want to change the delimeter to a colon. Others think you want replace any commas in each item. Which is it? - Steven Rumbalski
I apologize for the ambiguity of my question. I intended to ask how to change the delimiter from a comma to a colon. I didn't know it was as simple as specifying the delimiter as a colon. For some reason I didn't think that would work. Thank you everyone for the help! - drbunsen

6 Answers

25
votes

The answer is easier than you think. You just need to set the delimiter for csv.writer:

import csv

row = #your data

with open("temp.csv", mode="rU") as infile:
    reader = csv.reader(infile, dialect="excel")    
    with open("temp2.txt", mode="w") as outfile:
        writer = csv.writer(outfile, delimiter=':')
        writer.writerows(rows)

You're line trying to replace , with : wasn't going to do anything because the row had already been processed by csv.reader.

9
votes

If you are looking to read a csv with comma delimiter and write it in another file with semicolon delimiters. I think a more straightforward way would be:

reader = csv.reader(open("input.csv", "r"), delimiter=',')
writer = csv.writer(open("output.csv", 'w'), delimiter=';')
writer.writerows(reader)

I find this example much easier to understand than with the with open(...). Also if you work with file using comma and semicolon as delimiters. You can use the Sniffer of the csv file to detect which delimiter is used before reading the file (example in the link).

Also if you want to rewrite in the same file, check this stackoverflow answer.

1
votes

I will build my answer on @Sylhare's answer. In python3, the 'U' mode is deprecated. So, the following solution worked for me:

import csv

reader = csv.reader(open("input.csv", newline=None), delimiter=',')
writer = csv.writer(open("output.csv", 'w'), delimiter=':')
writer.writerows(reader)
0
votes

I'm writing csv files from JSON raw data and noticed that the DictWriter module also supports different delimiters. Example:

with open('file_1.csv', 'w', encoding="utf-8-sig", newline = '') as myfile:
    wr = csv.DictWriter(myfile, fieldnames = table_fields, delimiter=';')
    wr.writeheader()
    wr.writerows(# my data #)
-1
votes

Assuming that the CSV is comma delimited, and you want to replace commas in each entry, I believe the issue is replacing the wrong item:

for rows in reader:
    for parsed_item in rows:
        parsed_item = parsed_item.replace(',', ':') # Change rows to parsed_item
        writer.writerow(parsed_item)
-2
votes

If you're just replacing commas with colons, you don't need to use a csv parser at all.

with open("file.csv", 'r') as f:
    with open("temp.csv", 'w') as t:
        for lines in f:
            new_line = line.replace(",",":")
            t.write(new_line)

The only caveat is that you can't have commas elsewhere in the csv file.