【Excel】米国表記の日付が入ったCSVファイルが正しく読めない：Tech TIPS
海外の統計情報などのCSVファイルをExcelで読み込むと日付が正しく認識されないことがある。これは、Excelが自動的に日付を日本国内の表記に合わせてしまうためだ。このような場合の解決方法を紹介しよう。
この記事は会員限定です。会員登録（無料）すると全てご覧いただけます。
対象：Office 2013／2016／2019／365
「Microsoft Excel（エクセル）」で、海外の統計情報などのCSVファイルを読み込んだ際に日付データの読み込みに失敗した経験はないだろうか。米国式の「月、日、年」などの順で記述された日付データを、日本語版Excelで読み込むと、日本国内で多く使われる「年、月、日」の順で日付を解釈しようとして失敗してしまうことがある。
特に、西暦を省略して2桁表記（2012年1月3日を「1/3/12」といった具合に表記）とすると、年を判断する手掛かりがなくなるため、解釈の幅が広がり、米国式表記を日本式表記として解釈できるようになってしまう。しかし、日付によっては、途中から書式に合わなくなり、日付データにならない場合などがある。
このような場合には、［テキストファイル］ウィザードやPower Queryの「データの変換」を使い、CSVファイルの読み込み時に日付データの形式を指定することで、セルに正しい日付データ（シリアル値）を書き込むことが可能になる。
なお、タブ区切りのテキストファイルなども同じやり方で日付書式を指定できる。対象は、CSVだけでなく、テキストファイルや類似のファイル形式全般だ。ただし、表記が煩雑になるため、ここでは一括して「CSVファイル」として表記する。
［テキストファイル］ウィザードとは？
［テキストファイル］ウィザードとは、「テキストファイル」をExcelで開いたときに自動的に起動される機能だ。これを使うと、読み込み時に日付テキストの書式指定（年、月、日の並び）をユーザーが指定できるようになる。
Excelは、拡張子が「.csv」になっているCSVファイルには関連付けがなされており、エクスプローラーからCSVファイルをダブルクリックするなどしてExcelを起動させることができる。しかし、この方法でCSVファイルを開いてしまうと、日付データは、全て日本式の「年、月、日」で並んでいると解釈され、これに合わないパターンは日付として認識されない。
［テキストファイル］ウィザードを起動して読み込むには、CSVファイルの拡張子を事前に「.txt」に変更後、起動したExcel側からファイルを開いて読み込みを行う。［ファイル］タブの［開く］−［参照］を使い、［ファイルを開く］ダイアログを表示させる。
ファイル名の拡張子を「.txt」にしてExcelで読み込むExcelを起動し、［ファイル］タブから［開く］−［参照］を選択して該当のテキストファイルを開く。CSVファイルの場合、事前にコピーするなどして拡張子を「.txt」にしたものを用意する。
［ファイルを開く］ダイアログで目的のファイルがあるフォルダを開いたら、右下のドロップダウンリストから［テキストファイル］を選んで、対象のファイルを選択する。
すると、［テキストファイル］ウィザードが起動する。このウィザードは3ページあり、最初のページでは、「データの形式」「読み込み開始行」「文字コード」などを指定する。CSVファイルなら「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選ぶ必要がある。もっとも、これがデフォルト設定なので、確認するだけでよい。CSVファイルでは先頭行に項目名などが入っていることがあり、その場合には「先頭行をデータの見出しとして使用する」のチェックを「オン」にする。
［次へ］ボタンで2ページ目に移動したら「区切り文字」の「カンマ」の前にあるチェックを「オン」にする。CSVファイルなら「データのプレビュー」が項目の間に正しく線が引かれた状態になるはずである。
さらに［次へ］ボタンで3ページ目に移動したら、「データのプレビュー」で、日付のある列を選択し、「列のデータ形式」で［日付］を選択、日時の並びを指定する。
このドロップダウンリストでは、「年、月、日」が「Y、M、D」で表現されており、米国式の「月、日、年」ならば［"MDY"］を選択する。この指定は列ごとに行う必要があり、複数の日付列があるなら、それぞれで指定する必要がある。最後に［完了］ボタンを押せば、日付を正しく解釈してCSVファイルが読み込まれるはずである。
［テキストファイル］ウィザードでCSVファイルを読み込む（1）［テキストファイル］ウィザードの1ページ目では、ほとんど確認のみで、データの内容によって「先頭行を……」のチェックやファイル形式を指定する。
［テキストファイル］ウィザードでCSVファイルを読み込む（3）最後のページで、データのプレビュー欄で日付列を選択し、列のデータ形式で［日付］を選択して、該当の書式（米国式なら「MDY」）を選択する。すると、データのプレビューで日付列のヘッダ部分の表示が、選択した書式の略称に変わる。
このやり方は、日付の書式指定以外にも、文字コードの指定なども行うことが可能だ。CSVファイルをエクスプローラーから開いて、文字化けしているような場合には、この方法を試してみるとよい。むしろ、CSVファイルはエクスプローラーから直接開かず、Excel内から開いた方がトラブルは少ない。
Power Queryを使う場合
CSVファイルを扱う方法として、Tech TIPS「【Excel】毎日集計のCSVファイルもクエリ機能で一発自動更新」でPower Queryを使う方法を紹介した。
この方法でも、日本式以外の日付形式を読み込ませることは可能だ。ただし、前記記事で説明した手順でシートにCSVファイルを読み込む前に、日付書式を指定する作業が必要になる。
同一ファイル名のCSVファイルを繰り返し読み込む必要があるなら、Power Queryを使う方が効率がよい。というのは、日付部分の書式の変換はPower Queryならば一回設定すれば、以後、読み込みのたびに変換を行ってくれるからだ。前述の［テキストファイル］ウィザードでは、CSVファイルを読み込むたびにウィザードを実行する必要がある。また、Power Queryなら、CSVファイルの拡張子を.txtに変更する必要もない。
Excelを起動し、［データ］タブを開き、［テキストファイル］をクリックして、目的のCSVファイルやテキストファイルを開く（詳細な手順はTech TIPS「【Excel】毎日集計のCSVファイルもクエリ機能で一発自動更新」参照のこと）。Power Queryのダイアログが表示されるので、日付が正しく認識されているか確認する。ここで問題がないようならば、［読み込み］ボタンをクリックして、CSVファイルの読み込みを行う。
Power Queryを使ってCSVファイルを読み込む（3）Power Queryのプレビューが表示されるので、問題がない場合は［読み込み］ボタンをクリックする。また、日付の認識が間違っている場合には［データの変換］ボタンをクリックする。
Power Queryのプレビューで日付が正しく認識されなかった場合
日付が正しく認識されていない場合は、ここで［データの変換］ボタンを押す。すると［Power Queryエディター］ウィンドウが開くので、書式を変更したい日付列を選択する。日付列の見出し部分で右クリックし、［型の変更］−［テキスト］を選択する。確認のダイアログが表示されるので、［現在のものを置換］を選択する。
［Ａ］
これで、列が元のファイルと同じ状態になったはずである。ここで再度、日付列の見出し行で右クリックし、［型の変更］−［ロケールを使用］を選ぶ。すると、型とロケールを選択するダイアログが表示されるので、「データ型」に［日付］を、「ロケール」に［英語（米国）］を選択する。
これで［OK］ボタンを押せば、Power Queryエディターのプレビューが正しく日付を解釈したものになる。複数の日付列がある場合は、列ごとに同じ処理を繰り返す。最後にウィンドウ右上の［閉じて読み込む］をクリックすれば、Power Queryによって日付が正しく解釈されたテーブルが作られるはずだ。
Power Queryを使ってCSVファイルを読み込む（10）日付部分を確認し、正しく認識されていたら、［Power Queryエディター］ウィンドウの左上にある［閉じて読み込む］アイコンをクリックする。
ロケールの選択は、日付形式に合わせたものを選択する必要がある。「月、日、年」の順ならば、上記のように「英語（米国）」でよい。一方、「日、月、年」という日本の逆順になっている場合には、「英語（世界）」が対応している。なお、Microsoftからは、ロケールと標準の日付文字列書式の対応を示すドキュメントは公開されていない。このため、これ以外の日付書式（年月日の並び）については、作成者の利用言語などからロケールを推測して試してみる他ないが、多くの場合、日本式の「年、月、日」以外は、「月、日、年」か「日、月、年」で何とかなるのではないかと思われる。
型の変更でテキストを一回選択するのは、日付として認識されている状態では、西暦年が4桁に書き換えられていて、ロケールを指定しても正しく認識してくれないからだ。一回テキストとして認識させ、元の状態に戻してからロケールを適用しなければならないようだ。仕様上の問題なのか、バグなのか少々「納得」しかねる手間である。
日付の表示は国ごとに様式が違うため、CSVやテキストファイルから読み込む場合には注意が必要だ。
また、最近ではUTF-8形式のテキストファイルも増えてきている。ところが、シフトJISを前提にしたExcelでは、読み込み時の自動変換だけだと文字化けすることが少なくない。というのも、UTF-8形式ではオプションとされていて、利用が任意なBOM（Byte Order Mark）が付いていることをExcelが必須としているからだ。実際、BOMが付いていないUTF-8形式のテキストファイルはよく見かけるため、文字化けにも遭遇しやすい。
［テキストファイル］ウィザードやPower Query経由でCSVやテキストファイルを読み込むことで、文字コードや日付形式の手動指定が可能になる。自動変換にまかせて読み込んでおかしくなって再度、読み込み直しとなる手間を考えると、そろそろエクスプローラーからCSVファイルを直接Excelで開く時代は終わりといえそうだ。
Copyright© Digital Advantage Corp. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。