Excelで読みやすいCSVの日付と時刻の形式を考えてみる

Excelには、CSVという拡張子のファイルを読み込むときに、各列の書式を解析して自動的にセルに書式を割り振るという便利な機能があります。これが便利なときもありますが、かえって邪魔な場合もあります。それなのにこの機能をOFFにすることができません。

特に日付や時刻のデータを含むCSVファイルを読み込むときにこの機能が問題になったりしますので、PythonからCSVを出力するときにどう指定すれば良いか考えてみました。

拡張子をtxtにすると読み込み時に書式を設定できる

CSVファイルの拡張子をtxtにすると、Excelでファイルを開くときに列の書式を自分で設定できるようになります。その際に「文字列」と指定して読み込めば、その列は文字列として読み込まれるので、変に変換されたりしなくなります。

まあ、これでも実用上は一応解決します。

どうせPythonから出力するときに書式を指定するのですから、Excelに優しい書式にしてみます。

まず、下記のようなCSVファイルをExcelで開いてみました。

180123,20180123T010234,2018-01-23T01:02:34
180123,20180123T010234,2018-01-23T01:02:34
180123,20180123T010234,2018-01-23T01:02:34

左の列は、日付を省略するときによく行われる書き方です。yymmddというやつです。Pythonのdatetimeモジュールのstrftimeメソッドに指定する書式としては %y%m%d になります。

真ん中の列はISO 8601の基本形式、右の列はISO 8601の拡張形式です。

これをExcelで開くと、このようになります。

左の列は数値、中と右の列は文字列になりました。

ISO 8601は解釈してくれないわけですね。

続いて、下記のようなCSVファイルをExcelで開いてみました。

18/01/23,12:34:56,2018-01-23 01:02:34.000000
18/01/23,12:34:56,2018-01-23 01:02:34.000001
18/01/23,12:34:56,2018-01-23 01:02:34.001000

左の列は年月日をスラッシュで区切った書き方で、中の列は時分秒をコロンで区切った書き方です。この書き方は馴染みがありますよね。

右の列はdatetimeオブジェクトをそのままprintした場合の出力です。これで作ったCSVをExcelで読み込むと悲劇が起きます。

左の列の書式は日付になりました。中の列の書式は時刻・・・ではなくて、ユーザー設定の書式でh:mm:ssという書式になりました。右の列は明らかに誤ったデータになっていますが、書式はユーザー設定でmm:ss.0というものになりました。

そもそもExcel （Excel 2016）はミリ秒までしかサポートしてませんので、正しく読めないわけですね。

ちなみに、セルの書式を yy/m/d h:mm:ss.000 にするとこうなります。

ミリ秒未満はどこへ行った？データを切り捨てたり丸めたりするくらいなら、そのまま文字列として読んで欲しいものです。

というわけで、Excelで読み込む予定があるCSVデータを出力する場合には、日時のデータは年月日と時分秒とマイクロ秒をそれぞれ別のフィールドに出力する方が良さそうです。

公開日 2018-03-01