openpyxl.utils.exceptions.IllegalCharacterErrorを正しく解決する

# tl;dr

* CSV -> XLSXに変換するときにopenpyxl.utils.exceptions.IllegalCharacterErrorや_csv.Errorが出たので解決した
* コピペで対処したらちょっとハマった
* 最終的にはこうなった↓

```python
import re
import os
import openpyxl

_ILLEGAL_CHARACTERS_RE = re.compile(r"[\000-\010]|[\013-\014]|[\016-\037]")

def _fix_csv(self, csv_file_path: str, fixed_csv_path: str):
    with open(csv_file_path, "r") as fi:
        with open(fixed_csv_path, "w") as fo:
            line = fi.readline()

            while line:
                fixed_line =_ILLEGAL_CHARACTERS_RE.sub("", line)
                fo.write(fixed_line)
                line = fi.readline()

def csv_to_xlsx(csv_file_path: str, output_file_path: str)
    fixed_csv_path = '.temp.csv'
    try:
        _fix_csv(csv_file_path, fixed_csv_path)

        wb = openpyxl.Workbook(write_only=True)
        wb.create_sheet('Sheet1')
        wb.active = wb.sheetnames.index(sheet_name)
        ws = wb.active

        with open(fixed_csv_path, "r") as f:
            reader = csv.reader(f)
            for row in reader:
                ws.append(row)

        wb.save(output_file_path)
    finally:
        if os.path.exists(fixed_csv_path):
            os.remove(fixed_csv_path)


csv_to_xlsx("入力のcsvファイル.csv", "出力のxlsxファイル.xlsx")

```

# はじめに

PythonでCSV -> XLSX変換をやろうとしたら思ったより時間がかかったのでメモ。

# 環境

* Python: 3.8.7
* openpyxl: 3.0.6


# 問題1：CSV -> XLSX変換時にopenpyxl.utils.exceptions.IllegalCharacterErrorが出る

CSV形式ファイルをXLSX形式ファイルに変換するのに、Pythonのopenpyxlを使って変換することにした。

が、特定のCSVを変換・保存するときに `openpyxl.utils.exceptions.IllegalCharacterError` が送出され、失敗した。

# 原因1：不正な文字列を含んでいる。

openpyxlでは文字列に不正な文字列を含んでいると例外が送出されるようなセーフティーの処理が組み込まれている。
https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/cell/cell.py#L161

実は、今回入力となるCSVはユーザーが好き勝手に入力したテキストを含んでいるため、文字列として一切の正規化がされておらず変な文字が入っていることがあるのだが、そのせいで例外が送出されていたようだ。

# 正しくなかった解決：コピペ

`openpyxl.utils.exceptions.IllegalCharacterError` でググると似たような記事が引っかかるのでその解決のコードをコピペしてみたところ、例外が送出されなくなった。
後述のとおり、この対処は結果的に正しくなかった。
ので、詳細はここには書かない。

# 問題2：改行が消える

よしよしと思い、変換に成功したcsvファイルを見てみると **改行が消えている** 。

# 原因2：消してはいけない文字まで消している

コピペした解決のコードは、openpyxlで不正として扱われる文字列を、openpyxlに渡す前にあらかじめ消しておくという意図のコードだった。

openpyxlでの不正な文字列は以下の正規表現で表される。

```python
'[\000-\010]|[\013-\014]|[\016-\037]' # 16進表現だと `[\x00-08][\x0b-\x0c][\x0e-\x1f]`
```

もう少しわかりやすくUnicode表を使って表現すると

|     | +0  | +1  | +2  | +3  | +4  | +5  | +6  | +7  | +8  | +9  | +A  | +B  | +C  | +D  | +E  | +F  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 0   | NUL | SOH | STX | ETX | EOT | ENQ | ACK | BEL | BS  |     |     | VT  | FF  |     | SO  | SI  |
| 10  | DLE | DC1 | DC2 | DC3 | DC4 | NAK | SYN | ETB | CAN | EM  | SUB | ESC | FS  | GS  | RS  | US  |

が不正である（空白のセルは不正ではない文字）。

が、コピペ元の記事ではこの正規表現にカスタマイズが加えられており、

|     | +0  | +1  | +2  | +3  | +4  | +5  | +6  | +7  | +8  | +9  | +A  | +B  | +C  | +D  | +E  | +F  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
| 0   | NUL | SOH | STX | ETX | EOT | ENQ | ACK | BEL | BS  | HT  | LF  | VT  | FF  | CR  | SO  | SI  |
| 10  | DLE | DC1 | DC2 | DC3 | DC4 | NAK | SYN | ETB | CAN | EM  | SUB | ESC | FS  | GS  | RS  | US  |

と、

|     | +0  | +1  | +2  | +3  | +4  | +5  | +6  | +7  | +8  | +9   | +A  | +B  | +C  | +D  | +E  | +F  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ---- | --- | --- | --- | --- | --- | --- |
| 70  |     |     |     |     |     |     |     |     |     |      |     |     |     |     |     | DEL |
| 80  | PAD | HOP | BPH | NBH | IND | NEL | SSA | ESA | HTS | HTJ  | VTS | PLD | PLU | RI  | SS2 | SS3 |
| 90  | DCS | PU1 | PU2 | STS | CCH | MW  | SPA | EPA | SOS | SGCI | SCI | CSI | ST  | OSC | PM  | APC |

と、おまけに `\uffff` が除去される対象になっていた。見てわかるとおり、これでは改行として使われるLFやCRも取り除かれてしまう。

おそらくコピペ元の記事の著者の環境では改行コードが元々含まれないデータであり、かつ `DEL` 〜 `APC` が邪魔をするような状況だったのであろう。

一方、筆者の環境では、 **このカスタマイズが邪魔をしていた**[^1] のだ。

[^1]: 筆者が最初にコピペした記事を **そのまま鵜呑みにして書いたであろう別の記事が存在しており、** 「2つもソースがあるから正しいだろう」との思い込みがハマりポイントになってしまった。

# 解決2：不正な文字列を `'[\000-\010]|[\013-\014]|[\016-\037]'` にする

コピペ元の記事のカスタマイズされてしまった部分を元に戻せばよい。
除去の正規表現を `'[\000-\010]|[\013-\014]|[\016-\037]'` に書き換えた。

# 問題3：CSVの読み込みで `_csv.Error: line contains NULL byte` が出る

今度こそ解決だと思ったら、今度は、先程までとは別のCSVの読み込みでエラーが出た。

# 原因3：NULL( `\x00` )を含んでいた

問題2まではopenpyxlモジュールの問題だったが今度はcsvモジュールの問題だった。
どうやら、csvモジュールはNULL文字（ `\x00` ）を含むファイルは読み込めないようだ。
上述のとおり、openpyxlのための不正な文字列の対象にはNULL文字も含まれているのだが、除去処理はcsvモジュールでの読み込みの後に入れているため、エラーが出てしまった。

# 解決3：テキストとして読み込んで除去する

csvモジュールでの読み込みの前に不正な文字を除去するように、除去のタイミングを前倒しにした。

# 最終的なソースコード

最終的なソースコードは「tl;dr」を参照のこと。

ソースコードでははじめにCSVを単なるテキストファイルとして読み込み、不正な文字列の除去を行ってから一時ファイルに出力するようにした。
そして、その一時ファイルをCSVモジュールに読み込ませ、openpyxlに渡しすようにした。

# おわりに

安易なコピペはだめ（戒め）。