CSVファイルをExcelで開くと自動変換される9パターンの値

CSVファイルをExcelで開くと自動変換される

CSVファイルをExcelで直接開くと、一部のデータが自動的に変換されてしまい、意図しない値に置き換わることがあります。

これによりデータの正確性が損なわれ、データ編集やデータ分析に支障をきたす場合があります。

この記事では、ExcelでCSVを開いたときに自動的に変換してしまう代表的な9パターンの値を紹介します。

自動変換はダブルクォーテーションでは防げない

たまにCSVファイルの値を "001" のようにダブルクォーテーションで囲むとExcelで開いた際に自動変換されないと誤解されている方がいますが、値をダブルクォーテーションで囲んでもExcelで直接開いた場合は自動変換されます。

また、ChatGPTなどの生成AIでExcelでCSVファイルの取り扱いなどについての質問をすると、"001" のようにダブルクォーテーションで囲むと自動変換されないという誤った回答をすることがあります。

最新のExcelは変換の警告が表示される

最新のExcelであればCSVファイルを開いた際にデータ変換が発生する場合は「既定では、Excelはこのファイルで次のデータ変換を実行します:」という警告が表示されます。

データ変換を制御する機能は2023年9月28日にリリースされたExcelのバージョン 2309から使用可能です。

この警告が表示されたときに「変換しない」を押すと自動変換されないのですが、変換されないのは以下の4パターンのみで、それ以外のデータは「変換しない」を押しても自動変換されるので注意が必要です。

  • 大きな数値を科学的記数法に変換する
  • 文字 "E" を囲む数字を指数表記に変換する
  • 先頭のゼロを削除する
  • 連続する文字と数字を日付に変換する

自動データ変換の警告が表示されない場合

CSVファイルを開いたときに警告が表示されない場合は、使用しているExcelのバージョンが古いか、「環境設定 → 編集」の自動データ変換の「CSVファイルまたは同様のファイルを読み込むときに、自動データ変換を通知する」がオフになっています。

ちなみに自動データ変換の警告が表示されたときに「CSVまたは類似のファイルでの既定の変換について通知しません。」をチェックして有効にすると、自動データ変換の通知がオフになり、警告が表示されなくなります。

以降でCSVファイルをExcelで直接開いたときに自動変換されるパターンを説明します。

パターン1 数字の先頭がゼロ

「001」や「002」のように数字の先頭にゼロが付いた値は「1」や「2」に自動変換されます。

id,data1
1,001
2,002
パターン1 数字の先頭がゼロ

「環境設定 → 編集」の自動データ変換の「先頭のゼロを削除して数値に変換する」がオフになっている場合は自動変換されません。

sample1.csv

パターン2 16桁以上の整数

16桁以上の整数は16桁以降を「0」に自動変換します。

例えば「1234567890123456」は「1234567890123450」に自動変換されます。

id,data1,data2,data3,data4,data5
1,12345678901,123456789012,123456789012345,1234567890123456,12345678901234567
パターン2 16桁以上の整数

「環境設定 → 編集」の自動データ変換の「ロング数値の最初の15桁を保持し、科学的記数法に変換する」がオフになっている場合は自動変換されません。

ちなみにセルの幅が小さかったり、桁数が多い場合は最初に開いたときに「1.2346E+10」のように表示されますが、セルの書式設定を数値にして、セルの幅を広げれば「12345678901」のように表示されます。

Excelではセルの幅が小さかったり、桁数が多い場合は最初に開いたときに「1.2346E+10」のように表示される。

sample2.csv

パターン3 文字 "E" を囲む数字(科学的記数法)

Excelでは以下のような文字 "E" を囲む数字を科学的記数法と解釈するため、例えば「123E4」は「1.23E+06」に自動変換されます。

id,data1,data2,data3,data4
1,123E4,1.23E+5,2.5E-3,7.89e+2
パターン3 文字 "E" を囲む数字(科学的記数法)

科学的記数法は非常に大きな数や非常に小さな数を短く表記するための記法です。

「123E4」の場合は「123 × 10⁴ = 123 × 10000 = 1,230,000」となるため、Excelではセルの表示上は「1.23E+06」として扱われるため、前述のような結果になります。

また、「1.23E+5」のような表記の場合は「1.23E+05」のようにExcelでは0が追加されます。

「環境設定 → 編集」の自動データ変換の「文字 "E" を囲む数字を科学的記数法に基づく数値に変換する」がオフになっている場合は自動変換されません。

sample3.csv

パターン4 英語の月と数字の組み合わせ

「Jan1」や「MAR2023」のような英語の月と数字の組み合わせは自動変換されます。

英語の月は略称も対象で、大文字と小文字は区別しません。

id,data1,data2,data3
1,Jan1,February2,MAR2023
パターン4 英語の月と数字の組み合わせ
日本語 英語 略称
1月JanuaryJan
2月FebruaryFeb
3月MarchMar
4月AprilApr
5月MayMay
6月JuneJun
7月JulyJul
8月AugustAug
9月SeptemberSep
10月OctoberOct
11月NovemberNov
12月DecemberDec

「環境設定 → 編集」の自動データ変換の「連続する文字と数字を日付に変換する」がオフになっている場合は自動変換されません。

ここまでのパターン1からパターン4は自動データ変換の「Excelにテキストを入力、貼り付け、または読み込むときに、以下のすべての既定のデータ変換を有効にする」のチェックをオフにすれば自動変換されません。

しかし、パターン5以降はExcelでCSVファイルを直接開いた場合は自動データ変換を回避できないので注意が必要です。

sample4.csv

パターン5 スラッシュと数字の日付形式の組み合わせ

スラッシュと数字の組み合わせは日付形式であれば日付と判定されるため、例えば「1/2」は「1月2日」に自動変換されます。

「1/2/3」は日付形式には見えないですが、「2001/2/3」に変換されます。

id,data1,data2,data3
1,1/2,1/2/3,2025/1
パターン5 スラッシュと数字の組み合わせ

sample5.csv

パターン6 ハイフンと数字の日付形式の組み合わせ

ハイフンと数字の組み合わせも日付形式であれば日付と判定されるため、例えば「1-2」は「1月2日」に自動変換されます。

「1-2-3」も日付形式には見えないですが、「2001/2/3」に変換されます。

id,data1,data2,data3
1,1-2,1-2-3,2025-1
パターン6 ハイフンと数字の日付形式の組み合わせ

sample6.csv

パターン7 先頭に「=」がある式

先頭に「=」があると「式」だと判断されるため、「=1+2」は「3」、「=1-2」は「-1」に自動変換されます。

「=foo」のように式が成立しない場合は、「#NAME?」になります。

id,data1,data2,data3
1,=1+2,=1-2,=foo
パターン7 先頭に「=」があると式

sample7.csv

パターン8 Boolean (true or false)

Boolean型のtrueまたはfalseはExcelだと大文字で表示される仕様のため、true,falseはTRUEおよびFALSEに自動変換されます。

id,data1,data2
1,true,false
パターン8 Boolean (true or false)

sample8.csv

パターン9 括弧の数字

Excelでは (1) や (2) は -1 や -2 に自動変換されます。

これは会計や財務の世界では負の数値を括弧で表すことがあるため、括弧の数字は負の数値に変換する仕様になっているためです。ちなみに (-1) のように括弧内がマイナスの場合は変換されません。

id,data1,data2,data3,data4
1,(1),(2),(0),(-1)
パターン9 括弧の数字は負の数値になる

sample9.csv

ExcelでCSVファイルを自動変換させずに開く方法

ExcelでCSVファイルを自動変換させずに開くには「データ → データファイル指定 (Power Query)」を選択して、CSVファイルをインポートしてください。

インポート後に文字化けする場合は、元のファイルが「UTF-8」ではないので、「Shift JIS (シフトJIS)」などに変更してください。

Excel 「データ → データファイル指定 (Power Query)」

Power Query エディターで適用されたステップが「ソース」と「昇格されたヘッダー数」になっていれば、変換されずに表示されます。

Power Query エディター

左上の「閉じて読み込む」を押すとシートに反映されます。

まとめ

Excelで直接CSVファイルを開いたときに自動変換されるパターンは9パターンあります。

そのうちの4パターンは最新バージョンのExcelであれば、CSVファイルを開いたときに警告が表示されますが、それ以外のパターンは警告表示なしで自動変換されてしまいます。

パターン 自動変換の警告表示
数字の先頭がゼロ✅️
16桁以上の整数✅️
文字 "E" を囲む数字(科学的記数法)✅️
英語の月と数字の組み合わせ✅️
スラッシュと数字の日付形式の組み合わせ❌️
ハイフンと数字の日付形式の組み合わせ❌️
先頭に「=」がある式❌️
Boolean (true or false)❌️
括弧の数字❌️


CSVファイルをExcelで開く際、自動変換に気づかずデータが意図しない形式に変わってしまうことがよくあるため、事前に対策しておくことが大切です。