Python CSV Formatting Hard Return-CodePudding

I'm trying to append data to a CSV file. I'd like to be able to make manual changes to the file from time to time in Excel MacOS then rerun the python program.

However, when I open the file and save it in Excel MacOS, it removes a hard return. Then on subsequent runs the lack of a hard return messes up the formatting.

with open("test.csv", 'a', newline='', encoding='utf-8') as csvfile:
    fieldnames = ['ID', 'Name']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writerow({'ID': "1", 'Name': "Frank"})
    writer.writerow({'ID': "2", 'Name': "Bob"})

After initial python run

$ cat -e test.csv
1,Frank^M$
2,Bob^M$

After manual save in Excel on MacOS

$ cat -e test.csv
1,Frank^M$
2,Bob

After running python program a second time. Notice the messed up formatting.

$ cat -e test.csv
1,Frank^M$
2,Bob1,Frank^M$
2,Bob^M$

CodePudding user response：

I think you're trying to leverage the append mode, and when Excel doesn't add (or outright strips) the trailing line break, your process breaks.

The simplest answer is, "don't create a process that relies on that final line break". Excel doesn't have to include it^1:

2. The last record in the file may or may not have an ending line
   break.  For example:

   aaa,bbb,ccc CRLF
   zzz,yyy,xxx

I cannot say when append is the right thing to do, but I think it's the wrong thing for CSV.

Instead, always treat your data as CSV throughout... always using the readers and the writers from the CSV module, because they can deal with the vagueries of the CSV spec, and this whole "no final new line" just isn't an issue for them.

Here's a mockup of your data coming out of Excel:

cat -e test.csv
1,Frank^M$
2,Bob

Run this:

import csv

Fieldnames = ["ID", "Name"]

# Normalize previous CSV data into `previous_rows`
with open("test.csv", newline="") as csvfile:
    reader = csv.DictReader(csvfile, fieldnames=Fieldnames)
    previous_rows = list(reader)
    # previous_rows = [row for row in reader if row]  # to drop empty rows


with open("test.csv", "w", newline="") as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=Fieldnames)

    writer.writerows(previous_rows)

    # "Append" new rows
    writer.writerow({"ID": "3", "Name": "Frank"})
    writer.writerow({"ID": "4", "Name": "Bob"})

and it just works:

cat -e test.csv
1,Frank^M$
2,Bob^M$
3,Frank^M$
4,Bob^M$