Stop Excel from automatically converting certain text values to dates

CSV - comma separated values. Just create/edit through text editor instead of xls/xlsx/exel. In editing you can set date in required format and it must be kept intact. This is assuming same file is then getting processed programatically.

I know this is an old question, but the problem is not going away soon. CSV files are easy to generate from most programming languages, rather small, human-readable in a crunch with a plain text editor, and ubiquitous.

The problem is not only with dates in text fields, but anything numeric also gets converted from text to numbers. A couple of examples where this is problematic is with ZIP/postal codes, telephone numbers, and government ID numbers, which sometimes can start with one or more zeroes (0), which get thrown away when converted to numeric.

Two cases that I can think of that the "prepending =" solution, as mentioned previously, might not be ideal is where the file might be imported into a program other than MS Excel (MS Word Mail Merge comes to mind) or where human-readability might be important.

What I have figured out is that if one pre/appends a non-numeric and/or non-date character in the value, the value will be recognized as text and not converted. However, the plain old space character doesn't work for this as it gets chopped off by Excel and then the value still gets converted. But there are various other printing and non-printing space characters that will work well. The easiest however is to append (add after) the simple tab character (\t, ASCII 9). It doesn't bother the importation, normally does not bother Mail Merge results, is not a big hindrance when viewing the CSV in Notepad, and could be removed by find/replace in Excel. You don't need to import the CSV, but can simply double-click to open the CSV.

Another option might be to generate XML files, for which a certain format also is accepted for import by newer MS Excel versions, and which allows a lot more options similar to .XLS format, but I don't have experience with this.

So there are various options. Depending on your requirements/application, one might be better than another.

I had a similar problem and this is the workaround that helped me without having to edit the csv file contents:

If you have the flexibility to name the file something other than ".csv", you can name it with a ".txt" extension, such as "Myfile.txt" or "Myfile.csv.txt". Then when you open it in Excel (not by drag and drop, but using File->Open or the Most Recently Used files list), Excel will provide you with a "Text Import Wizard".

In the first page of the wizard, choose "Delimited" for the file type.

In the second page of the wizard choose "," as the delimiter and also choose the text qualifier if you have surrounded your values by quotes

In the third page, select every column individually and assign each the type "Text" instead of "General" to stop Excel from messing with your data.

Hope this helps you or someone with a similar problem!

it I have found that putting an = before the double quotes will accomplish what you want. It forces the data to be text.

eg.

="2008-10-03",="more text"

has worked for me

Here is the simple method we use at work here when generating the csv file in the first place, it does change the values a bit so it is not suitable in all applications:

Prepend a space to all values in the csv

This space will get stripped off by excel from numbers such as " 1"," 2.3" and " -2.9e4" but will remain on dates like " 01/10/1993" and booleans like " TRUE", stopping them being converted into excel's internal data types.

It also stops double quotes being zapped on read in, so a foolproof way of making text in a csv remain unchanged by excel EVEN IF is some text like "3.1415" is to surround it with double quotes AND prepend the whole string with a space, i.e. (using single quotes to show what you would type) ' "3.1415"'. Then in excel you always have the original string, except it is surrounded by double quotes and prepended by a space so you need to account for those in any formulas etc.

SELECT CONCAT('\'',NOW(),'\''), firstname, lastname 
FROM your_table
INTO OUTFILE 'export.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n'

Working off of Jarod's solution and the issue brought up by Jeffiekins, you could modify

"May 16, 2011"

to

"=""May 16, 2011"""

WARNING: Excel '07 (at least) has a(nother) bug: if there's a comma in the contents of a field, it doesn't parse the ="field, contents" correctly, but rather puts everything after the comma into the following field, regardless of the quotation marks.

The only workaround I've found that works is to eliminate the = when the field contents include a comma.

This may mean that there are some fields that are impossible to represent exactly "right" in Excel, but by now I trust no-one is too surprised.

An alternate method:

Convert the format of the column you want to change to 'Text'. Select all the cells you want to preserve, copy. Without deselecting those columns, click "Edit > Paste Special > As values"

Save as CSV. Note that this has to be the last thing you do to the file because when you reopen it, it will format itself as dates since cell formats cannot be saved in CSV files.

If you put an inverted comma at the start of the field, it will be interpreted as text.

Example: 25/12/2008 becomes '25/12/2008

You are also able to select the field type when importing.

(Assuming Excel 2003...)

When using the Text-to-Columns Wizard has, in Step 3 you can dictate the data type for each of the columns. Click on the column in the preview and change the misbehaving column from "General" to "Text."