Python | delimited text file to csv format

Question

I'm new to python but I'm having trouble reading a text file which contains data separated by "|" as the delimiter. How would I separate the file into columns in a CSV format.

import csv
my_file_name = "NVG.txt"
cleaned_file = "cleanNVG.csv"

with open(my_file_name, 'r') as infile, open(cleaned_file, 'w') as outfile:
    data = infile.read()

    data = data.replace("|","")
    outfile.write(data)

This code gets rid of the | to a blank but all the data is just in one column now. How can I format this correctly? I appreciate your help in advance.

Why are you removing the |? Why don't you just tell csv that that is your separator? i.e. csv.reader(infile, delimiter='|') — FamousJameous
– FamousJameous, Commented Oct 4, 2016 at 14:10

MisterMiyagi · Accepted Answer · 2016-10-04 14:19:34Z

5

The csv module allows you to read csv files with practically arbitrary delimiters.

with open(my_file_name, 'r', newline='') as infile:
    for line in csv.reader(infile, delimiter='|'):
        # do stuff

If you really want to reformat the file, you can use the csv.writer directly:

with open(my_file_name, 'r', newline='') as infile, open(cleaned_file, 'w', newline='') as outfile:
    writer = csv.writer(outfile)
    for line in csv.reader(infile, delimiter='|'):
        writer.writerow(line)

Note that your approach doesn't work because you remove the separator instead of replacing it. data.replace("|","") will replace each | with the empty string, i.e. "foo|bar" becomes "foobar". You must replace the old separator with a new one, e.g. data.replace("|", ",").

edited Oct 4, 2016 at 14:19

answered Oct 4, 2016 at 14:12

MisterMiyagi

53.4k14 gold badges131 silver badges138 bronze badges

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

3 Comments

Cesar Over a year ago

Ohh Okay!!! Thanks! The data is now in their columns but it leaves an empty row after. so Row 1 has data and Row 2 is blank , Row 3 has Data and Row 4 is blank and so on.... why is it doing that?

Moses Koledoye Over a year ago

@Cesar Define the writer object as writer = csv.writer(outfile, lineterminator="\n")

Cesar Over a year ago

Actually never mind, the **newline= ''**was messed up. This worked perfect!!

K Richardson · Accepted Answer · 2016-10-04 14:11:17Z

1

The simplest way with your code would be to replace "|" with "," rather than removing "|"

data = data.replace("|", ",")

answered Oct 4, 2016 at 14:11

K Richardson

1,7201 gold badge12 silver badges14 bronze badges

Comments

That1Guy · Accepted Answer · 2016-10-04 14:12:27Z

1

You're importing the csv module, but aren't using it. Make use of csv.reader

with open(my_file_name, 'r') as infile, open(cleaned_file, 'w') as outfile:
    reader = csv.reader(infile, delimiter='|')

answered Oct 4, 2016 at 14:12

That1Guy

7,2335 gold badges50 silver badges60 bronze badges

Collectives™ on Stack Overflow

Python | delimited text file to csv format

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related