XLSX を NDJSON に変換

JSON の配列はそれ自体が 1 つの値です。中の最後のオブジェクトを読むには、それより前をすべて解析し終えている必要があり、文書全体をメモリーに置くか、部分的な構造を理解できるストリーミングパーサーを使うことになります。設定ファイルであれば問題になりませんが、行数が 400 万あれば、それは正常に動く取り込みと OS に強制終了させられる処理との分かれ目になります。

改行区切りの JSON はこの問題を、そもそも入れ物をなくすことで解決します。各行が独立した完全な JSON 値なので、読み込む側は 1 行読んで処理し、捨てて次に進めます。バルクローダーやログの転送、データウェアハウスの取り込みがそろってこの形にたどり着いたのはこの性質のためです。失敗したときに再開できるのも同じ理由です。80 万レコード目で失敗したストリームはその行から再開できますが、解析に失敗した 1 つの文書は何も渡してくれず、どのレコードが原因かもわかりません。

この保証が成り立つのは、データ側から改行が生まれないよう変換が保証しているからです。セルの中の改行——複数行の住所や Alt+Enter で入力されたコメント——は JSON の文字列の中でエスケープされた状態で書かれるため、レコードは必ず 1 行に収まります。セルの中のタブも同様にエスケープされます。

これは区切り文字を使う形式に対する具体的な優位点です。同じシートをタブ区切りで書き出すと、扱いにくいセルを引用符で囲み、読み取る側がその約束を実装していることを期待するしかありません。ここではその心配自体がありません。1 万行ずつファイルを分割しても安全で、行数を数えればそのままレコード数がわかり、上の行によって下の行が壊れることもありません。

区切り文字の形式には型が 1 つしかありません。文字列です。読み込む側は推測するしかなく、その推測こそが ID を整数にしてしまったりバージョン番号を小数にしてしまったりする原因です。JSON はその区別を明示的に持てるため、数量は 12、フラグは true、欠損値は null として書かれ、表計算では文字列だった商品コードは先頭のゼロを保ったまま引用符付きで残ります。

型付きの取り込み先にとって、これは本当に時間の節約になります。宣言したスキーマと渡す値の間に変換の層を挟まずに一致させられ、取り込みが行を拒否するときも、行番号のエラーではなくどの項目が食い違っているかが返ってきます。この型は表計算ソフトが持っていたものであり、文字を見て推測されたものではないことが重要です。数値としてセルに入っていたものは、数値に見える文字だったからではなく実際に数値だったから、この出力でも数値として書かれます。

すべての行がすべてのキーを繰り返します。6 列を持つ 5 万行のシートは、6 個のフィールド名を 5 万回複製することになり、実際のシートで測ると同じデータがタブ区切りのテキストで 1.8 MB だったのに対しここでは約 4.4 MB、およそ 2.5 倍でした。

この行き先にとってはたいてい妥当な代償です。ファイルは機械によって 1 回読まれるために存在するからです。同じレコードを何度も問い合わせるなら、行指向ではなく列指向の形が向いていて、同じシートはそのほんの一部の大きさで収まります。NDJSON は転送のための形式であり、保存のための形式ではありません。転送中であれば容量はほとんど問題になりません。中身のよく圧縮できる繰り返しのおかげで、gzip をかければオーバーヘッドのほとんどが消えるからです。

この形のおかげで使えるツールが決まります。wc -l は行数をそのまま正確な件数として返し、head -1 は覚えていた名前ではなく実際に書かれたフィールド名を見せてくれます。split -l 10000 で作った各チャンクはそれ単体で有効です。

それ以上のことをするなら jq が値のストリームとして読み込めるので、jq -c "select(.qty > 100)" はメモリーより大きなファイルでも絞り込め、読み込んだのと同じ形式で出力します。この組み合わせやすさこそが、あとで分解し直す必要がある配列ではなく、この形に変換しておく理由です。誰かから送られてきた表計算ファイルを検分する最も安上がりな方法でもあります。1 つのコマンドでフィールド名がわかり、別のコマンドで行数がわかり、3 つ目のコマンドである列のすべての異なる値を確認できます。どれもワークブックを開いたり、最初の画面の見た目を信用したりする必要がありません。

表計算ソフトの日付は表示形式をまとった日数にすぎず、この変換は見た目ではなく実際の値を書き出します。2024 年 1 月 1 日は 45292 になり、時刻を含む値には端数が付いた同じ数値になります。この数え方は 1899 年 12 月末を起点にしています。

この組み合わせで特に注意すべき失敗の形です。JSON の数値としては正当なので、数値の列を持つ取り込み先は 45292 を何のエラーもなく受け入れてしまいます。こうした項目は意図的に変換してください。ファイル全体に対する jq のステップで行うか、取り込んだ後に行うかはともかく、6 週間後にダッシュボードで気づくのではなく、パイプラインの一部として組み込んでおくべきです。

空のセルは省略されるのではなく null として書かれるため、すべてのオブジェクトが同じキーを持ち、決まった形を期待する取り込み先にとって扱いやすくなります。後の行で最初の行になかった項目が現れた場合は、シート全体の和集合がキーになるため、遅く現れた項目が失われることはありません。

ワークブックの最初のシートだけが変換されます。レコードのストリームには「ここから別の表です」を表す方法がないからです。必要なシートが先頭にない場合は、ワークブックの並びを変えてから再度変換してください。複数のシートが必要な場合は複数回変換して別々のストリームとして取り込んでください。取り込み先もそれを求めることがほとんどです。

すべてお使いのブラウザーの中で処理されます。ワークブックの解析も行の書き出しもその場で行われ、アップロードも待ち行列もありません。顧客データや注文情報、データ保護の方針が適用される情報の書き出しにとって、これは職場で使えるツールと使えないツールの違いになります。

上限は 1 ワークブックあたり 100 MB、1 回のドロップで 100 ファイルまでで、これは全員に共通の上限であって、支払って超えられる枠ではありません。それよりずっと手前で気になり始めるのはメモリーです。シートはいったん配列として読み込まれてから書き出されるため、数十メガバイト程度なら問題なくても、上限に近いワークブックではブラウザーのタブが重くなり始めます。それを超える規模には、スクリプトの中でストリーミングリーダーを使うのが適切な道具で、非常に大きなファイルの 4 分の 3 まで進んでから失敗するより、そう伝えるほうが役に立ちます。