Fixing extra double-quotes in csv for string with double-quote at start and middle?

Question

Trying to write lines to a csv file. When adding a string with double-quotes at the begining and in the middle of the string, I get extra double-quotes when I open it as .txt but no problem when opened as .xlsx.

I've played around with quoting=QUOTE_NONE and quotechar='//' and escapechar='//', but can't figure out what works.

Here's the code:

import csv

csv_file = open('file.csv', 'w', newline='')
file_writer = csv.writer(csv_file)
file_writer.writerow(['Name', 'Search term'])

name = 'Patrick Fox'
search_term = '"%s" mp' % name
file_writer.writerow([name, search_term])

^ Issue is with the search_term. Expected output is "Patrick Fox" mp for both .txt and .xlsx. Actual output is """Patrick Fox"" mp" for .txt

Answer 1

I think you can't because the double quotes after the delimiter would be considered as special quoting in XLS format and the reader won't show it but can be seen on the raw format (as a text file). So I think you have two options:

Use single quotes instead of double quotes:

import csv

csv_file = open('file.csv', 'w', newline='')
file_writer = csv.writer(csv_file)
file_writer.writerow(['Name', 'Search term'])

name = 'Patrick Fox'
search_term = '{!r} mp '.format(name)
file_writer.writerow([name, search_term])

Another option is to add a single space character in the second column. Also, I set the quoting to None. You can find more about CSV formatting in here.

import csv


csv_file = open('file.csv', 'w', newline='')
file_writer = csv.writer(
    csv_file, quoting=csv.QUOTE_NONE, quotechar='\\')
file_writer.writerow(['Name', 'Search term'])

name = 'Patrick Fox'
search_term = ' \"{!s}\" mp '.format(name)
file_writer.writerow([name, search_term])