【Power Query】CSVの文字コードを置換文字で判定する

CSVを取り込むと、文字化けすることがあります。ファイルがShift_JISなのかUTF-8なのか、分からないときです。

以前の記事「【Power Query】CSVの文字化けをEncoding指定で防ぐ方法」では、先頭のBOMの有無で判定する方法を紹介しました。ただし、BOMのないUTF-8を、Shift_JISと誤判定する限界がありました。

今回は、この限界を置換文字（�）で補えるかを試しました。結論を先に書くと、補えたのは一部です。Shift_JIS側では置換文字が出ず、想定していた「両方を数えて比べる」方法は成り立ちませんでした。

想定読者
結論
置換文字と、最初に考えた方法
Shift_JIS側は数えられなかった
判定のコード
5種類のファイルで試した結果
よくある失敗
応用例
まとめ

想定読者

取込元ごとに文字コードが違うCSVを、1つのクエリで取り込みたい人
BOMなしのUTF-8のCSVが混ざることがある人
文字コードの自動判定で、何が判定できて何ができないかを知りたい人

結論

UTF-8として読めないバイトは、置換文字（U+FFFD）になる
Shift_JIS（932）で読むと、置換文字は出ない
判定に使えるのは「UTF-8で読んで、置換文字が出るか」だけ
英数字だけのCSVと、半角カナのShift_JISは、この方法では区別できない

検証環境は、Excel 16.0（Windows、日本語環境、コードページ932）です。他のバージョン・Mac版は未検証です。

置換文字と、最初に考えた方法

バイト列を、ある文字コードとして読めなかったとき、その位置は「�」（U+FFFD）に置き換わります。これが置換文字です。

最初は、次の方法を考えました。

UTF-8とShift_JISの両方で読む
置換文字の数を数える
少ない方を採る

Shift_JIS側は数えられなかった

実際に試すと、Shift_JIS（932）で読んだときの置換文字は、どのファイルでも0個でした。UTF-8で作ったCSVを932で読んでも、0個です（3種類のファイルで確認）。

そこで、不正なバイトを含む短いバイト列を、両方の文字コードで読み比べました。

バイト列（10進）	UTF-8（65001）で読む	Shift_JIS（932）で読む
65 255 66	A、置換文字、B	A、私用領域の文字（U+F8F3）、B
65 129 32 66	A、置換文字、スペース、B	A、中黒（・）、B
65 129（末尾）	A、置換文字	A、中黒（・）

Shift_JISで読むと、不正なバイトは置換文字ではなく、中黒（U+30FB）や私用領域の文字に変わりました。2行目では、後ろのスペースまで1文字に吸収されています。ほかに、128は制御文字（U+0080）、160・253・255は私用領域の文字になりました。13パターンで確認しています。

それなら、932で読んだときの中黒を数えればよいかもしれません。そこで、5種類のファイルを932で読み、中黒の数を数えました。

正しいShift_JISのファイル：1個（もともと中黒を1つ含むため）
BOMありのUTF-8：4個、BOMなしのUTF-8：3個
英数字だけ、半角カナのShift_JIS：0個

中黒は、正しい文字としても現れます。件数だけでは、中黒を含む正しいShift_JISと区別できません。半角カナのShift_JISは0個で、この方法でも判定できませんでした。サンプルは5ファイルだけで、境目になる件数は決められません。判定には使わないことにしました。

判定のコード

使えるのは、UTF-8側の置換文字だけです。UTF-8として読めたらUTF-8、読めなければShift_JISとみなします。

let
    Count = (t as text, ch as text) as number =>
        Text.Length(t) - Text.Length(Text.Replace(t, ch, "")),
    Detect = (buf as binary) as record =>
        let
            t8 = Text.FromBinary(buf, 65001),
            bad = Count(t8, "#(fffd)"),
            kind =
                if bad > 0 then "NotUtf8"
                else if Text.Length(t8) = Binary.Length(buf) then "AsciiOnly"
                else "ValidUtf8"
        in
            [Utf8Bad = bad, Kind = kind, Encoding = if bad > 0 then 932 else 65001],
    Buf = Binary.Buffer(File.Contents("C:\data\sample.csv")),
    Info = Detect(Buf),
    Result = Csv.Document(Buf, [Delimiter = ",", Encoding = Info[Encoding], QuoteStyle = QuoteStyle.None])
in
    Result

Kindは、判定の確かさを示す目印です。NotUtf8はShift_JISと判断できます。ValidUtf8は、UTF-8として読めたという意味で、Shift_JISでないとは言い切れません。

関数の仕様は、Microsoft LearnのText.FromBinaryで確認できます。

5種類のファイルで試した結果

同じ内容のCSVを、5種類作って判定しました。ReadOkは、判定した文字コードで読んだとき、見出しが正しく読めたかどうかです。

5種類のCSVに判定関数を適用した結果の表。Shift_JISだけNotUtf8で932、BOMありとなしのUTF-8は65001、半角カナのShift_JISは65001と判定されReadOkがFALSE — 再現用の最小コードの実行結果

ファイル	BOMだけで判定	置換文字（UTF-8側）で判定
Shift_JIS	正しく読めた	正しく読めた
BOMありUTF-8	正しく読めた	正しく読めた
BOMなしUTF-8	文字化け	正しく読めた
英数字だけ	正しく読めた	正しく読めた（どちらで読んでも同じ）
半角カナのShift_JIS	正しく読めた	文字化け