CSVを読み込むと予期しない値になる現象を「データの取得と変換」で解消する方法

CSVファイルを読み込むと予期しないセルの書式になってしまうことがある

Excelをよく利用している方は、CSVを読み込むと、文字列が数値に変わってしまうといった経験をしていると思います。

例えば、以下のケースです。

  • 日付の書式が変わってしまう
    2021-5-30 → 2021/5/30
  • 年月日時分秒の日付が「E」を含めた数値に変わってしまう
    20210530120000 → 2.02105E+13
  • ゼロで始まる数値のみの文字列で、先頭のゼロがなくなってしまう
    0123456789 → 123456789
  • 住所の番地が日付に変わってしまう
    2-4-3 → 2002/4/3

以下にサンプルがありますので、サンプルをtest.csvとして保存し、Excelで開くとより理解できると思います。

Excelで開くと、以下のようになってしまいます。

test.csvをExcelで開いた画面

この場合、セルの修正が必要になります。例えばA2の場合、

  1. A2セルを選択する。
  2. 「セルの書式設定」で文字列に変更する。
  3. 「F2」キーを押して、「Enter」キーを押す。

とすることで、CSVと同じ文字列になります。

しかし、ゼロで始まる数値のみの文字列は、先頭のゼロがなくなっているため、先頭に0を入れる必要があり、手間がかかります。

 

このようなデータが1000行あったら・・・。

 

1万行あったら・・・。

 

100万行あったら・・・。

 

1000行だったら、なんとか手動で変更ができるとしても、時間がかかってしまいますよね。

Excelをあまり知らなかったら、それしか方法がないと考えて、頑張って手動で1000行のデータを修正すると思います。

手動で修正は、単調な作業を延々と繰り返すため、間違いに気づかないまま修正してしまうかもしれません。修正が終わったら、我ながらよくやったと満足し、仕事した気になるでしょう。

私も、Excelを始めたばかりのときは、手動で修正し、終わったら「終わったー」と心の中で満足していました。

 

しかし、修正が終わってもどこかで修正した箇所が間違っているかもしれません。

その可能性を考え、次は間違って修正したセルがあるかどうかを探す作業に入ります。その作業でまた時間がかかってしまいます。

そんなのやってられませんよね。

幸い、Excelは簡単な操作で実現できる機能があります。

当時にその機能を知っていれば・・・と思いました。

「データの取得と変換」を利用して変換する

一番簡単な方法としては、「データの取得と変換」を利用し、セルを文字列として変換する方法です。

では、「データの取得と変換」を利用して変換してみましょう。

  1. 「データ」メニューの「データの取得と変換」から「テキストまたは CSV から」をクリックします。
    「データの取得と変換」より「テキストまたは CSV から」をクリックした画面
  2. 「test.csv」を選択し、「インポート」をクリックします。
    「test.csv」を選択し、「インポート」をクリックした画面
  3. そのまま、「データの変換」をクリックします。
    「データの変換」をクリックした画面
  4. Power Query エディターが起動します。
    「Power Query エディター」が起動した画面
  5. 「日付」を文字列に変更するには、「日付」の左にある日付マークをクリックします。
    「Power Query エディター」で「日付」の左の日付マークをクリックする画面
  6. クリック後、変更メニューで「テキスト」をクリックします。
    変更メニューで「テキスト」をクリックした画面
  7. 「列タイプの変更」で、「現在のものを変更」をクリックします。
    列タイプの変更」で、「現在のものを変更」をクリックした画面
  8. マークが「ABC」に変わり、文字列もCSVと同じ文字列に変わりました。
    マークが「ABC」に変わり、文字列もCSVと同じ文字列に変わった画面
  9. その他の列も同様に、「テキスト」に変更します。
    その他の列も同様に、「テキスト」に変更した画面
  10. 変更が終わったら、「閉じて読み込む」をクリックします。
    「閉じて読み込む」をクリックした画面
  11. 変更したデータがシートに読み込まれ、テーブルとして表示されます。この時は、まだクエリと接続されたままになっています。
    変更したデータがシートに読み込まれた画面
  12. クエリと切り離したい場合は、「1行読み込まれました」にカーソルを移動します。
    「1行読み込まれました」にカーソルを移動する画面
  13. 「クエリと接続」の詳細で、「削除」をクリックします。
    クエリと接続」の詳細で、「削除」をクリックした画面
  14. 「よろしいですか?」をメッセージが出ますので、「削除」をクリックします。
    「よろしいですか?」をメッセージが出ますので、「削除」をクリックした画面
  15. 「0個のクエリ」と表示されていれば、クエリと接続が切れた状態になります。この時点は、テーブルになっています。テーブルも外したい場合は、次へ行きます。
    「0個のクエリ」と表示されている画面
  16. 「A1」セルをクリックし、「テーブルデザイン」の「範囲に変換」をクリックします。
    「A1」セルをクリックし、「テーブルデザイン」の「範囲に変換」をクリックした画面
  17. 「テーブルを標準の範囲に変換しますか?」メッセージで、「はい」をクリックします。
    「テーブルを標準の範囲に変換しますか?」メッセージで、「はい」をクリックする画面
  18. これでテーブルが外れました。あとは、背景色やフォントなどを好みにし、「Sheet1」を削除して保存して完了です。
    テーブルが外れた画面

以上です。

 

いかがでしたでしょうか?

 

CSVと同じ状態するだけなら、予期しないセルに変換された列をテキストにするだけで出来るので、簡単だと思います。

Power Query エディターを駆使して、変換マスターになりましょう。