【Excel】Power QueryでCSVの区切り文字が自動判定されない時の読み込み設定

Power QueryはCSVファイルを読み込む際、先頭数行をスキャンして区切り文字を自動判定します。しかし、ファイルによっては期待どおりに判定されず、データが正しく分割されないことがあります。この記事では、区切り文字が自動判定されない原因と、手動で設定する方法を具体的に解説します。また、組織のポリシーで変更できない設定がある場合の確認ポイントや、失敗しやすいパターンについても説明します。

Power QueryがCSVの区切り文字を自動判定する仕組み

Power QueryはCSVファイルを読み込むとき、最初の200行をサンプリングして区切り文字を推測します。具体的には、各行に含まれるカンマ、タブ、セミコロン、パイプなどの出現頻度を調べ、最も一貫性のある文字を区切り文字とみなします。ただし、この自動判定はファイルの先頭にメタデータ(BOMなど)があるか、引用符で囲まれたデータに区切り文字が含まれている場合に誤判定することがあります。また、Power QueryのバージョンやExcelの設定によって、既定の区切り文字の優先順位が異なるため、環境によって動作が変わることがあります。

区切り文字が自動判定されない主な原因

自動判定が失敗する原因は、ファイル自体の問題とPower Queryの設定の問題に大別できます。以下に代表的な原因を挙げます。

ファイル側の問題

• 区切り文字が統一されていない: 一部の行が異なる区切り文字を使っている場合、判定がブレます。
• 引用符の扱いが特殊: ダブルクォーテーションで囲まれていないフィールド内にカンマが含まれていると、誤って区切りと認識されます。
• エンコードとBOM: UTF-8 with BOMのファイルは最初の3バイトがBOMとして認識され、区切り文字の判定に影響を与えることがあります。Shift-JISやEUC-JPなどマルチバイト文字を含むファイルでは、文字列の途中に区切り文字と同じバイト値が現れる可能性があります。
• 改行コード: CRLFとLFが混在していると、行の区切りが正しく認識されず、区切り文字のカウントがずれます。

Power Query側の問題

• 自動検出の設定がオフ: 読み込み時に「元のファイル」ダイアログで「データ型の検出」が「常に」以外になっていると、区切り文字の自動判定が働かないことがあります。
• ロケール設定: Excelの言語設定や地域設定によって、既定の区切り文字(例:ドイツ語ではセミコロンが優先)が変わります。
• 古いバージョンのPower Query: 2016以前のExcelにバンドルされているPower Queryでは、自動判定のアルゴリズムが簡素な場合があります。

読み込み設定を手動で変更する手順

自動判定がうまくいかない場合は、Power Queryエディター内で区切り文字を明示的に指定できます。以下の手順で設定を変更してください。

1. Excelで「データ」タブを開き、「データの取得と変換」グループから「ファイルから」→「テキスト/CSVから」を選択します。
2. 目的のCSVファイルを選択し、「インポート」をクリックします。「元のファイル」ダイアログが表示されます。
3. ダイアログ下部にある「データの読み込み」の代わりに「変換」ボタンをクリックして、Power Queryエディターを開きます。
4. エディターの左側にある「クエリの設定」ペインで、適用されたステップの「ソース」をクリックします。数式バーに = Csv.Document(...) のような式が表示されます。
5. 数式バーのCsv.Document関数のパラメーターを編集します。第2引数に区切り文字を指定します。例えば、カンマ区切りの場合は","、タブ区切りの場合は"#(tab)"を追加します。変更後はEnterキーを押して確定します。
6. 必要に応じて、エンコードや引用符の設定も変更します。Csv.Document関数の第3引数でエンコード(例:TextEncoding.Utf8)、第4引数で引用符のスタイル(例:QuoteStyle.Csv)を指定できます。
7. 「閉じて読み込む」または「閉じて次に読み込む」でデータをワークシートに出力します。

なお、上記の方法はあくまで手動設定です。自動判定を復活させたい場合は、ソースステップを削除して最初から読み込み直すことも可能です。

状況別の比較表:区切り文字設定のポイント

区切り文字	Power Queryでの指定例	注意点	よく使われる拡張子
カンマ(,)	"",","	日本語のCSVではカンマ区切りが一般的ですが、数値に桁区切りのカンマが含まれると誤認識されやすいです。	.csv
タブ(\t)	"","#(tab)"	タブは見た目で区別しにくいため、エディターで確認する必要があります。引用符で囲まれていると自動判定が失敗しやすいです。	.tsv, .tab
セミコロン(;)	"",";"	ヨーロッパ地域のCSVでよく使われます。ロケール設定により自動判定の優先度が変わります。	.csv
パイプ(|)	"","|"	データにパイプ記号が含まれることは少ないため、比較的安全です。ただし、ファイルサイズが大きいと自動判定の速度が低下することがあります。	.psv

失敗しやすいパターンと対処法

実際の業務で遭遇しやすい失敗例をいくつか紹介します。

パターン1:1列にすべてのデータがまとまってしまう

これは区切り文字が全く認識されていない状態です。Power Queryがファイルを1つの大きなテキストとして読み込んでいます。原因としては、ファイルの拡張子が.csvではない(例:.txt)場合や、区切り文字が極めて特殊な文字(例えば制御文字)である場合が考えられます。対処法は、上記の手順で明示的に区切り文字を指定することです。

パターン2:列が途中でずれる、または追加の空列ができる

一部の行だけフィールド数が異なる場合に発生します。例えば、データ内に改行が含まれていると、1行とカウントされずに列がずれることがあります。この場合は、引用符の設定を確認し、Csv.Document関数の第4引数でQuoteStyle.Csv(標準)を指定してください。それでも解決しない場合は、前処理として改行を削除するステップを追加します。

パターン3:日本語などのマルチバイト文字が文字化けする

エンコードの自動検出が失敗している可能性があります。Power QueryはUTF-8を優先しますが、Shift-JISで保存されたCSVファイルでは文字化けが発生します。対処法は、ソースステップでTextEncoding.ShiftJISを明示的に指定することです。また、ファイルにBOMがある場合は、TextEncoding.Utf8に加えてByteOrderMark.Trueを試すことも有効です。

管理者に確認すべき設定

会社のPCでは、グループポリシーやセキュリティソフトによってPower Queryの動作が制限されている場合があります。以下の点を管理者に確認してください。

• データ接続の許可: 特定のファイル形式(CSV)の読み込みがポリシーで禁止されている可能性があります。
• Power Queryアドインのバージョン: 古いバージョンでは自動判定の精度が低く、手動設定が必須になることがあります。更新を依頼してください。
• ロケール設定の固定: 地域設定がシステム管理者によって固定されている場合、区切り文字の既定値が変更できません。回避策としてPower Queryエディター内で強制的に指定する方法を相談してください。

よくある質問(FAQ)

Q1. 自動判定を完全にオフにする方法はありますか?

Power Queryの「元のファイル」ダイアログで「データ型の検出」を「常に」以外に設定しても、区切り文字の自動判定は完全にはオフになりません。完全に手動で制御するには、上記の手順でCsv.Document関数のパラメーターを直接編集する必要があります。

Q2. 読み込み後に区切り文字を変更するにはどうすればいいですか?

既に読み込まれたクエリのソースステップを修正します。「クエリの編集」からPower Queryエディターを開き、適用されたステップの「ソース」の数式を編集してください。変更後、自動的にデータが再読み込みされます。

Q3. 区切り文字が「自動」と表示されるが、正しく認識されないのはなぜですか?

「自動」表示はPower Queryが判定を試みた結果を示しています。判定が失敗している原因としては、ファイルの先頭行が空行である、またはデータ内に引用符が不適切に使われていることが考えられます。まずファイルをテキストエディターで開いて構造を確認してください。

Q4. 100MB以上の大きなCSVファイルを扱う場合、注意点はありますか?

大きなファイルでは、自動判定のために先頭200行をスキャンする時間が無視できない場合があります。また、メモリ不足が発生する可能性があるため、Power Queryの「データのプレビュー」を制限する設定を検討してください。手動で区切り文字を指定しておくことで、スキャン時間を短縮できます。

まとめ

Power QueryのCSV読み込みで区切り文字が自動判定されない場合、ファイルの構造やエンコード、Power Queryの設定が原因であることが多いです。まずは「元のファイル」ダイアログでプレビューを確認し、必要に応じてCsv.Document関数で明示的に指定します。組織のポリシーで設定が制限されている場合は、管理者に相談して適切な対応を取ってください。これらの手法を押さえておけば、区切り文字の問題で作業が停滞することはなくなります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】