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
– Steven Rumbalski, Commented Jul 8, 2011 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
– drbunsen, Commented Jul 8, 2011 at 21:27

Gordon Seidoh Worley · Accepted Answer · 2018-01-03 02:03:54Z

29

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 3, 2018 at 2:03

answered Jul 8, 2011 at 20:37

Gordon Seidoh Worley

8,1367 gold badges51 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ramy Over a year ago

...rows is not defined here. I'm unsure what it's supposed to refer to.

rlaverde Over a year ago

Replace writer.writerows(rows) for writer.writerows(list(reader)), or even better writer.writerows(reader)

Sylhare · Accepted Answer · 2021-06-05 00:09:20Z

13

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 5, 2021 at 0:09

answered Mar 21, 2018 at 19:09

Sylhare

7,2898 gold badges75 silver badges88 bronze badges

3 Comments

Swedgin Over a year ago

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.

Sylhare Over a year ago

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 Over a year ago

Indeed @Bhanunday Sharma, the U is deprecated so I removed it. Apparently newline=None is the default and does the same thing as the U was suppose to do :)

Bhanuday Sharma · Accepted Answer · 2020-05-22 19:31:47Z

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, 2020 at 19:31

Bhanuday Sharma

4535 silver badges11 bronze badges

2 Comments

Sylhare Over a year ago

This could have been a comment, I updated it ;)

Bhanuday Sharma Over a year ago

@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.

jeppoo1 · Accepted Answer · 2020-02-28 11:25:02Z

0

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

answered Feb 28, 2020 at 11:25

jeppoo1

6981 gold badge10 silver badges27 bronze badges

Comments

TorelTwiddler · Accepted Answer · 2011-07-08 20:38:41Z

-1

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)

answered Jul 8, 2011 at 20:38

TorelTwiddler

6,1963 gold badges34 silver badges39 bronze badges

Comments

Wilduck · Accepted Answer · 2011-07-08 20:38:26Z

-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 8, 2011 at 20:38

Wilduck

14.2k13 gold badges63 silver badges91 bronze badges

2 Comments

Ron7 Over a year ago

There's a typo on the 4th line; line.replace should be lines.replace

Gnudiff Over a year ago

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.

Collectives™ on Stack Overflow

Python parse csv file - replace commas with colons

6 Answers 6

2 Comments

3 Comments

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

3 Comments

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related