Open .csv file edit one field, write back to .csv file

Question

So I have a .csv file with names, lat, lon, and phone number; separated by comma. I need to open the file, edit the phone number into a more legible format and then write that back to the file.

There is a nice solution to editing the phone numbers Here

Not really sure of the best way to approach this problem. Any help is greatly appreciated. Thanks.

import csv

def phone_format(n):
    return format(int(n[:-1]), ",").replace(",", "-") +n[-1]

with open('sample.csv', 'rU') as csvfile:
    spotreader = csv.reader(csvfile)
    spotwriter = csv.writer(csvfile)
    for row in spotreader:
        spotwriter.writerow([0] + phone_format(spotreader[1]))

This does not work. Not really sure how to get what I am looking for.

Sample of my csv file below

Jason Schow,,5016098648
Dena VanGorder,,6074621816
Lindsey McNabb,3066533971,3066505001
Jeff Wozniak,3066531566,3069420647
Victoria Norton,3067692840,3067697062
Benjie Butikofer,3067692107,3067697108
Jessica Duede,,3062813158
Pete Vogeh,3063776261,3069890349
Melissa Kwasney,,3069412583

Type of output to .csv file that I am looking for below:

Jason Schow,,501-609-8648
Dena VanGorder,,607-462-1816
Lindsey McNabb,306-653-3971,306-650-5001
Jeff Wozniak,306-653-1566,306-942-0647
Victoria Norton,306-769-2840,306-769-7062
Benjie Butikofer,306-769-2107,306-769-7108
Jessica Duede,,306-281-3158
Pete Vogeh,306-377-6261,306-989-0349
Melissa Kwasney,,306-941-2583

Not clear what is the issue here, you know how to load the csv content to memory, how to format your phone numbers, and how to write a dataframe back into a csv file. I would simply suggest to use panda's functions: `load_csv` https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html and `to_csv` https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_csv.html — byouness, May 16 '18 at 20:42

Ewout Maertens · Accepted Answer · 2018-05-16T21:27:42.123

1

Shouldn't you be formatting on row, instead of spotreader? This works fine for me reading it from a .csv file. I added a check for ignoring empty strings. Also the spotwriter won't work since you aren't opening the file in write mode. 'rU' is read mode only. What you want to do is make a brand new .csv file and write the output there like this:

import csv

def phone_format(n):
    return format(int(n[:-1]), ",").replace(",", "-") +n[-1]

with open('sample.csv', 'rU') as csvfile:
    with open('sampleOutput.csv', 'w') as csvfile2:
        spotreader = csv.reader(csvfile)
        spotwriter = csv.writer(csvfile2)
        for row in spotreader:
            if row[1] != '':
                spotwriter.writerow([row[0], phone_format(row[1]), row[2]])
            else:
                spotwriter.writerow([row[0], row[1], row[2]])

The input is your .csv file.

Output:

Jason Schow,,5016098648
Dena VanGorder,,6074621816
Lindsey McNabb,306-653-3971,3066505001
Jeff Wozniak,306-653-1566,3069420647
Victoria Norton,306-769-2840,3067697062
Benjie Butikofer,306-769-2107,3067697108
Jessica Duede,,3062813158
Pete Vogeh,306-377-6261,3069890349
Melissa Kwasney,,3069412583

This is assuming you only want to edit the first phone number, if you want to edit the second phone number too, you will have to do some additional programming.

edited May 16 '18 at 21:27

answered May 16 '18 at 21:00

Ewout Maertens

26
3

When I try to add the + '\n' to the end of the spotwriter.writerow I get a TypeError: can only concatenate list (not "str") to list. Any ideas about that particular problem. – Rex May 16 '18 at 22:49
Well, '\n' is basically a character. The writerow function can only write a list into a row (for each argument in the list there will be an argument created in the row). That's why the parameter to the writerow function has to be a list. What you could do is this: writerow([row[0], row[1], '\n']), but I don't know why you would want to do this since each row is automatically written on a new line. Can you give me some insight on why you want to add a newline at the end? – Ewout Maertens May 16 '18 at 22:59
As for your problem with the whitespaces go, I have no clue why it's there for you. It works perfectly fine for me, without having the whitespace at the end. Can you print out each argument before you write it into a new .csv file to check if there is already a whitespace there? If so you can remove them with the command: string.replace(" ", "") – Ewout Maertens May 16 '18 at 23:01
I thought perhaps I needed the '\n' to get rid of the white space. I print out the rows after each iteration and have no white spaces in the cmd line output. However, when I open up the file I have all the white space. Scratching my head. – Rex May 16 '18 at 23:26
Can you try opening it into excel and check if the whitespaces are there too? If so can you put the line where you write it into the .csv file again. In any case as long as you remember the whitespaces are there you can trim them out if you read it in again, it's just strange because it shouldn't be doing it. – Ewout Maertens May 16 '18 at 23:32
Yeah, they (the white spaces) show up when using Excel to open the file also. – Rex May 16 '18 at 23:37
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/171190/discussion-between-rex-and-ewout-maertens). – Rex May 16 '18 at 23:38

score 0 · Answer 2 · answered May 17 '18 at 04:08

Got it figured out. I stumbled across this in another stackoverflow article. Article Here

The corrected code is as follows. Thanks again for all the help and suggestions.

import csv

def phone_format(n):
    return format(int(n[:-1]), ",").replace(",", "-") +n[-1]

with open('sample.csv', 'rU') as csvfile:
    with open('sampleOutput.csv', 'w', newline='') as csvfile2:
        spotreader = csv.reader(csvfile)
        spotwriter = csv.writer(csvfile2)
        for row in spotreader:
            if row[1] != '' and row[2] != '':
                spotwriter.writerow([row[0], phone_format(row[1]), phone_format(row[2])])
            elif row[1] != '' and row[2] == '':
                spotwriter.writerow([row[0], phone_format(row[1]), (row[2])])
            elif row[1] == '' and row[2] != '' :
                spotwriter.writerow([row[0], (row[1]), phone_format(row[2])])

Open .csv file edit one field, write back to .csv file

2 Answers2