Excel Office365

ExcelでCSVファイルを開く時はダブルクリックは絶対するな

2019年8月13日

CSVファイルを読み込むときに注意しないといけないことがある。
エクスプローラーからCSVファイルをダブルクリックすると先頭に
0が入っている文字列が数字と認識されて先頭0が取れてしまうのだ。
excel
先頭0を取れないようにCSVファイルをExcelに読み込むにはどうしたら良いのか? 😥
この記事では以下のことについて記載しています。
・ExcelをCSVファイルで安全に開く方法がわかる
・CSVファイルを加工する時に使えるExcelのサポート機能を紹介
システムエンジニアとして22年間Excelを使ってきた私がExcelでCSVファイルを扱う時の注意点を解説します。




目次【本記事の内容】

1.ExcelをCSVファイルで安全に開く方法がわかる

1-1 Excelで郵便番号住所一覧を開く

郵便番号は先頭に0(ゼロ)が来る地域がある。北海道だ。何故そんな事を私が知っているかと言うとこのせいでシステムトラブルを起こしてしまった経験があるからだ。
だから仕事で郵便番号を扱うときには最新の注意をしてデータを扱うようにしている。
まずは実験のデータとして使える郵便番号、住所一覧をダウンロードしよう。以下のサイトからダウンロードできる。
https://www.post.japanpost.jp/zipcode/download.html
ZIP形式なので解凍するとCSVファイルが出てくる。
まずはこれをいきなり開いてみると何がおこるか?
私が過去にやらかしてしまった北海道の郵便番号の先頭0(ゼロ)が取れてしまっていることが分かる。

1-2 Excelで先頭の0が取れないように開く

まずは最初にExcelを立ち上げておく。その後、データタブのところにあるテキストまたはCSVからをクリック。
そして先ほどダウンロードして回答したKEN_ALL.CSVを開く。
そうするとあらビックリ 😥  まだ、先頭の0が取れてしまっているではないですか。
これをよく見るとどうやら先頭の200レコードをみて自動的にデータ型を決定しているようだ。Excelは先頭に0がついていても容赦なく数値型と解釈したのだろう。

1-3 EXCELにCSVファイルを取り込む時のパラメータに注意する

そこでデータ型の検出のオプションをデータ型を検出しないを選択して取り込む。
そうすると見事に先頭0がついた状態のサンプルが表示されているではないか。このまま下にある読み込みボタンをクリックする。
excel
自動でテーブルスタイルが適用され、1行目が見出し行に設定されて取り込まれてた。

2.CSVファイルを加工する時に使えるExcelのサポート機能を紹介

 2-1 CSVファイルを取込んだシートに触らない

CSVファイルを取り込みしているシートでは加工しないほうが良い。

加工していると誤ってセルを編集してEnterキーを押すと先頭の0が取れてしまう。
気づけが良いが、気づけなかった時は問題ないので触らないことだ。

それより本来は郵便番号や住所のようなセンシティブ情報を扱うとき
に誰でも編集できるExcelで管理しないようにすることだ。
いくら注意してもうっかりミスを防ぐのは難しい。データベースソフトを使うのが望ましい。

 

 2-2 別のシートに入力していると入力候補が表示される

という事で別のシートでCSVデータを抽出する式を埋め込んでみよう。
今回は郵便番号を入力すると都道府県、住所1、住所2をVlookup関数で表示するようにしてみる。
124,271件のレコードを検索する。以外なほどサックリと計算が終了10秒もかかっていない。Office365のパフォーマンスの良さを改めて感じた。

そして式を入力しているときに気づいたのだが、以下のような入力候補が表示されている。
まずはCSVのファイル名が入力候補として表示された。インポートしたのでシート名ではなくデータソースのCSVファイル名が候補として出てくるようだ。

続いて今度は列名が入力候補に表示されている。

 

見たところ郵便番号を取り込んだシートの列名が表示されているだけに見えたので、列名をわかりやすく変更して再度入力候補を出してみた。

 

 

変更したカラム名が候補として表示されるようになった。

ただし、Visual Studioのような操作性ではないのが残念。Enterキーを押すとエラーになってしまう。Tabキーかマウスでクリックすれば選択した候補が表示される。

 

 2-3 Accessのテーブルに郵便番号・住所データを取り込んでExcelから参照

Excelだとご入力で先頭0が取れてしまう可能性があるから、やっぱりデータの管理はAccessなどのデータベースソフトでやっておきたい。
多分大丈夫だろうが、Excelで誤入力したと想定して正しいデータがAccessから反映されるのか確認した。
以下のシートはCSVファイルをAccessに取り込んでAccessのテーブルからExcelに取り込んだ状態だ。それをあえて編集状態にして先頭0が取れた状態にしてみた。
excel
データタブの更新でAccessから再度データを取り直す。
Excel
想定通りではあったが、無事に先頭0が復活。やっぱりデータの管理はデータベースソフトで行ったほうが良いと改めて感じた。

ココがポイント

CSVファイルを読み込むときに注意するデータ型の誤検知でコードや区分値の先頭0が取れないように注意することを記載した。
根本的な問題として数値でコード定義しておけば何の問題もないのだが、何故数値なのに文字型でコード値を定義するシステムが多いことか。
先頭0は使わないようにコード設計してもらえればこんな苦労はしなくて済むのだが。

追記

excel
全ExcelユーザーはRelaxtools Addinをインストールせよ

2019/09/12 実際に使っている動画をページの最後に追加しました。 Excelを使う時に最高に使えるAddinがRelaxtoolsだ。実はこのツールを知る前には自分で同じようなAddinを作っ ...

続きを見る

上の記事で書いているRelaxtools使えばCSVの前0を落とさないでExcelに取り込む機能がある。ただ、クリップボード経由だからサイズが大きいやつは時間がかかる可能性がある。

excel

-Excel, Office365
-, ,

Copyright© ビジネス中学 , 2024 All Rights Reserved.