0

As per the answers to this question: Stop Excel from automatically converting certain text values to dates I am trying to create a CSV file using the csv module of python, with certain fields formatted as 

"=""<string-that-looks-like-a-date>"

however I am being unable to get the CSV writer to generate output like that. If I use the default csvwriter (no special parameters), and pre-format my data, it tries to quote all my quotes, and I get output that looks like this:

"""=""""6/6"""

If I set the quoting level of the csv writer to QUOTE_NONE, then I get an "Error: need to escape, but no escape char set". Explicitly setting an escape character causes all the quotes I put in to be escaped. Trying just adding an equal sign to my data results in excel showing 

=<string_that_looks_like_a_date>

And so it goes - every combination of Quoting and formatting data I have tried fails to output the desired format.

Is there any way to get the python csvwriter module to output something that excel will interpret as plain text without any extra characters showing up in excel?

Community
  • 1
  • 1
ibrewster
  • 3,482
  • 5
  • 42
  • 54
  • Will you post the original value of the object and what you want it to be converted to? I read char fields from a database and write them into a csv file and it works fine with excel. The char fields contain dates, names, etc... It sounds like you have quotes in your original object that you are reading. – teewuane Feb 11 '14 at 22:06
  • @teewuane As per my description, my data includes various strings that look like (but aren't) dates. So, for example, I may have the string 3/4 in my data, indicating three-out-of-four total. When excel sees this string in a csv file, even if it is quoted, it converts it to the date march fourth of the current year. The answer by john Y seems to resolve the issue, however. – ibrewster Feb 11 '14 at 23:38

2 Answers2

2

You just need to prepend =" and append " to your data. For example:

import csv

with open('test.csv', 'wb') as f:
    w = csv.writer(f)
    data = 'Feb 11'
    w.writerow([data])  # Excel will interpret this as a date
    w.writerow(['="%s"' % data])  # This is what you want (old way)
    w.writerow(['="{0}"'.format(data)])  # Or this, same thing
    w.writerow([f'="{data}"'])  # Or this, also same thing

Note that the string interpolation using the .format() method is available in Python 2.6 and later. In Python 2.7, you can leave out the 0. The f-string interpolation was introduced in Python 3.6.

John Y
  • 14,123
  • 2
  • 48
  • 72
  • Oh, I see. I had thought that didn't work, since the CSV file then contains a couple of extra quotes at the end of the field - for example "=""17/19""". However, it does appear to open and display correctly in excel. – ibrewster Feb 11 '14 at 23:24
0
def excel_literal_print( string ): 
    print("=\"{0}\"".format(string) )

the output file needs to read (unless excel 2007) 

value,="date",="time"
corn3lius
  • 4,857
  • 2
  • 31
  • 36
  • That works fine for outputting to the screen (or a file, if I am writing directly to a file), however as I stated am trying to use the csv module, and the problem arises when said module tries to escape and/or quote my data. – ibrewster Feb 11 '14 at 23:30