Python parse csv file - replace commas with colons

Question

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.

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, Jul 08 '11 at 20:45
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, Jul 08 '11 at 21:27

Gordon Seidoh Worley · Accepted Answer · 2018-01-03T02:03:54.953

28

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.

edited Jan 03 '18 at 02:03

answered Jul 08 '11 at 20:37

Gordon Seidoh Worley

7,839
6
45
82

22

...rows is not defined here. I'm unsure what it's supposed to refer to. – Ramy Oct 08 '13 at 14:47
2

Replace `writer.writerows(rows)` for `writer.writerows(list(reader))`, or even better `writer.writerows(reader)` – rlaverde May 09 '18 at 17:13

Sylhare · Answer 2 · 2021-06-05T00:09:20.960

11

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.

edited Jun 05 '21 at 00:09

answered Mar 21 '18 at 19:09

Sylhare

5,907
8
64
80

The downside here is that you don't close the file objects you've opened, eg. `f.close()`. Using `with open(...)` this gets done automatically. Better to name them and close them after you don't need them anymore. – Swedgin May 10 '21 at 09:53
Pros and cons, in this particular case the `with open(...)` just to automatically close is not that much of a help in my opinion. – Sylhare May 13 '21 at 14:08
Indeed [@Bhanunday Sharma](https://stackoverflow.com/users/8957899/bhanuday-sharma), the `U` is deprecated so I removed it. Apparently `newline=None` is the [default](https://stackoverflow.com/questions/56791545/what-is-the-non-deprecated-version-of-open-u-mode) and does the same thing as the U was suppose to do :) – Sylhare Jun 05 '21 at 00:12

score 1 · Answer 3 · answered May 22 '20 at 19:31

1

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)

answered May 22 '20 at 19:31

Bhanuday Sharma

323
3
10

This could have been a comment, I updated it ;) – Sylhare Jun 05 '21 at 00:16
@Sylhare Yes. You are right. But at that time I didn't have the privilege to comment. Anyway, thanks for both the original and updated answers. – Bhanuday Sharma Jun 05 '21 at 12:08

score 0 · Answer 4 · answered Feb 28 '20 at 11:25

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

score -1 · Answer 5 · answered Jul 08 '11 at 20:38

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)

score -2 · Answer 6 · answered Jul 08 '11 at 20:38

-2

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.

answered Jul 08 '11 at 20:38

Wilduck

13,822
10
58
90

There's a typo on the 4th line; line.replace should be lines.replace – Ron7 Sep 13 '17 at 04:10
CSV has some different flavours, but in most of them there are special ways to escape commas inside data, which need to be taken into account, so a simple replace won't do. – Gnudiff Dec 22 '17 at 11:12

Python parse csv file - replace commas with colons

6 Answers6

Linked