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

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


当前回答

这个问题仍然存在于Mac Office 2011和Office 2013,我无法阻止它发生。这似乎是很基本的事情。

在我的例子中,我有诸如“1 - 2”和“7 - 12”的值,在CSV中正确地用倒逗号括起来,这将自动转换为excel中的日期,如果您尝试随后将其转换为纯文本,您将得到日期的数字表示形式,如43768。此外,它将条形码和EAN编号中的大数字重新格式化为123E+数字,再次无法转换回来。

我发现谷歌驱动器的谷歌表不能将数字转换为日期。条形码中每3个字符有一个逗号,但这些很容易被删除。它处理csv非常好,特别是在处理MAC / Windows csv时。

说不定还能救人。

其他回答

None of the solutions offered here is a good solution. It may work for individual cases, but only if you're in control of the final display. Take my example: my work produces list of products they sell to retail. This is in CSV format and contain part-codes, some of them start with zero's, set by manufacturers (not under our control). Take away the leading zeroes and you may actually match another product. Retail customers want the list in CSV format because of back-end processing programs, that are also out of our control and different per customer, so we cannot change the format of the CSV files. No prefixed'=', nor added tabs. The data in the raw CSV files is correct; it's when customers open those files in Excel the problems start. And many customers are not really computer savvy. They can just about open and save an email attachment. We are thinking of providing the data in two slightly different formats: one as Excel Friendly (using the options suggested above by adding a TAB, the other one as the 'master'. But this may be wishful thinking as some customers will not understand why we need to do this. Meanwhile we continue to keep explaining why they sometimes see 'wrong' data in their spreadsheets. Until Microsoft makes a proper change I see no proper resolution to this, as long as one has no control over how end-users use the files.

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

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

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

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

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

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

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

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

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

而创建字符串要写入我的CSV文件在c#中,我必须这样格式化它:

"=\"" + myVariable + "\""