blocks|key|1571032|text|对于第二个精度，yyyy-MM-dd+HH:mm:ss应该可以做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1571033|我认为Excel不太擅长处理秒内的分数(在与COM对象IIRC交互时会丢失分数)。|1571034|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

For second accuracy, yyyy-MM-dd HH:mm:ss should do the trick.

I believe Excel is not very good with fractions of a second (loses them when interacting with COM object IIRC).

blocks|key|1571100|text|"yyyy-MM-dd+hh:mm:ss.000“格式不适用于所有区域设置。对于一些人(至少是丹麦人)来说，"yyyy-MM-dd+hh:mm:ss,000“会工作得更好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1571101|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

"yyyy-MM-dd hh:mm:ss.000" format does not work in all locales. For some (at least Danish) "yyyy-MM-dd hh:mm:ss,000" will work better.

blocks|key|1355783|text|我相信，如果您使用double数据类型，那么在Excel中重新计算将会很好地工作。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1355784|entityMap^0|9|6|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

I believe if you used the <code>double</code> data type, the re-calculation in Excel would work just fine.

blocks|key|1571227|text|转到“控制面板”中的“语言”设置，然后转到“格式选项”，选择一个区域设置并查看Windows默认使用的所选区域设置的实际日期格式。是的，该时间戳格式是区域敏感的。Excel在分析CSV时使用这些格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1571228|此外，如果区域设置使用ASCII码以外的字符，则必须在对应的Unicode+Windows+"ANSI“代码页中发出CSV，例如CP1251。Excel不接受UTF-8。|1571229|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Go to the language settings in the Control Panel, then Format Options, select a locale and see the actual date format for the chosen locale used by Windows by default. Yes, that timestamp format is locale-sensitive. Excel uses those formats when parsing CSV.

Even further, if the locale uses characters beyond ASCII, you'll have to emit CSV in the corresponding pre-Unicode Windows "ANSI" codepage, e.g. CP1251. Excel won't accept UTF-8.

blocks|key|1571175|text|至于时区。我必须将UTC偏移量存储为UTC的秒数，这样Excel/OpenOffice中的公式才能最终本地化datetimes。我发现这比存储前面有0的任何数字更容易。-0900在任何电子表格系统中都不能很好地解析，导入它几乎不可能训练人们去做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1571176|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

As for timezones. I have to store the UTC offset as seconds from UTC that way formulas in Excel/OpenOffice can eventually localize datetimes. I found this to be easier than storing any number that has a 0 in front of it. -0900 didn't parse well in any spreadsheet system and importing it was nearly impossible to train people to do.

blocks|key|1571366|text|我将时间戳写入到CSV文件中，格式为yyyy-mm-dd+hh:mm:ss。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1571367|在Excel+365中我创建一个空白工作簿，转到数据选项卡并选择“从文本/CSV”。|1571368|打开一个带有预览的对话框，奇怪的是，它没有显示时间戳的第一列，但显示它将转换第二列。|1571369|您可以“加载”数据，也可以选择“转换数据”。|1571370|现在你在"Power+Query+Editor“中，你可以利用Excel如何引入这些列。|1571371|在我的例子中，我撤消了它所做的任何自动更改，并使用功能区上的一个小按钮告诉它两列都是数据类型“日期/时间”。|1571372|按Close+&+Load将其作为数据源导入Excel中，您可以对其进行排序等操作。|1571373|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

I wrote my timestamps to the CSV file as yyyy-mm-dd hh:mm:ss.
In Excel 365 I create a blank workbook, go to the Data tab and choose &quot;From Text/CSV.
A dialog opens with a preview, which weirdly leaves the first column of timestamps alone, but shows it is going to convert the second column.
You can either &quot;Load&quot; the data, or &quot;Transform Data&quot;, which is what I choose.
Now you're in the &quot;Power Query Editor&quot; and you can massage how Excel will bring the columns in.
In my case I undo any automatic changes it made, and tell it both columns are Data Type &quot;Date/Time&quot; using a little button on the ribbon.
Pressing Close &amp; Load brings it into Excel as a data source that you can sort etc.

blocks|key|1570971|text|我猜ISO-format是个好主意。(Wikipedia+article，也有时间信息)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1570972|entityMap|0|LINK|mutability|MUTABLE|url|http://www.w3.org/QA/Tips/iso-date|1|http://en.wikipedia.org/wiki/ISO_8601^0|2|A|0|J|H|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

I would guess that <a href="http://www.w3.org/QA/Tips/iso-date" rel="nofollow noreferrer">ISO-format</a> is a good idea. (<a href="http://en.wikipedia.org/wiki/ISO_8601" rel="nofollow noreferrer">Wikipedia article</a>, also with time info)

blocks|key|1571277|text|给定一个具有以下格式的日期时间列的csv文件:+yyyy-mm-dd+hh:mm:ss|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1571278|Excel以以下格式显示:+dd/mm/yyyy+hh:mm|1571279|例如2020-05-22+16:40:55显示为22/05-2020+16:40|1571280|这显然是由Windows中选择的短日期和短时间格式决定的；例如，如果我将Windows中的短日期格式更改为yyyy-mm-dd，Excel将显示2020-05-22+16:40。|1571281|令人恼火的是，我找不到任何方法让Excel自动显示秒数(我必须在Excel中手动设置列的格式)。但是，如果csv文件包含hh:mm:ss格式的时间列(例如16:40:55)，这就是Excel中显示的时间列，包括秒。|1571282|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

Given a csv file with a datetime column in this format: yyyy-mm-dd hh:mm:ss

Excel shows it in this format: dd/mm/yyyy hh:mm

e.g. 2020-05-22 16:40:55 shows as 22/05/2020 16:40

This is evidently determined by the Short date and Short time format selected in Windows; for example, if I change the Short date format in Windows to yyyy-mm-dd, Excel shows 2020-05-22 16:40.

Annoyingly, I can't find any way to make Excel show the seconds automatically (I have to manually format the column in Excel). But if the csv file includes a time column in hh:mm:ss format (e.g. 16:40:55), that's what shows in Excel, including the seconds.

I'm writing a CSV file. I need to write timestamps that are accurate at least to the second, and preferably to the millisecond. What's the best format for timestamps in a CSV file such that they can be parsed accurately and unambiguously by Excel with minimal user intervention?

Best timestamp format for CSV/Excel?

我正在写一个CSV文件。我需要编写至少精确到秒，最好是毫秒的时间戳。CSV文件中时间戳的最佳格式是什么，以便Excel可以在最少的用户干预下准确而明确地解析它们？

问CSV/Excel的最佳时间戳格式？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问CSV/Excel的最佳时间戳格式？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问CSV/Excel的最佳时间戳格式？
EN