在Excel中保存为CSV会丢失区域日期格式 [英] Saving to CSV in Excel loses regional date format
问题描述
我有一个.xls我需要转换为.csv
该文件包含一些日期列。日期格式为* 14/03/2001,根据Excel表示日期响应操作系统指定的区域日期和时间设置。
在Excel中,您会看到:
20/01/2013
01/05/2013
另存为... CSV
在记事本中打开:
01/20/2013
05/01/2013
我已经通过将日期格式设置为14/03/2001(否*)暂时修复,甚至还有一些其他没有*的自定义格式,如d / mm /
虽然记住这一点 http://xkcd.com/1179/
最后我决定使用所有CSV文件中的格式 YYYYMMDD
,不会在Excel中转换为日期,但可以正确地由我们的所有应用程序读取。
I have a .xls I need to convert to .csv
The file contains some date columns. The format on the date is "*14/03/2001" which, according to Excel means the date responds to regional date and time settings specified for the OS.
Opening in Excel you see:
20/01/2013
01/05/2013
Save as... CSV
Open in notepad:
01/20/2013
05/01/2013
I have temporarily fixed by setting date formats to "14/03/2001" (no *) but even some other custom formats with no *, like "d/mm/yyyy h:mm" get mangled when saved to CSV.
Although keeping this in mind http://xkcd.com/1179/
In the end I decided to use the format YYYYMMDD
in all CSV files, which doesn't convert to date in Excel, but can be read by all our applications correctly.
这篇关于在Excel中保存为CSV会丢失区域日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!