CSVをダブルクリックすると、Excelが「気を利かせて」くれます。郵便番号 01234 は 1234 になります。注文番号 4012888888881881 は 4.01289E+15 になり、最後の桁は0に置き換わります。商品コード 3-5 は5月3日になります。こうした変更はどれもファイルの中にはありません。Excelが開くときに行ったもので、保存するとそのまま確定してしまいます。
対処法は、毎回開き方を変えて運に任せることではありません。Excelに推測させないようにすれば済み、1分ほどで終わります。
結論
- CSVをダブルクリックしない。 データ → テキストまたはCSVから を使い、壊れやすい列を読み込む前に テキスト に設定します。
- Microsoft 365では、 ファイル → オプション → データ → データの自動変換 で、不要な変換をオフにします。
- ダブルクリックで開いたあとは、元のファイルに上書き保存しない。 被害はその時点でファイルに書き込まれます。
- Googleスプレッドシート: ファイル → インポート で、テキストを数値、日付、数式に変換する のチェックを外します。
Excelが何を変えるのか、その理由
CSVには型がなく、すべての値が文字列です。Excelは数値や日付を扱いたいので、列ごと、セルごとに推測します。困る推測は次のとおりです。
| 元の値 | Excelの表示 | 何が起きたか |
|---|---|---|
01234 | 1234 | 「数値」の先頭の0が削除された |
4012888888881881 | 4.01289E+15 | 指数表記になり、15桁目より後ろは 0 になる |
3-5、1/2 | 5-Mar、2-Jan | 日付として読み込まれた |
MARCH1、SEPT2 | 1-Mar、2-Sep | 日付として読み込まれた(遺伝子名で有名な問題) |
1E5 | 100000 | 指数表記として読み込まれた |
=1+1 | 2 | 数式として読み込まれた |
TRUE | TRUE(論理値として) | たいていは無害。名前だった場合を除く |
このうち2つは見た目だけでなく、元に戻せない破壊 です。16桁の数値が指数表記になると、最後の桁が失われます。Excelは数値を有効桁数15桁で保存するためです。また、日付として読み込まれた値は日付のシリアル値として保存されます。書式を文字列に戻しても、3-5 ではなく 46086 のようなシリアル値が表示されます。
重要なのは保存の瞬間です。 CSVを開いて変わるのは表示だけです。CSVとして保存すると、表示されている内容がファイルに書き戻され、元の値は失われます。
対処法1: 開かずにインポートする
2016以降のExcelデスクトップ版ならどれでも使えます。
- 空のブックを開きます。
- データ → テキストまたはCSVから で、ファイルを選びます。
- プレビューで データの変換(「読み込み」ではありません)をクリックします。
- Power Queryで、壊れやすい列(ID、郵便番号、電話番号、商品コード、口座番号など)をそれぞれ選びます。続いて 変換 → データ型 → テキスト を選びます。確認が出たら 現在のものを置換 を選びます。
- 閉じて読み込む をクリックします。
値は書かれたとおりに読み込まれます。本当に数値の列では計算もできます。推測をオフにしたのは、困る列だけだからです。
従来のウィザードを使いたい場合は、ファイル → オプション → データ → 従来のデータ インポート ウィザードの表示 → テキストから(レガシ) を選びます。すると データ → データの取得 → レガシ ウィザード に表示され、ウィザードの手順3で列を「文字列」に指定できます。
対処法2: 自動変換をオフにする
最近のMicrosoft 365版Excelには、まさにこのための設定があります。ファイル → オプション → データ → データの自動変換 にあります。
- 先頭のゼロを削除して数値に変換する
- 長い数値の先頭15桁を保持し、指数表記で表示する
- 文字「E」を囲む数字を指数表記の数値に変換する
- 連続する英数字を日付に変換する
困るものだけチェックを外してください。CSVを開くときにExcelが何かを変換したら通知する、というオプションもあります。これはオンのままにしておく価値があります。気づかない変更が、目に見える変更に変わるからです。
この設定はダブルクリックで開く場合にも効くので、いちばん手軽な方法です。ただし、持ち運びにはいちばん向きません。ファイルを送った相手には、その人自身の設定があるからです。
対処法3: Googleスプレッドシートと Numbers
Googleスプレッドシート も、初期設定では変換します。ファイル → インポート → アップロード を使い、インポートのダイアログで テキストを数値、日付、数式に変換する のチェックを外してください。すべてが書かれたとおりの文字列として読み込まれます。
MacとiPhoneの Numbers も、先頭の0を削除し、日付らしい値を日付として読み込みます。列を選んで書式を テキスト にすると、新しく入力する値には効きますが、開いた時点の変換はすでに済んでいます。データが大事な場合は、元のファイルと既知の値をいくつか突き合わせてください。
賢そうに見えて役に立たない方法
- CSV内の値を
="01234"の形で書く。 Excelでは01234と表示されます。しかし、そのファイルを読むほかのツールからは数式に見えますし、書き出し先の次のシステムでも同じです。 - アポストロフィを付けて
'01234とする。 同じ問題です。Excel以外のあらゆる場所で、アポストロフィがデータの一部になります。 - 開いたあとで列の書式をテキストにする。 手遅れです。0はすでに消えていて、書式を変えても戻りません。
スマートフォンの場合
スマートフォンでも同じ問題が起き、しかも設定項目は少なめです。iPhoneのNumbers、SheetsやExcelのアプリは、どれも開くときに変換します。そこから保存し直したCSVには、変更がそのまま残ります。
すべてのセルを文字列として扱うCSVアプリなら、変換しないのでこの問題は起きません。Smart CSV Viewerでは、列に数値、通貨、日付が入っていると指定して、正しく並べ替えやフィルタをかけることもできます。ただし、型が変えるのは列の表示だけです。ファイル内の文字列はそのままです。
列を数値、通貨、日付として表示しても、中身は書き換えません。先頭の0、長いID、コードがファイルのとおりに残ります。
できること身につけておきたい習慣
CSVを表計算ソフトで開いて保存し直す前に、壊れやすい3つの値を確認してください。先頭に0があるID、ファイル内でいちばん長い数値、日付に見えるコードです。この3つが無事なら、残りもほぼ間違いなく無事です。CSVとXLSXの比較ガイドでは、Excelに限らずどの表計算ソフトでもこれが起きる理由を説明しています。
