Python's csv.writerow() is acting a tad funky

Question

From what I've researched, csv.writeRow should take in a list, and then write it to the given csv file. Here's what I tried:

from csv import writer
with open('Test.csv', 'wb') as file:
        csvFile, count = writer(file), 0
        titles = ["Hello", "World", "My", "Name", "Is", "Simon"]
        csvFile.writerow(titles)

I'm just trying to write it so that each word is in a different column.

When I open the file that it creates, however, I get the following message:

After pressing to continue anyways, I get a message saying that the file is either corrupted, or is a SYLK file. I can then open the file, but only after going through two error messages everytime I open the file.

Why is this?

Thanks!

Answer 1

It's a documented issue that Excel will assume a csv file is SYLK if the first two characters are 'ID'.

Venturing into the realm of opinion - it shouldn't, but Excel thinks it knows better than the extension. To be fair, people expect it to be able to figure out cases where the extension really is wrong, but in a case like this assuming the extension is wrong, and then further assuming the file is corrupt when it doesn't appear corrupt if interpreted according to the extension is just mind-boggling.

@John Y points out:

One thing to watch out for: The "workaround" given by the Microsoft issue linked to by @PeterDeGlopper is to (manually) prepend an apostrophe into the file. (This is also advice commonly found on the Web, including StackOverflow, to try to force CSV digits to be treated as strings rather than numbers.) This is not what I'd call good advice, as that injects a literal apostrophe into your data.

@DSM suggests using quoting=csv.QUOTE_NONNUMERIC on the writer. Excel is not confused by a file beginning with "ID" rather than ID, so if the other tools that are going to work with the CSV accept that quoting level this is probably the best solution other than just ignoring Excel's confusion.