Find and replace blanks in CSV with python

21330
5
Jump to solution
12-14-2010 07:57 PM
AngelaPaltridge
New Contributor
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
Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JasonScheirer
Regular Contributor II
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.

View solution in original post

0 Kudos
5 Replies
ChrisMathers
Regular Contributor II
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.
0 Kudos
AngelaPaltridge
New Contributor
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
0 Kudos
JasonScheirer
Regular Contributor II
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.
0 Kudos
AngelaPaltridge
New Contributor
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!
0 Kudos
curtvprice
MVP Esteemed Contributor

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.

0 Kudos