(假设Excel 2003…)

当使用文本到列向导时，在步骤3中，您可以为每个列指定数据类型。单击预览中的列，将行为不端的列从“常规”更改为“文本”。

在我的案例中，使用R生成的csv文件中的“Sept8”被Excel 2013转换为“8-Sept”。该问题通过使用xlsx包中的write.xlsx2()函数生成xlsx格式的输出文件来解决，该输出文件可以由Excel加载，而无需进行不必要的转换。因此，如果给你一个csv文件，你可以尝试将它加载到R中，并使用write.xlsx2()函数将其转换为xlsx。

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? .

嗨，我也有同样的问题，

我写这个vbscipt来创建另一个CSV文件。新的CSV文件将在每个字段的字体中有一个空格，因此excel将把它理解为文本。

所以你用下面的代码创建了一个。vbs文件(例如Modify_CSV.vbs)，保存并关闭它。将原始文件拖放到vbscript文件中。它将在相同的位置创建一个文件名为“SPACE_ADDED”的新文件。

Set objArgs = WScript.Arguments

Set objFso = createobject("scripting.filesystemobject")

dim objTextFile
dim arrStr ' an array to hold the text content
dim sLine  ' holding text to write to new file

'Looping through all dropped file
For t = 0 to objArgs.Count - 1
    ' Input Path
    inPath = objFso.GetFile(wscript.arguments.item(t))

    ' OutPut Path
    outPath = replace(inPath, objFso.GetFileName(inPath), left(objFso.GetFileName(inPath), InStrRev(objFso.GetFileName(inPath),".") - 1) & "_SPACE_ADDED.csv")

    ' Read the file
    set objTextFile = objFso.OpenTextFile(inPath)


    'Now Creating the file can overwrite exiting file
    set aNewFile = objFso.CreateTextFile(outPath, True) 
    aNewFile.Close  

    'Open the file to appending data
    set aNewFile = objFso.OpenTextFile(outPath, 8) '2=Open for writing 8 for appending

    ' Reading data and writing it to new file
    Do while NOT objTextFile.AtEndOfStream
        arrStr = split(objTextFile.ReadLine,",")

        sLine = ""  'Clear previous data

        For i=lbound(arrStr) to ubound(arrStr)
            sLine = sLine + " " + arrStr(i) + ","
        Next

        'Writing data to new file
        aNewFile.WriteLine left(sLine, len(sLine)-1) 'Get rid of that extra comma from the loop


    Loop

    'Closing new file
    aNewFile.Close  

Next ' This is for next file

set aNewFile=nothing
set objFso = nothing
set objArgs = nothing

无需修改您的csv文件，您可以:

将excel格式单元格选项更改为“text” 然后使用“文本导入向导”定义csv单元格。 一旦导入，删除该数据 然后粘贴为纯文本

Excel将正确格式化和分离您的CSV单元格为文本格式，忽略自动日期格式。

有点愚蠢的工作，但它比修改csv数据之前导入。安迪·贝尔德(Andy Baird)和理查德(Richard)回避了这种方法，但遗漏了几个重要步骤。

如果在字段的开头放置一个倒置逗号，它将被解释为文本。

例子: 25/12/2008变成了25/12/2008

您还可以在导入时选择字段类型。