Find and replace blanks in CSV with python

[AngelaPaltridge]

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

[ChrisMathers]

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.

[AngelaPaltridge]

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

[JasonScheirer · Accepted solution]

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.

[AngelaPaltridge]

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!

[curtvprice]

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.