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

tl;dr

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

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での不正な文字列は以下の正規表現で表される。

'[\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 が邪魔をするような状況だったのであろう。

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

解決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に渡しすようにした。

おわりに

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

---

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