CSVをそのままExcelで開くと文字化けや0落ちを招く
取り込みの段で確定してしまうもの
CSVは値をカンマで区切っただけのテキストファイルで、「この列は文字列」という情報を持ちません。CSVには型の情報がないのでExcelは開くときに値の見た目から型を推測し、社員番号の00123は数値と判断され商品コードの1-2は日付と判断されます。
壊れているのは変換の処理ではなく、その前の取り込みの段です。 ダブルクリックで開いた時点で判定は終わっており、そのまま保存すると壊れた値のままxlsxになるので、元のCSVを下の手順でもう一度取り込む必要があります。
Excelが先頭の0を消して15桁超を指数表記へ変える理由
Microsoftは、数式や四則演算で計算できるように先頭のゼロを取り除いて数値へ変換するとしています。社員番号・電話番号・郵便番号のように、桁をそろえること自体に意味がある値がこれで崩れます。
桁数が多い番号も同じ理由で値が変わります。精度が15桁で切り捨てられて1.23E+15のような指数表記の数値として表示されるため、クレジットカード番号をCSVで受け取ると番号が指数表記に置き換わります。 0落ちしたセルは、取り込んだあとに書式を設定して修正することができます。
"Excel automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them."（導入節）／"Removing leading zeros from numerical text and converting to a number."／"Truncating numerical data to 15 digits of precision and converting to a number displayed in scientific notation."（自動データ変換の対象一覧より）— Microsoft サポートより公式情報を見る →
12/2が2-Decに変わるのは日付入力を助ける組み込み動作
Excelには日付を入力しやすくする動作があらかじめ組み込まれています。Microsoftは12/2が2-Decに変わる例を出しており、英数字が連続した値を日付へ変更する動作も自動データ変換の対象に含まれます。
ECの受注データや在庫データで次のような列が変わると、表示だけでなくセルに入っている値そのものが日付に置き換わります。次の3つの値はどれも日付として解釈される値です。
- 商品コードの1-2
- サイズ表記の3/4
- 型番の5-10
"Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec."（導入節）— Microsoft サポートより公式情報を見る →
CSVの文字化けは文字コードとBOMの組み合わせで左右される
Excelがデータを取り込むときに使うPower Queryは、文字コードを推測しません。例外はUTF-8で、ファイルの先頭にBOMがあるときだけUTF-8と判別します。BOMとは、どの文字コードで書かれたかを見分けるためにファイルの先頭へ置く数バイトの印です。
BOMの無いUTF-8のCSVはパソコン側で使っている文字コードで読まれるため、日本語環境で読むと氏名や住所が別の文字に置き換わります。BOMを付けずにUTF-8で書き出す仕組みから受け取ったCSVは、このように化けます。
化けるかどうかは文字コードとBOMの組み合わせによって変わります。つまり、自動で判別されるのはBOMの付いたUTF-8だけで、それ以外は「元のファイル」で選んだ文字コードで読まれます。
| CSVの文字コード | Power Queryでの判定結果 |
|---|---|
| UTF-8（BOMあり） | UTF-8と判別される |
| UTF-8（BOMなし） | 推論されない。「元のファイル」がデフォルト値のままならパソコン側の文字コードで読まれる |
| Shift_JISなどBOM付きUTF-8以外 | 自動では判別されない。「元のファイル」で選んだ文字コードで読まれる |
日本語のCSVはShift_JISとUTF-8のどちらでも書き出されるため、ファイルがどちらの文字コードで書かれているかは、開くまで判別できません。プレビューで氏名や住所が正しく表示されているか確認し、文字化けしていれば「元のファイル」の文字コードを切り替えるのが確実です。
文字コードが2種類ある理由を知らなくても、プレビューで見比べて正しい方を選ぶことができます。すでに化けたファイルも、Excelで開く前に文字コードを合わせれば直すことができます。
"Currently, the character set isn't inferred, and UTF-8 is only inferred if it starts with a UTF-8 BOM"（Text 節・File Origin の説明より）— Microsoft Learn より公式情報を見る →
日付化はオフにできず取り込み時の区切り記号で防ぐ
12/2のような値が日付に変わる動作はオフにできず、使えるのは値を入力するときの回避策で、セルを先に文字列書式にしておく・数値の前にスペースを置く・アポストロフィを置く・分数には0とスペースを前置する、の4通りです。どれも入力の前に手を打つ方法なので、すでにあるCSVをダブルクリックで開く場合には間に合いません。
設定で変更できる変換もあります。自動データ変換の設定は、先頭ゼロの除去・15桁での切り捨て・Eの前後の指数表記化・英数字が連続した値の日付化の4つが対象です。
ただしこの設定を持つのは4製品だけです。設定を持つ製品は次のとおりです。
| Excelのバージョン | 自動データ変換の設定 |
|---|---|
| Excel for Microsoft 365（Windows版・Mac版） | ある |
| Excel 2024（Windows版・Mac版） | ある |
| Excel 2021 / Excel 2019 / Excel 2016 | ない |
Excel 2021・Excel 2019・Excel 2016ではこの設定が画面に出てこないなど、使うことができる設定はバージョンによって異なります。どの環境でも通用する対処は、取り込みの段で列を文字列に指定しておくことです。 開いた後に列の書式を文字列へ変更しても、そのとき既に消えている先頭ゼロは戻りません。
"Unfortunately there is no way to turn this off. But there are ways to get around it."（導入節）／"Preformat the cells you want to enter numbers into as Text."／"A zero and a space before you enter a fraction such as 1/2 or 3/4 so that they don’t change to 2-Jan or 4-Mar, for example."（回避策の一覧より）— Microsoft サポートより公式情報を見る →
"This feature is available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac."（Set automatic data conversions 節の Important より）／"Converting a continuous string of letters and numbers to a date."（同節の対象一覧より）／"This will not restore leading zeros that were removed prior to formatting."（Use a custom format to keep the leading zeros 節の Note より）— Microsoft サポートより公式情報を見る →
CSVを文字列のまま取り込むPower Queryの使い方
壊さない取り込みの流れ
- データタブ →「テキストまたはCSVから」でファイルを選ぶ
- 「元のファイル」で文字コードを選ぶ
- データ型検出を「データ型を検出しない」にする
- 「読み込み」でシートへ取り込む
ダブルクリックで開くかわりに、Excelを先に起動して取り込み画面から読み込みます。この画面には文字コードとデータ型を選ぶ入力フィールドがあり、そこで指定した内容がそのまま結果になります。
「データの取得」ボタンの隣の「テキストまたはCSVから」を選ぶ
Excelのデータタブを開いて「データの取得」ボタンの隣にある「テキストまたはCSVから」を選びます。ファイルを選ぶとプレビュー画面が開き、読み込む前の状態を確認できます。
この入口を紹介しているMicrosoftのページは、Excel for Microsoft 365からExcel 2016までの7製品を対象にしています。Mac版も含まれます。
「データの取得」ボタンが表示されない場合の回り道も、同じ手順に添えられています。「新しいクエリ」→「ファイルから」→「テキストから」と進んでファイルを選び「インポート」を押す手順で、Microsoftはこの回り道をバージョンで区切っておらず、ボタンが画面に出ているかどうかだけで分けて書いています。
"Select the Data tab, then From Text/CSV next to the Get Data button."／"If you don’t see the Get Data button, go to New Query > From File > From Text and browse to your text file, then press Import."（Convert numbers to text when you import text data 節より）／"Applies To Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2019 Excel 2016"（ページ冒頭の Applies To より）— Microsoft サポートより公式情報を見る →
文字コードはプレビューの「元のファイル」で自分で選ぶ
プレビュー画面の左上にある「元のファイル」が文字コードを選ぶプルダウンです。ただし、BOMの無いUTF-8のファイルはここでUTF-8を選ばないと化けたままになるので、プレビューの表示が正しくなるまで切り替えてチェックします。
同じ画面で区切り記号も選ぶことができます。Power Queryで指定できる区切り記号は、コロン・カンマ・等号・セミコロン・スペース・タブ・任意の文字列・固定幅の8種類。タブ区切りやセミコロン区切りで受け取ったファイルは、区切り記号を合わせないと1列にまとまって表示されます。
"In addition to file origin, CSV also supports specifying the delimiter and how data type detection is handled."／"Delimiters include colon, comma, equals sign, semicolon, space, tab, a custom delimiter (which can be any string), and a fixed width (splitting up text by some standard number of characters)."（CSV 節より）— Microsoft Learn より公式情報を見る →
データ型の自動検出を切って全列を文字列のまま取り込む設定
プレビュー画面の右下にあるデータ型検出のプルダウンでは、先頭200行を基準にする・全データを基準にする・自動検出そのものをやめる、の3つから選ぶことができます。Power Queryの仕様によると、自動検出をオフにすれば全列が文字列として読み込まれるとのこと。
「データ型検出」のプルダウンを「データ型を検出しない」にしておけば、先頭の0は消えず、日付として解釈される値も文字列のまま取り込まれます。 一部の列だけ数値にしたい場合は、取り込んだあとにExcel側で列の書式を変更してください。
列ごとに指定したいときはプレビュー画面で「データの変換」を押して編集画面を開き、列を選んでからホームタブの「データ型」で「テキスト」を選びます。終わったら「閉じて読み込む」でシートへ戻します。
"You can base it on the first 200 rows or on the entire data set. You can also choose to turn off automatic data type detection and instead let all columns default to 'Text.'"（CSV 節より）／"From the Navigator, you can either transform the data in the Power Query editor by selecting Transform Data, or load the data by selecting Load."（Connect to local text/CSV file from Power Query Desktop 節より）— Microsoft Learn より公式情報を見る →
"If any columns need to be converted to text, select the column to convert by clicking on the column header, then go to Home > Transform > Data Type > select Text."／"When you're done, select Close & Load, and Excel will return the query data to your worksheet."（Convert numbers to text when you import text data 節より）— Microsoft サポートより公式情報を見る →
Mac版Excelのサポートページで案内されているメニューの順序
Mac版を対象にした手順ページでは、データタブから「データの取得と変換」→「データの取得」→「ファイルから」の順に進み、そこで「テキストまたはCSVから」を選びます。 ファイルを選んで「インポート」を押すと取り込み先を指定するダイアログが開き、押すメニューそのものは「テキストまたはCSVから」で変わりませんが、Mac版の手順ページではデータタブとの間にこの3つのメニューが挟まります。
次に紹介する従来のウィザードはMacでは使うことができないため、MacでCSVを取り込むならこの順に進みます。この手順ページの対象は次の4製品です。
- Excel for Microsoft 365 for Mac
- Excel 2024
- Excel 2024 for Mac
- Excel 2021 for Mac
"Go to Data > Get & Transform Data > Get Data > From File."／"Select the type of file that you want to import (such as From Excel Workbook or From Text/CSV"／"Select the file you want to import, and then select Import."（Here's how 節の手順1〜3より）— Microsoft サポートより公式情報を見る →
従来のテキストファイルウィザードでCSVを取り込む方法
2つの取り込み経路の違い
テキストファイルウィザードはMicrosoftが後方互換のために残しているレガシー機能で、使う前にオプションで有効にする操作が必要な場合があるとMicrosoftは書いています。列ごとのデータ形式を3画面目でまとめて指定する作りなので、列数の多いCSVでも文字列にする列を一度に指定できます。
従来のテキストファイルウィザードをオプションで表示
ファイルタブの「オプション」を開いて「データ」の「従来のデータ インポート ウィザードを表示する」で「テキストから（レガシ）」を選びます。有効にする手順はこれだけです。
設定したあとはデータタブの「データの取得と変換」→「データの取得」→「従来のウィザード」→「テキストから（レガシ）」から開きます。開いた「テキスト ファイルのインポート」ダイアログで対象のファイルをダブルクリックすると、ウィザードが立ち上がります。
"The Text Import Wizard is a legacy feature, which you might need to enable."／"Select File > Options > Data."／"Under Show legacy data import wizards, select From Text (Legacy)."／"Once enabled, go to the Data tab > Get & Transform Data > Get Data > Legacy Wizards > From Text (Legacy). Then, in the Import Text File dialog box, double-click the text file that you want to import, and the Text Import Wizard opens."（導入節の Note より）— Microsoft サポートより公式情報を見る →
ウィザードの1番目で文字コード、3番目で文字列を選ぶ
ウィザードは3画面あります。1画面目は「元のファイル」のプルダウンで文字コードを選ぶ画面です。読み込む側のパソコンと異なる文字コードで作られたCSVなら、その文字コードに合わせるようMicrosoftは推奨しています。
2画面目では区切り記号を選びます。カンマ・タブのように一覧に表示される記号のほかに、「その他」へチェックを入れて任意の1文字を打ち込めます。
3画面目の「列のデータ形式」が0落ちと日付化を無効にする場所で、数字だけの列をExcelの文字列書式にするには対象の列を指定してから「文字列」を選びます。桁をそろえたい列はここで「文字列」に指定しておきます。
"Select the character set that's used in the text file. In most cases, you can leave this setting at its default."／"If you know that the text file was created by using a different character set than the character set that you're using on your computer, change this setting to match that character set."（Step 1 of 3・File origin より）／"If the character you want isn't listed, select the Other check box, and then type the character in the box with the cursor."（Step 2 of 3・Delimiters より）／"To convert a column of all number characters to the Excel Text format, select Text"／"Excel imports the column as General if the conversion could yield unintended results."（Step 3 of 3・Column data format より）— Microsoft サポートより公式情報を見る →
このウィザードを使うことができるのはWindows版だけ
Mac版は対象に入っていません。 Microsoftがこのページの対象として書いているのは次の3製品です。
- Excel for Microsoft 365
- Excel 2024
- Excel 2021
Macで取り込むならPower Queryの経路を使います。Microsoftもこのウィザードを後方互換のために残しているレガシー機能とし、代わりにPower Queryを推奨しているとのこと。Power Queryの経路なら、WindowsとMacのどちらでも同じ手順を使うことができます。
"The Text Import Wizard is a legacy feature that continues to be supported for backward compatibility. As an alternative, you can import data from data sources (Power Query)"（導入節の Note より）— Microsoft サポートより公式情報を見る →
Excelを開かずブラウザでCSVをxlsxへ変換
取り込み画面を触らずに完了させる場合
取り込み画面の設定を毎回選ぶのが面倒な場合や、そもそもExcelを開かずにxlsxがほしい場合は、ブラウザで変換する方法があります。sakuttoAIのCSV→Excel変換を使うとCSVファイルをExcel(.xlsx)形式で受け取れ、列幅の自動調整はデフォルトで有効です。
sakuttoAIのツールは文字コードを5種類から自動判定
このツールはファイルの中身を見て文字コードを判定してから読み込みます。 Excelの取り込みとは異なりBOMの有無には左右されないので、BOMの無いUTF-8のファイルも文字コードを選び直さずに読み込みます。判定する文字コードは次の5種類です。
- UTF-8
- Shift_JIS
- EUC-JP
- JIS
- Unicode
全セルを文字列で書き出すので0落ちも日付化も発生しない
CSVを全セル文字列として読み取りその文字列のままxlsxへ書き出すため、数値への変換を挟まず先頭のゼロも1-2のような日付として解釈される値もそのまま保持されます。ヘッダー行はデフォルトで太字になります。
ファイルはブラウザの中だけで処理され、サーバーへ送信されません。顧客リストや受注データのように社外へ出せないCSVでも、お使いのPC上で変換が完了します。 変換する前に中身だけ確認しておきたいときも、開くアプリを選ぶと、CSVを壊さずに読むことができます。
取り込んだCSVをxlsx形式で保存して書式と数式を残す
保存し直して初めて保持されるもの
ダブルクリックで開いた場合のように、開いているファイルが .csvのままなら書式は保存されません。ファイルタブの「名前を付けて保存」を開き、ファイルの種類に「Excelブック（.xlsx）」を選びます。
保存時にファイルの種類で.xlsxを選ぶ
.xlsxはXMLをもとにしたファイル形式で、MicrosoftはExcel 2007とExcel 2010のデフォルトとしています。保存するときにExcelブック（.xlsx）を選び直せば、列幅・セルの書式・数式・複数シートも残すことができます。
xlsxに保存できないVBAのマクロコードとマクロシート
.xlsxには、VBAのマクロコードとExcel 4.0のマクロシートを保存できません。マクロを含めるなら .xlsmを選びます。CSVから取り込んだデータを集計するだけなら .xlsxで十分です。 Microsoftは、別の形式で保存すると書式・データ・機能の一部が引き継がれない場合があるとも指摘しています。
"The default XML-based file format for Excel 2010 and Excel 2007. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm)."（Excel file formats 表・Excel Workbook の行より）／"Whenever you save a file in another file format, some of its formatting, data, and features might not be transferred."（導入節の Note より）— Microsoft サポートより公式情報を見る →
保存する前に日本語・番号・コードの3つの列をチェックしておく
保存してしまうと壊れた値かどうかは見た目では判断しにくくなります。次の3点だけは保存の前に確認しておきます。
| チェックする場所 | 崩れていない状態 |
|---|---|
| 日本語の列 | 氏名・住所が文字化けせずに表示されている |
| 番号の列 | 先頭のゼロが残っていて指数表記にもなっていない |
| コードの列 | 1-2や3/4が日付に変わっていない |
取り込みの経路を選べば0落ちと文字化けは防げる
CSVをExcelに変換するとき、結果を左右しているのは変換の処理ではなく取り込みの経路です。 ダブルクリックで開けば文字コードもデータ型もExcelが判定した内容で確定し、そのまま保存すると壊れた値がxlsxに残ります。
WindowsでもMacでも共通して使うことができるのは、データタブの「テキストまたはCSVから」です。「元のファイル」で文字コードを選択し、データ型検出を「データ型を検出しない」にしてから読み込み、最後に.xlsxで保存します。従来のテキストファイルウィザードを使う場合はWindows版に限られ、オプションで表示させたうえで3画面目の列のデータ形式で「文字列」を選びます。
どちらの経路も手を打つのは開く前で、開いてから書式を変更しても消えた先頭ゼロは戻りません。保存する前には、日本語・番号・コードの3つの列をチェックしておくといいでしょう。
FAQ
- CSVをExcelに変換すると先頭の0が消えるのはなぜですか？
- Excelが値を数値として処理するためです。数式や四則演算で計算できるように、先頭のゼロを取り除いて数値へ変換する動作がもともと組み込まれています。桁数が多い番号も同じ理由で指数表記に変わります。取り込みのときに列を文字列として指定しておけば、この変換は発生しません。
- CSVをExcelで開くと日付になるのを設定で無効にできますか？
- 無効にできません。日付への自動変換をオフにする方法は無く、使えるのは値を入力するときの回避策だけです。セルを先に文字列書式にしておく方法や値の前にアポストロフィを置く方法がありますが、どれも値を入力する前に手を打つ対策なので、すでにあるCSVをダブルクリックで開く場合には間に合いません。取り込みの段で列を文字列に指定しておくのが、いちばん手戻りの少ない回避策になります。
- CSVをExcelに変換すると文字化けするのはなぜですか？
- 文字コードが自動では判別されないためです。Power Queryの仕様では、文字セットが推論されることはなく、UTF-8はファイルの先頭にBOMが含まれている場合にだけ判別されます。BOMの無いUTF-8のCSVは、パソコン側の文字コードで読まれて化けます。取り込み画面の「元のファイル」で文字コードを選べば直ります。
- 変換したxlsxにマクロは保存できますか？
- できません。.xlsxはVBAのマクロコードを保存できない形式です。マクロを含めて保存するなら.xlsm、マクロが不要なら.xlsxのままで問題ありません。CSVから取り込んだデータを集計するだけなら.xlsxで十分です。
関連する記事
ExcelなしでCSVを開くCSVビューアは、読み込める行数の上限で選ぶCSVを開くツールは見た目が似ていても、読み込める行数が異なります。sakuttoAIのCSVビューアは先頭10万行まで読み込み、画面にはそのうち500行を表示します。Excelの上限は104万8,576行、Googleスプレッドシートは1,000万セルで、CSVを取り込む場合も同じ上限です。
スプレッドシートの関数一覧 エクセルとの違いと独自の関数を検索Googleスプレッドシートの関数を、エクセルの関数と並べた一覧です。関数名や用途を入れて検索すると、その行だけを表示します。SUMIFやVLOOKUPなどエクセルと同じ名前の関数と、IMPORTRANGEやQUERYなど独自の関数、スプレッドシートにないエクセルの関数の代わりの書き方もまとめています。
エクセルのエラー一覧と#VALUE!や#N/Aの意味・原因・対処法エクセルのセルに出た#VALUE!や#REF!、#DIV/0!などの意味と原因は、記号ごとの早見表で調べることができます。検索ボックスに記号を入れると、その行だけが表示されます。修正が難しいエラーや見せたくないエラーは、IFERRORやIFNAで空白や0に置き換え、緑の三角はエラーチェックの設定で消すことができます。
スプレッドシートとエクセルの違い 料金・共同編集・オフライン・マクロスプレッドシートとエクセルの違いを、料金・共同編集・オフライン・データ量の上限・マクロ・互換性の6つで比較します。スプレッドシートは個人なら無料でブラウザから同時編集でき、エクセルは買い切りかMicrosoft 365で使い、オフラインや大量データ、VBAのマクロに強みがあります。関数の大半は共通です。