topic Find and replace blanks in CSV with python in Python Questions

Find and replace blanks in CSV with python

AngelaPaltridge — Wed, 15 Dec 2010 03:57:50 GMT

Hi,
I have a csv with some blanks that I want to convert to a value of N so I can import into a database. Can someone tell me what I'm doing wrong in my code?

Thanks.

import fileinput
for line in fileinput.FileInput(OPS_frq,inplace=1):
line = line.replace(" ","N")
print line

The csv looks like this:
GEOKEY,INDIG,CAVE,CORR,EYPT,PSP,LIC,HERIT,WELLS,TS,TT
1,,,,,,,,,,
2,,,,
3,,,,Y
4
5

Re: Find and replace blanks in CSV with python

ChrisMathers — Wed, 15 Dec 2010 13:07:49 GMT

well you are telling the line to replace " " with "N". Try "" instead of " ". This is replacing an empty string instead of a string that contains only a space character.

Re: Find and replace blanks in CSV with python

AngelaPaltridge — Mon, 20 Dec 2010 03:17:04 GMT

well you are telling the line to replace " " with "N". Try "" instead of " ". This is replacing an empty string instead of a string that contains only a space character.

When I try that, I get this:

NGNENONKNENYN,NINNNDNINGN,NCNANVNEN,NCNONRNRN,NENXNPNTN,NPNSNPN,NLNINCN,NHNENRNINTN,NWNENLNLNSN,NTNSN,NTNTN
N
N1N0N1N0N0N0N1N0N0N0N0N,N,N,N,N,N,N,N,N,N,N
N
N1N0N1N0N0N1N1N0N0N0N0N,N,N,N,N,N,N,N,N,N,N
N
N1N0N1N0N0N1N1N0N1N0N0N,N,N,N,N,N,N,N,N,N,N
N
N1N0N1N0N0N3N1N2N3N0N0N,N,N,N,N,NYN

Re: Find and replace blanks in CSV with python

JasonScheirer — Sun, 12 Dec 2021 04:25:25 GMT

This won't work, you're going to need to use the CSV module to look deeper into your data. Yo could do something like this:

import csv

in_file = "hello.csv"
out_file = "hello_fixed.csv"

row_reader = csv.reader(open(in_file, "rb"))
row_writer = csv.writer(open(out_file, "wb"))

first_row = row_reader.next()
row_writer.writerow(first_row)
for row in row_reader:
    new_row = [val if val else "N" for val in row] + (["N"] * (len(first_row) - len(row)))
    print row, "->", new_row
    row_writer.writerow(new_row)

Even though this is plain text, it's pretty tricky to get right without the help of a library.

Re: Find and replace blanks in CSV with python

AngelaPaltridge — Sun, 12 Dec 2021 04:25:28 GMT

This won't work, you're going to need to use the CSV module to look deeper into your data. Yo could do something like this:
import csv

in_file = "hello.csv"
out_file = "hello_fixed.csv"

row_reader = csv.reader(open(in_file, "rb"))
row_writer = csv.writer(open(out_file, "wb"))

first_row = row_reader.next()
row_writer.writerow(first_row)
for row in row_reader:
    new_row = [val if val else "N" for val in row] + (["N"] * (len(first_row) - len(row)))
    print row, "->", new_row
    row_writer.writerow(new_row)
Even though this is plain text, it's pretty tricky to get right without the help of a library.

Thanks Jason - that worked brilliantly!

Re: Find and replace blanks in CSV with python

curtvprice — Thu, 29 Apr 2021 19:15:36 GMT

I mean Python *is* great but I want to add to the thread that the import wizard in MS Excel is pretty flexible with a lot of delimited (and fixed-column) data -- so there is absolutely nothing wrong with using the excel text file wizard to get to excel, fix your data up, and export again as csv for import to ArcGIS. Just sayin.