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: ZIP/postal codes telephone numbers government ID numbers which sometimes can start with one or more zeroes (0), which get thrown away when converted to numeric. Or the value contains characters that can be confused with mathematical operators (as in dates: /, -). 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's Mail Merge function comes to mind), where human-readability might be important. My hack to work around this 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. A non-printing character would be good as it will not alter the displayed value. However, the plain old space character (\s, ASCII 32) 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). Benefits of this approach: Available from keyboard or with an easy-to-remember ASCII code (9), It doesn't bother the importation, Normally does not bother Mail Merge results (depending on the template layout - but normally it just adds a wide space at the end of a line). (If this is however a problem, look at other characters e.g. the zero-width space (ZWSP, Unicode U+200B) is not a big hindrance when viewing the CSV in Notepad (etc), and could be removed by find/replace in Excel (or Notepad etc). You don't need to import the CSV, but can simply double-click to open the CSV in Excel. If there's a reason you don't want to use the tab, look in an Unicode table for something else suitable. 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. Addition It needs to be said that newer versions (2013+) of MS Excel don't open the CSV in spreadsheet format any more - one more speedbump in one's workflow making Excel less useful... At least, instructions exist for getting around it. See e.g. this Stackoverflow: How to correctly display .csv files within Excel 2013? .

(excel2007及以上版本)

如何强制excel不“检测”日期格式而不编辑源文件

:

将文件重命名为.txt 如果你不能这样做，而不是直接在excel中打开CSV文件，创建一个新的工作簿，然后转到 数据>从文本中获取外部数据>并选择您的CSV。

无论哪种方式，你都会看到导入选项，只需选择每个包含日期的列，并告诉excel格式为“文本”而不是“一般”。

根据Jarod的解决方案和Jeffiekins提出的问题，你可以进行修改

"May 16, 2011"

to

"=""May 16, 2011"""

另一种方法:

将要更改的列的格式转换为“Text”。选择要保留的所有单元格，复制。在不取消这些列的情况下，单击“编辑>粘贴特殊> As值”

保存为CSV。请注意，这必须是您对文件所做的最后一件事，因为当您重新打开它时，它将自己格式化为日期，因为单元格格式不能保存在CSV文件中。

这是我知道如何在不搞乱文件本身的情况下完成这一点的唯一方法。就像使用Excel一样，我用头在桌子上敲了几个小时才学会这一点。

Change the .csv file extension to .txt; this will stop Excel from auto-converting the file when it's opened. Here's how I do it: open Excel to a blank worksheet, close the blank sheet, then File => Open and choose your file with the .txt extension. This forces Excel to open the "Text Import Wizard" where it'll ask you questions about how you want it to interpret the file. First you choose your delimiter (comma, tab, etc...), then (here's the important part) you choose a set columns of columns and select the formatting. If you want exactly what's in the file then choose "Text" and Excel will display just what's between the delimiters.

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: ZIP/postal codes telephone numbers government ID numbers which sometimes can start with one or more zeroes (0), which get thrown away when converted to numeric. Or the value contains characters that can be confused with mathematical operators (as in dates: /, -). 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's Mail Merge function comes to mind), where human-readability might be important. My hack to work around this 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. A non-printing character would be good as it will not alter the displayed value. However, the plain old space character (\s, ASCII 32) 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). Benefits of this approach: Available from keyboard or with an easy-to-remember ASCII code (9), It doesn't bother the importation, Normally does not bother Mail Merge results (depending on the template layout - but normally it just adds a wide space at the end of a line). (If this is however a problem, look at other characters e.g. the zero-width space (ZWSP, Unicode U+200B) is not a big hindrance when viewing the CSV in Notepad (etc), and could be removed by find/replace in Excel (or Notepad etc). You don't need to import the CSV, but can simply double-click to open the CSV in Excel. If there's a reason you don't want to use the tab, look in an Unicode table for something else suitable. 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. Addition It needs to be said that newer versions (2013+) of MS Excel don't open the CSV in spreadsheet format any more - one more speedbump in one's workflow making Excel less useful... At least, instructions exist for getting around it. See e.g. this Stackoverflow: How to correctly display .csv files within Excel 2013? .