Cesar Cesar - 1 year ago 115
Python Question

Python | delimited text file to csv format

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("|","")

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.

Answer Source

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='|'):

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("|", ",").

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download