How do you write a dictionary to a CSV file on a line that already contains text?

Question

I'm using python 3.6.0. I have written dictionaries to CSV files before, but never on lines that already contains text. I am having trouble with that now. Here's my code:

import csv

f='/Users/[my name]/Documents/Webscraper/tests/output_sheet_1.csv'
bigdict = {'ex_1': 1, 'ex_2': 2, 'ex_3': 3}
with open(f, 'r+') as file:
    fieldnames=['ex_1','ex_2','ex_3']
    writer = csv.DictWriter(file, fieldnames=fieldnames,delimiter=',')
    if '\n' not in file.readline()[-1]:
        file.write('\n')
    writer.writerow(bigdict)

When I run this, python appends the dictionary on the first line after the row containing the fieldnames, starting at the last cell that's below a fieldname. In other words, the first row contains many entries, including ex_1, ex_2, and ex_3 in the last three cells. In the second row, I have values stored in all cells except for those under ex_1, ex_2, and ex_3, which are blank. Python writes the integer 1 below ex_3, and writes 2 and 3 in the cells right of it.

I would like to re-position them so each number is under their respective fieldname cells. How do I do this, and why is this happening? Thanks.

Did you attempt `writer.writeheader(); writer.writrow(bigdict)`? — pstatix, Jun 22 '18 at 02:21
@pstatix I tried using `writer.writeheader()`, but, as expected, it creates another header, which I don't want since it's already in the CSV file. I could take the contents in the file and rewrite them to a new file, but I don't want to do that since the file is very large, and I'd like to be more efficient with my code. — dxxg-syz35, Jun 22 '18 at 02:32
I dont think this happens like you think. Just because those headers exist doesn't mean that they will automatically be written underneath them just because you've instantiated a `DictWriter`. — pstatix, Jun 22 '18 at 02:34
OK, from what I understand, so far, python assumes that everything under the unlisted fieldnames is blank. When that is not the case, it basically messes up the formatting. With that said... is rewriting the whole thing to a new CSV file the most efficient way to do this? — dxxg-syz35, Jun 22 '18 at 02:46

score 1 · Answer 1 · answered Jun 22 '18 at 08:26

You have two issues:

If there is no file (or if it is empty) you would want to have a header line added.
If you are appending a new row to an existing file, you are trying to ensure that the last character in your file is a newline. writerow() will add a trailing newline so normally this would not be a problem. If however the file has been manually edited and the trailing newline is missing, this would cause the new row to be appended to the end of the last line.

The first issue can be resolved by first testing the size of the file. If it is 0 then it exists but is empty. If the file does not exist, an OSError exception is raised. write_header is used to signal this.

The second issue is a bit more tricky. If you open the file in binary mode, it is possible to seek to the last byte of the file and read it in. This can be checked to see if it is a newline. If your file ever used another encoding, this would need to be changed. The file can then be reopened in append mode and the new row written.

This can all be done as follows:

import csv

filename = '/Users/[my name]/Documents/Webscraper/tests/output_sheet_1.csv'
bigdict = {'ex_1': 1, 'ex_2': 2, 'ex_3': 3}

# Does the file exist? If not (or it is empty) write a header
try:
    write_header = os.path.getsize(filename) == 0
except OSError:
    write_header = True

# If the file exists, does it end with a newline?    
if write_header:
    no_ending_newline = False
else:
    with open(filename, 'rb') as f_input:
        f_input.seek(-1, 2)     # move to the last byte of the file
        no_ending_newline = f_input.read() != b'\n'

with open(filename, 'a', newline='') as f_output:
    fieldnames = ['ex_1','ex_2','ex_3']
    csv_writer = csv.DictWriter(f_output, fieldnames=fieldnames)

    if write_header:
        csv_writer.writeheader()

    if no_ending_newline:
        f_output.write('\n')

    csv_writer.writerow(bigdict)

How do you write a dictionary to a CSV file on a line that already contains text?

1 Answers1