結論:CSVファイルの拡張子は.txtにするといい
以上。以下、その背景を解説する。
また、操作方法はMicrosoft 365 for Bussinessのエクセル(2022-11-05時点)を前提としている。
CSVファイルの3パターンのエンコード
csvファイルを企業間でやり取りするケースは多い。その時に問題になるのが、エンコード。
.csvファイルでありえるエンコードは以下の3種類。
- SJIS
- BOMありUTF8
- BOMなしUTF8
以下良し悪し
エンコード | 良し | 悪し |
---|---|---|
SJIS | 20年前のシステムとの互換性。 ダブルクリックでエクセルで開ける | サーバサイドで扱いにくい。自動化には向かない |
BOMありUTF8 | サーバーサイドで扱えるし、BOMありならエクセルのダブルクリックで開ける | BOMなし前提で取り込むシステムが大混乱 |
BOMなしUTF8 | UTF8と言えば普通はこっち | .csvファイルのダブルクリックでエクセルが化ける |
普通はUTF8と言えばBOMなし
BOMとはバイトオーダーマークのことで、バイト順序気にしないと行けないのはUTF16の方。そもそもUTF8はバイトオーダーなど教えてもらわなくてもいい。
さらにBOMありUTF8のファイルを結合すると途中にゴミが入る。
例えば以下のようなBOMありcsvがあったとする。
(less -U ファイル名
でBOMを表示できる。まずこのオプションを探す段階で苦労する)
<U+FEFF>id,sei,mei
1,gouda,takeshi
2,minamoto,shizuka
bomなし前提でのシステムがこのCSVを読むと、idカラムの指定は <U+FEFF>id
としないと行けない。そんなの無理。
とはいえ、.csv拡張子のファイルはうっかりダブルクリックしちゃう。エンジニアなら糸へんだらけの文字化けをみて、あぁUTF8かと思うけど、非エンジニアは引く。そしてエンジニアは非エンジニアを引かせちゃいけない。システム設計の最善手は驚き最小限の法則だ。
じゃぁやっぱりBOMつきUTF8の.csvなら文字化けセずに開けるんだから驚かせずに済んでいいじゃないという判断もありえる。そういう判断をするシーンはとても多い。
そもそも.csvをダブルクリックしてはいけない
.csvをダブルクリックすることで、エクセルは「エンコードの自動判定」「型の自動判定」を行う。BOMつきUTF8にすることでエンコードの自動判定はクリアされるが、型の自動判定でやっぱり事故が起こる。例えば以下のようにデータが壊れる
- 電話番号の
09012345678
が数値扱いになって9012345678
と前ゼロが欠落する - 長めのID
1588774516955942912
のような文字列が数値扱いになって1.58877E+18
という指数表記になる。一度指数表記になったら有効数字が減って元データも1588774516955940000
と変わってしまう -
11-12
のようなIDが日付扱いになって11月12日
と表記される。実データも2022/11/12
とデータが変わる
化ける化けないどころか情報を失うのが問題。しかもデータが空になるわけではない。別物がこっそり入る。ウソが入る。消えたことは分かるがすり替わったことは見つけにくい。「エクセルあるある」など悠長なことを言ってる場合ではない。
これらはPowerQueryなら型の変換画面が一拍挟まるので、自動変換させずに取り込むことができる。
解決策は「必ずPowerQueryを起動させて型をしてして取り込む」しかない
データ→データの取得→ファイルから→テキストまたはCSVから
という導線で取り込むしかない。ダブルクリックで開けるのから比べると格段に不便。不便だがしかたない。
文字が化ける化けないが問題じゃない。データを失うのが問題なのだ。
だったら最初から、.csvなんて要らない。.txtにしてしまえばダブルクリックで開くのはWindowsならメモ帳だ。そして今のメモ帳はBOMなしUTF8でも見れる。データとして正しい。表形式で確認したければ、PowerQuery経由でエクセルみれる。
.csvを.txtにすることで、驚かせることもなければ、データを欠落するリスクも抑えれる。
PowerQueryのショートカット
とはいえPowerQueryを起動する導線は遠い。当然ショートカットボタンもある。
わかりにくいが「テキストまたはCSVから」のボタンがむき出しにはなってる。
さらに、一度PowerQueryで取得したなら履歴から同じPowerQueryの設定データを取り込むこともできる。
ファイル名が違うと当然PowerQueryでカラムの型設定からやり直す必要がでるが、逆に同じ設定で取り込みたいなら都度ファイル名を揃えるというのもデータフローとしては健全かも知れない。