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

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

sample1.csv

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

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

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

id,data1,data2,data3,data4,data5
1,12345678901,123456789012,123456789012345,1234567890123456,12345678901234567

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

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

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

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

「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

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

ここまでのパターン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

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

sample6.csv

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

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

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

id,data1,data2,data3
1,=1+2,=1-2,=foo

sample7.csv

パターン8 Boolean (true or false)

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

id,data1,data2
1,true,false

sample8.csv

パターン9 括弧の数字

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

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

id,data1,data2,data3,data4
1,(1),(2),(0),(-1)

sample9.csv

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

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

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

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

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

まとめ

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

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

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

関連した記事

3分でわかるExcel VBAでIEを利用したHTMLのチェック方法 JavaScriptで文字列の有無を調べるにはindexOfではなくtestを使う Google Apps ScriptのsetBorderで枠線をリセットする方法 GoogleスプレッドシートやExcelのIFS関数は改行できる