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で開くとより理解できると思います。
|
1 2 |
日付,年月日時分秒,ゼロから始まる数値のみの文字列,住所の番地 2021-5-30,20210530120000,0123456789,2-4-3 |
Excelで開くと、以下のようになってしまいます。
この場合、セルの修正が必要になります。例えばA2の場合、
- A2セルを選択する。
- 「セルの書式設定」で文字列に変更する。
- 「F2」キーを押して、「Enter」キーを押す。
とすることで、CSVと同じ文字列になります。
しかし、ゼロで始まる数値のみの文字列は、先頭のゼロがなくなっているため、先頭に0を入れる必要があり、手間がかかります。
このようなデータが1000行あったら・・・。
1万行あったら・・・。
100万行あったら・・・。
1000行だったら、なんとか手動で変更ができるとしても、時間がかかってしまいますよね。
Excelをあまり知らなかったら、それしか方法がないと考えて、頑張って手動で1000行のデータを修正すると思います。
手動で修正は、単調な作業を延々と繰り返すため、間違いに気づかないまま修正してしまうかもしれません。修正が終わったら、我ながらよくやったと満足し、仕事した気になるでしょう。
私も、Excelを始めたばかりのときは、手動で修正し、終わったら「終わったー」と心の中で満足していました。
しかし、修正が終わってもどこかで修正した箇所が間違っているかもしれません。
その可能性を考え、次は間違って修正したセルがあるかどうかを探す作業に入ります。その作業でまた時間がかかってしまいます。
そんなのやってられませんよね。
幸い、Excelは簡単な操作で実現できる機能があります。
当時にその機能を知っていれば・・・と思いました。
「データの取得と変換」を利用して変換する
一番簡単な方法としては、「データの取得と変換」を利用し、セルを文字列として変換する方法です。
では、「データの取得と変換」を利用して変換してみましょう。
- 「データ」メニューの「データの取得と変換」から「テキストまたは CSV から」をクリックします。
- 「test.csv」を選択し、「インポート」をクリックします。
- そのまま、「データの変換」をクリックします。
- Power Query エディターが起動します。
- 「日付」を文字列に変更するには、「日付」の左にある日付マークをクリックします。
- クリック後、変更メニューで「テキスト」をクリックします。
- 「列タイプの変更」で、「現在のものを変更」をクリックします。
- マークが「ABC」に変わり、文字列もCSVと同じ文字列に変わりました。
- その他の列も同様に、「テキスト」に変更します。
- 変更が終わったら、「閉じて読み込む」をクリックします。
- 変更したデータがシートに読み込まれ、テーブルとして表示されます。この時は、まだクエリと接続されたままになっています。
- クエリと切り離したい場合は、「1行読み込まれました」にカーソルを移動します。
- 「クエリと接続」の詳細で、「削除」をクリックします。
- 「よろしいですか？」をメッセージが出ますので、「削除」をクリックします。
- 「0個のクエリ」と表示されていれば、クエリと接続が切れた状態になります。この時点は、テーブルになっています。テーブルも外したい場合は、次へ行きます。
- 「A1」セルをクリックし、「テーブルデザイン」の「範囲に変換」をクリックします。
- 「テーブルを標準の範囲に変換しますか？」メッセージで、「はい」をクリックします。
- これでテーブルが外れました。あとは、背景色やフォントなどを好みにし、「Sheet1」を削除して保存して完了です。
以上です。
いかがでしたでしょうか？
CSVと同じ状態するだけなら、予期しないセルに変換された列をテキストにするだけで出来るので、簡単だと思います。
Power Query エディターを駆使して、変換マスターになりましょう。