CSV 文件的最佳日期格式是什么,以便 Excel 在加载时不会弄乱它?

CSV 文件的最佳日期格式是什么,以便 Excel 在加载时不会弄乱它?

这是一个 CSV 文件的示例:

timestamp,datetime,price,volume,is_buy,cycle,realized_pnl,unrealized_pnl,fees_paid,margin_use,volume_traded,average_price,position_price,order_long_price,order_short_price
1596112043043,2020-07-30 12:27:23.043,319.5,5,false,0,0,0.0,0,0,0,319.5,0,271.58,-367.42
1596112045045,2020-07-30 12:27:25.045,319.5,0.269,false,0,0,0.0,0,0,0,319.5,0,271.58,-367.42
1596112045045,2020-07-30 12:27:25.045,319.49,3.645,false,0,0,0.00,0,0,0,319.50,0,271.58,-367.42

时间戳是 Unix 时间戳,也是代码真正使用的内容。约会时间列是“人类可读的”日期字符串。

但是当 Excel 加载该 CSV 时,它看起来像这样:

在此处输入图片描述

我尝试使用“/”和“-”作为日期分隔符,但结果相同。

有趣的是,一旦我编辑一个单元格但不做任何更改,Excel 就会正确显示它。

所以,我的问题是:我可以使用什么字符串格式来表示日期和时间,以便 Excel 在加载 CSV 时正确显示它?

答案1

首先,格式问题。Excel 查看前 8 条记录(是的,只有 8 条,即使对于 500,000 行导入也是如此)来决定要应用的格式。其中最重要的一半是,您几乎肯定会将 12:27:23 显示为 27:23.0,因为 CSV 中的第一个记录的时间非常接近午夜,所以说 00:01:25.043。Excel 会看到这样的 8 条记录,并计算出您需要显示分钟、秒和一位小数。无论您有 400,000 条记录,还是有 28 或 435 或任何其他带有“00”表示小时的记录。

这指出了一个更大的问题:为什么日期不包含在该格式中?如果你查看那些第一条记录,你可能会发现日期不存在,或者在某种程度上,值为 0。因此,Excel 只会看到日期和时间格式,并将其也破坏。

你可以做很多事情。Power Query 是你的救星,我很快就会对此进行一些介绍。但按照亚特兰蒂斯探索节目的传统,让我们花 58 分钟来嘲笑其他解决方案,然后再花一两秒钟来谈论 PQ。

首先,VBA。其实不要对此嗤之以鼻。只需打开文件,然后运行一个宏来调整您需要格式化的列的格式。一次编写,多次使用。不会。不,不会流行。一点努力,永远受用。在打开大量文件之前,将其放入您打开的某个无人机文件中,并让它对打开的 CSV 进行操作。

其次,创建一个具有所需格式的模板文件。打开 CSV 并立即将数据复制并粘贴到模板文件中。全部完成。使用正确的名称保存并继续下一个。

第三,手动格式化。我敢打赌这里没有很多列,所以这并不难忍受。手动格式化可以通过模板文件完成,您将第一行复制到剪贴板,然后粘贴 | 特殊 | 格式化(或Alt-E, S, T)到新打开的 CSV 的所有行。所以,实际上并不难,也不耗时。

好了,够了。注意你反对每一项的线索。你可能会想“不喜欢宏”或“不懂 VBA”或“老兄,我什么都不用手做”或“打开一个模板,复制粘贴或粘贴一次即可获得格式,唉,这太麻烦了”,但我敢打赌你会回到“大量文件”的问题上。

大量的文件,大量的工作,而你的生命只有这么多年。这就是 VBA 的用途。VBA 确实是一个奇迹,可以满足你所描述的需求。天哪,甚至,可怕的是,花几百美元请人编写所需的宏,以便打开一个又一个文件并进行格式化,然后以适当的名称保存,将其放置在需要的目录结构中,甚至给他们发送电子邮件或诸如此类的事情……所有这些只需几百美元。一个拥有大量文件需要处理的组织有足够的资金来做这样的事情,并且老板会尊重那些认为最佳解决方案不是他的人,并且会以经济高效和省时的方式处理这种情况。

但说实话,PQ 在许多方面都比 VBA 做得更好。它可以设置为导入和修改数据格式等,而不需要太多麻烦。然后它将所有这些保留在自身中(经典的“数据模型”),并且可以毫无怨言地处理一百万个文件。它可以处理内存所能处理的行数,而不仅仅是像 Excel 那样处理一百万行。并且可以通过 Power Pivot(Excel 附带)和 Excel 以及许多其他程序访问其结果。这里的一个弱点是它正在查看的文件名,但它的 SQL(它通过 SQL 完成所有这些,如果您愿意,可以“在后台”完成)可以由任何了解 SQL 的人编辑。需要外行吗?回到那几百美元的事情,但 SQL 女孩的收费可能比 VBA 专家高一些。无论如何,如果您愿意,它应该能够被修改以处理一个充满文件的目录。

而且,如果需要的话,它比 VBA 有一个巨大的优势:它可以自然而轻松地合并这些文件。您曾想过使用 Excel 本身来分析数据,但却有 1500 万行数据?必须将其拆分成大量文件才能导入电子表格,然后拼凑公式来读取全部数据并使其清晰可见?并没有要求这样做,但是 25 年前编写了导出例程的提供商只是以这种方式提供,因为任何其他方式都需要工作,而这只需单击鼠标即可?PQ 可以将它们无缝地重新组合在一起,就像从未被分解过一样。这是来自 25 个不同来源的类似材料,而您的项目实际上是将它们组合在一起?PQ 也是一样,同样易于操作。

所以...如果您的任务只是将其导入并保存为具有适当格式的 Excel 文件以供其他人使用,那么 VBA 就是您的最佳选择。如果您需要自己使用它,或者下一步需要将其组合在一起进行更大规模的分析,那么老板会惊讶地发现,他必须使用 PQ 一举完成两个项目的预算。

由于您明确表示不想在每次导入时投入大量精力,所以我之前略过了一个小细节:如果您在编辑器中打开每个文件,并将包含更多“详细”数据的十几行或两行移到文件的顶部行,Excel 在判断如何格式化列时将参考更详细的行。如果它首先看到您的样本材料中的第一行,那么您就会看到完整的日期和时间显示,如下所示: 2020-07-30 12:27:23.0这些都不会让您烦恼。

但是……这可能比格式化需要更多的工作,所以我想你不会感兴趣。

您还可以强制 Excel 查看您想要查看的行数,而不是 8 行。但是,请记住三件事:

  1. 你必须研究一下,因为我不记得是怎么研究的了。已经很久了。我在另一个问题中提到过,那里有“常见的(知识渊博的)嫌疑人”,但没有人向海报上说明这一点,所以似乎不是随便发现的。
  2. 您的数据可能经常会有很多无用的行排在最前面。感谢导出时按此方式排序的那个人。但是,如果您要导入 50 万行数据,那么前 10-20,000 行可能在午夜到凌晨 1 点之间,而要获得包含完整详细信息的行可能要远远超过这个时间。这可能意味着导入过程会长时间滞后,甚至可能意味着 Excel 会卡住并失败。
  3. 考虑到这种延迟,在编辑器中打开文件并将完全具有代表性的行移至开头,或者实际上只是将一组(比如说 10-20 行)完全具有代表性的数据复制到一个文件中,然后将这些行复制到每个文件的开头,并在导入时立即删除这些行,这样可能会更简单。考虑到在开始导入之前有 10-20,000 行或更多行,导入过程的快速性与停滞不前相比,应该很容易胜过刚刚描述的工作。

相关内容