有没有人碰巧知道,如果有一个令牌,我可以添加到我的csv的某个字段,这样Excel就不会试图将它转换为日期?

我试图从我的应用程序中编写一个.csv文件,其中一个值碰巧看起来足够像一个日期,Excel会自动将它从文本转换为日期。我曾尝试将所有文本字段(包括看起来像日期的文本字段)放在双引号内,但没有效果。


当前回答

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

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

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

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

其他回答

我做了这个VBA宏,基本上在粘贴数字之前将输出范围格式化为文本。当我想要粘贴诸如8/11、23/6、1/3等值时,它非常适合我,而Excel不会将它们解释为日期。

Sub PasteAsText()
' Created by Lars-Erik Sørbotten, 2017-09-17
Call CreateSheetBackup
    
Columns(ActiveCell.Column).NumberFormat = "@"
    
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

ActiveCell.PasteSpecial

End Sub

我很想知道这是否也适用于其他人。我一直在寻找一个解决这个问题的方法有一段时间了,但我还没有看到一个快速的VBA解决方案,不包括在输入文本前面插入。该代码以原始形式保留数据。

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

在双引号中添加空格前缀解决了这个问题!!

我在csv文件的一个列中有“7/8”这样的数据,MS-Excel将其转换为“07-Aug”。但是使用“LibreOffice Calc”就没有问题了。

为了解决这个问题,我只是给空格字符加上前缀(在7之前添加空格),比如“7/8”,这对我来说很有效。这是为Excel-2007测试的。

不是Excel。Windows可以识别公式、数据作为日期并自动更正。你必须更改Windows的设置。

“控制面板”(->“切换到经典视图”)->“区域和语言” 选项”->选项卡“区域选项”->“自定义…”->选项卡“数字”->和 然后根据你想要的改变符号。

http://www.pcreview.co.uk/forums/enable-disable-auto-convert-number-date-t3791902.html

它将在您的计算机上工作,如果这些设置没有更改,例如在您的客户的计算机上,他们将看到日期而不是数据。

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

"May 16, 2011"

to

"=""May 16, 2011"""