How can I stop Excel from formatting values such as 10-3 as a date?

Question

I have a Python script that creates a CSV file, and one of the columns has values such as 4-10, 10-0, etc.

When I open the CSV in Excel it's formatting these values as dates, ex, 4-Oct. When I go to Format Cells and change the type to Text, it changes 4-10 to 43012.

What's the easiest way to stop this?

can you share sample of your python code that creates these values in a CSV? — David Zemens, Mar 24 '17 at 17:21
Try: `output.write('"{0}\t","{1}\t","{2}\t"\n'.format(value1, value2, value3))` — David Zemens, Mar 24 '17 at 17:31

Gary's Student · Accepted Answer · 2017-03-24T17:22:18.743

3

When you import the data into Excel, tell the Import Wizard that the field is Text.

edited Mar 24 '17 at 17:22

answered Mar 24 '17 at 17:19

Gary's Student

95,722
10
59
99

I'm not really importing the data into excel, I was just opening the CSV file. So, I should open a blank Excel document and import the CSV file? – ianbroad Mar 24 '17 at 17:20
Just what I was looking for! It's so dumb that excel does not allow you to turn off the dates when you open the file....your answer was very helpful, thanks! – wllychng Apr 11 '17 at 02:39

score 2 · Answer 2 · edited May 23 '17 at 11:54

My preference is to deal with the inputs, when possible, and in this case if you have control over the python script, it may be preferable to simply modify that, so that Excel's default behavior interprets the file in the desired way.

Borrowing from this similar question with a million upvotes, you can modify your python script to include a non-printing character:

output.write('"{0}\t","{1}\t","{2}\t"\n'.format(value1, value2, value3))

This way, you can easily double-click to open the file and the contents will be treated as text, rather than interpreted as a numeric/date value.

The benefit of this is that other users won't have to remember to use the wizard, and it may be easier to deal with mixed data as well.

Example:

def writeit():
    csvPath = r'c:\debug\output.csv'
    a = '4-10'
    b = '10-0'
    with open(csvPath, 'w') as f:
        f.write('"{0}\t","{1}\t"'.format(a,b))

Produces the following file in text editor:

And when opened via double-click in Excel:

How can I stop Excel from formatting values such as 10-3 as a date?

2 Answers2