【xlsx2csvを使おう!】 Excelデータシートのセル内改行をCsvファイルで取り扱う方法


2021/04/03

csvをシェルコマンドで取り扱う上でもっとも厄介なものが改行文字です。

Excelではセルの中の文字列を改行して複数行で表示することができますが、Csv形式では改行文字
\nによって行を識別するため、ExcelデータシートをそのままCsvファイルにエクスポートしてしまった場合、セルの途中の文字列にある改行文字により意図としない箇所でcsvデータが行分割されてしまいます。

今回は、
セル内改行をCsv形式のテキストファイルでどのように扱えば良いのかを解説します。


はじめに

当サイトではオフィス業務のComputer-Aidedなハイブリッドな方法を模索し、より効率的なExcel業務を実現したい多忙なオフィスワーカー向けの主にAwkとSedを使うシェル講座です。

シェルスクリプトはどこでもどんなOSでも基本的に使えて、しかも一度使い方を覚えると、Excelと組み合わせて最高に効率の良いオフィスワークツールが作れることでしょう。

合同会社タコスキングダム|蛸壺の技術ブログ


セル内改行を含むCsvファイルについて

まず、セル内改行を含む文字列をExcelからエクスポートした場合のcsvの中身はどのようなものか確認しましょう。

合同会社タコスキングダム|蛸壺の技術ブログ

このシートをExcelから直接csv形式でエクスポートした場合に、シェル上で表示してみると以下のように表示されるでしょう。

            
            $ cat hoge.csv
"のどかな
田園風景","あ
小林さん!","メガネは
ずれ落ちる"
w,e,l
12,5,7
        
この標準出力から分かるように、セル内改行を含む文字列はダブルクォーテーション括弧("...\n...")の内部で定義されることで、その文字列はセルの中で改行されていることを示しています。

ですが、このやり方は、改行位置を\nで判別しているシェルコマンド処理にはとても相性の悪いため、セル内改行はあらかじめ
改行エスケープにしておかないと行けません。

xlsx2csvによる-eオプションの利用

今回はこの改行エスケープを別記事にて紹介していたxlsx2csvコマンドを使う方法を紹介します。

xlsx2csvを使えばExcelシートから直接かつ簡潔に、セル内改行の改行エスケープを行うことが可能です。

先程例に挙げたExcelファイルをhoge.xlsxとしてローカルに保存し、xlsx2csvコマンドから直接csvデータにアクセスしてみましょう。

            
            $ xlsx2csv -e hoge.xlsx | awk -F"," '{ print $1, $2, $3; }'
#👇出力
のどかな\n田園風景 あ\n小林さん! メガネは\nずれ落ちる
w e l
12 5 7
        
結果は見ての通りで、セル内改行の適切な位置で\nとして改行エスケープが実行されています。

もしもxlsx2csvによる-eオプションではなく、同様の結果をSedスクリプトで操作しようと思うと、改行を含む複数行に跨る文字列をマッチさせたり、ループ処理やNコマンドやホールドスペースの活用など、非常に厄介な問題をクリアしないといけなくなります。

ということで、Sedでも同じ処理をやれなくはないのですが、過去にxlsx2csvの-eオプションを知る前に使っていたセル内改行の自動検出のスクリプトは上級シェル芸人向きになるため、腕試しとしては面白い題材ですが、実用上はxlsx2csv(-eオプション)が最強です。

個人的にはExcelデータをシェルコマンドに橋渡しする上で、xlsx2csvの-eオプションはまさに神ツールのような気がします。

記事を書いた人

記事の担当:taconocat

ナンデモ系エンジニア

主にAngularでフロントエンド開発することが多いです。 開発環境はLinuxメインで進めているので、シェルコマンドも多用しております。 コツコツとプログラミングするのが好きな人間です。