今日を乗り切るExcel研究所

Excel に働かされていませんか

Excel に消された CSV のダブルクォーテーションを元に戻したい

今回は、Excel による CSV ファイルの編集で、ダブルクォーテーションが失われてしまう問題について調べ、それを復活させる方法を検討します。

ダブルクォーテーションを返してほしい

CSV ファイルをダブルクリックすると当然のように Excel で開かれますが、そのときは注意が必要です。 開くだけならまだしも、保存すると CSV の形式が勝手に変更されてしまうからです。

その一つに今回取り上げる「ダブルクォーテーションが消える問題」があります。 CSV のフィールド値を囲んでいるダブルクォーテーション(")が、Excel で保存するだけで失われ、ハダカにされてしまうのです。

CSV でデータをやり取りするときにはこれがトラブルの元になることがあります。 Excel でCSVを保存しなければいい話なのですが、業務的に避けられないこともあります。

CSV のダブルクォーテーションを復活させることは出来ないのでしょうか。

ダブルクォートを復活させるには

高機能な Excel ならそれぐらい簡単にできてよさそうなものですが、調べてもそのような機能はありません。 ただ、この問題は Excel ばかりを責められた事でもないのです。

問題の根は CSV の形式の「方言」にあります。

一言で CSV といっても、いろいろ微妙な形式の違い(方言)があり、システムによって解釈が異なるのです。 違いはいろいろありますが、今回のクォートの扱いについて言えば、大まかに次の4つのポリシーに分けられます。

  1. 一切のフィールドをクォートしない
  2. 基本的にクォートなしで、必要に迫られたフィールド値のみクォートする
  3. 決められたフィールドタイプのみクォートする(数値以外など)
  4. すべてのフィールドをクォートする

Excel は2番目のタイプです。

タイプ2の「必要に迫られた」というのは、フィールド値の中にカンマ(,)が使われていたり、折り返し(改行)を含むテキストデータなど、CSV のデータ区切りを混乱させる文字がある場合です。 このようなフィールド値があったときのみ、ダブルクォーテーションで囲んで、データの境界を明確にします ちなみに、テキストデータ内に元々あった「"」は、2重の「""」に変更することでクォート囲みと区別されます。

ただ、そのようなデータはそう多くはないので、ぱっと見ではクォートなしのタイプ1と区別できません。

Excel はクォート囲みの多いタイプ3や4の CSV 形式でも問題なく開くことができるのですが、元にあったダブルクォーテーションはすっぱりと捨ててしまいます。

失われたものを取り戻すのは難しいです。

しかし、もしタイプ4 CSV でいいなら、つまり全てのフィールドをダブルクォーテーションで囲み直すだけでいいなら、テキストエディタの置換機能で何とか復旧させることができます。

テキストエディタの置換機能を使う

テキストエディタの「置換」機能をうまく使うと、一括でダブルクォーテーションを挿入することができます

テキストエディタの置換機能は普段から活用されている方も多いかと思います。 置換ダイアログで、「検索」文字列と「置換」文字列を入力して「すべて置換」を実行すると該当文字列が一括で置換されます。操作自体は難しくありません。

その代り、ちょっとした頭の体操が必要です。

【手順例】

  1. テキストエディタで CSV ファイルを開き、「置換」ダイアログを開きます
  2. 置換オプションで特殊な文字表現の使用を許可します
    • 秀丸:「正規表現」をチェック
    • サクラエディタ:「正規表現」をチェック
    • TeraPad:「¥n=改行, ¥t=TAB, ¥¥=¥」をチェック
    • EmEditor:「エスケープシーケンスを使用する」をチェック
    • メモ帳:使えません!
  3. 「検索」に「,」を、「置換」に「","」を入力して全て置換を実行します
    • これですべての「,」が「"」で挟まれます。 結果として「,aaa,」のように挟まれたデータ部分が「","aaa","」のように「"」で囲まれます
    • この時点で各行の先頭と終端のみ「"」が欠けている状態になります。
  4. 「検索」に「¥n」を、「置換」に「"¥n"」を入力して全て置換を実行します
    • 「¥n」は改行を表す特別な表現です
    • これですべての改行が「"」で挟まれます 結果として「aaa","bbb","ccc」のような行の先頭と終端に「"」が挿入され「"aaa","bbb","ccc"」になります。
    • 【注意】サクラエディタなど一部のテキストエディタでは、「¥n」では余分な改行で行数が増えてしまうことがあります。 その場合、「¥n」の代わりに「¥r¥n」を試してみてください
      • 「検索」に「¥r¥n」を、「置換」に「"¥r¥n"」を入力して全て置換
  5. CSV ファイルの先頭(先頭行の行頭)の足りない「"」を入力します
  6. CSV ファイルの最終行に追加されている余分な「"」を削除します
  7. すべてのフィールド値がダブルクォートされています。

置換ができたら間違いがないか結果のテキストをよく見て確認してください。 特に、CSVの最初と最後が変なことになっていないか念入りに確認します。 もし間違った置換でCSVが壊れたら、「元に戻す」(Ctrl+Z)で戻せます。

「¥n」のような円マーク付きの文字列は、「エスケープシーケンス」といい、 文字では表せない文字コードを指定するための特別な表現です。 「¥n」や「¥r」は改行を表します。 ほかにも TAB(タブ)を表す「¥t」というものがあります。 本来はプログラマなど技術者向けの用途ですが、一般の業務でも知っておくと役に立つことがあります。

さて、これで全てのフィールド値をクォート囲みすることができました。 ただ、手順がちょっと煩雑で、1・2回ならいいですが、3回もやるともう飽きますね。 まして定型作業になるなら、せめて1ステップの手順でできないと、余計な仕事感に耐えられないでしょう。 エディタによってはマクロなどで自動化できるのかもしれませんがちょっと敷居が高い感じです。

もし、置換ダイアログに「正規表現」という機能があるなら、それを活用する方法もあります。

テキストエディタの正規表現置換を使う

「正規表現」を使うと、置換操作がほぼ1ステップで済みます。

その代り、頭の体操がバク転なみになります。

【手順例】

  1. テキストエディタで CSV ファイルを開き、「置換」ダイアログを開きます
  2. 置換ダイアログのオプションで「正規表現」を有効にします
    • 秀丸:「正規表現」をチェック
    • サクラエディタ:「正規表現」をチェック
    • EmEditor:「正規表現を使用する」をチェック
    • TeraPad:使えません!
    • メモ帳:使えません!
  3. 「検索」と「置換」の入力欄に以下の文字列を入力します(コピペしてください)
    • 検索: (.*?)(,|\n) (サクラエディタでは (.*?)(,|\r?\n)
    • 置換: "\1"\2
  4. 「全て置換」を実行します。
    • 全てのフィールドが「"」で囲まれます
  5. CSVが壊れていないかよく確認します
    • ファイルの先頭と最後をよく確認してください。特にファイルの最後に「"」の過不足がありがちなので、適宜修正してください

正規表現とは単語など文字の並びを「パターンの式」として表現したものです。

手順の「(.*?)(,|¥n)」というのが、正規表現パターンです。 呪文にしか見えませんが、これで「カンマか改行で終わる0個以上の文字の並び」を表しています。 (上記手順では円マーク(¥)がバックスラッシュ(\)で表示されていますが、コピー&ペーストすれば¥で入力されるはずです)

これも本来は技術者向けの高度な機能ですので、全てのテキストエディタでサポートされているわけではありません。本ブログでは正規表現の書き方までは解説できませんが、初歩的な記号や規則を知っておくだけでもテキスト編集の作業効率が格段に上がりますので、興味のある方は調べてみてください。

タイプ2のクォート囲みをどうするか

さて、これでダブルクォート付き CSV に一回の操作で置換することができました。 ただし、この技が使えるのは元がクォートの一切無い、タイプ1の CSV の時だけです。

Excel CSV はタイプ2なのでした。

タイプ2では、一部のテキストデータなど「必要に迫られた」フィールド値でダブルクォーテーションが使用されている可能性があります。これまで紹介した単純な置換では、すでに混在するクォート囲みを壊してしまいます。それを避けたり直したりするのは簡単そうに見えて実はかなりややこしい事です。

それでも正規表現とやらを使えば、なんとかできるんじゃないかって?

えぇ、できますよ、できますとも、頭の体操が「前方2回半ひねり」レベルになりますが・・・、それとはそれとして、どうも気が乗りませんね。

というのも、テキストエディタによって正規表現の文法や解釈に微妙な違いがあるのです。言ってみれば、呪文にも「方言」があるのです。ちょっと複雑な正規表現パターンになると、方言に惑わされて、やってみないと分からないという理屈の通用しない泥沼の世界に陥ります。このブログでは、さすがにエディタの方言まで研究するわけにはいきません。

どうしたものかと、色々調べてみたところ、実は Windows 自体も独自の正規表現機能(エンジン)を持っているようです。それが標準的に使えるのなら方言の違いに捕らわれることもなくなり好都合です。

 



CSV をダブルクォートするバッチ

Windows の機能を使って Excel CSV をダブルクォート付きの CSV に変換するバッチを作成しました。

Excel で保存した CSV ファイルをこの BAT ファイルにドラッグ&ドロップすると、全てのフィールドがダブルクォーテーションで囲まれた CSV ファイル(タイプ4)が出力されます。元からあるダブルクォーテーションはそのままです。

@setlocal & set PS1="%TEMP%\%~n0.%TIME::=%.ps1"
@more +3 "%~f0">%PS1% && powershell -nop -exec bypass -f %PS1% %*
@del %PS1% & pause & exit /b

filter csv_quote_all {
  $_ -replace '(?s)("?)((""|.)*?)\1(,|\r?\n|\z)','"$2"$4' -replace '(?<!,)""\z'
}

function has_bom($file) {
  !(diff (gc -li $file -enc byte -first 3) (239,187,191))
}

$args|?{Test-Path -li $_ -inc *.csv}|%{$_
  $enc = if(has_bom $_) {'UTF8'} else {'Default'}
  gc -li $_ -raw | csv_quote_all | sc -li "$_.qq.csv" -nonewline -enc $enc
}

【使い方】

  1. テキストエディタに上記バッチスクリプトをコピー&ペーストし、適当な名前を付けて BAT ファイルとして保存します
    • バッチスクリプトを先頭行からきっちり貼り付ける必要があります。先頭に行を空けないように注意してください。
    • ファイルのエンコーディングはシフトJIS(SJIS)にしてください。
  2. CSV ファイルを BAT ファイルのアイコンにドラッグ&ドロップします
    • *.csv のみが変換対象です
    • 複数ファイルでも OK です
  3. 変換後の CSV ファイルが別名で保存されます

このバッチは、「BOM 付き UTF-8」の CSV ファイルにも対応していて、そのままドラッグ&ドロップしで大丈夫です。「BOM 付き UTF-8」は、ファイル保存時に「ファイルの種類」で「CSV UTF-8 (カンマ区切り)(*.csv)」を選んだときの形式で、Excel 2016 からサポートされているものです。BOM 付き UTF-8 ついては以下の記事を参考にしてください。

シフトJISのテキストファイルをUTF-8に変換するバッチ - 今日を乗り切るExcel研究所

また、この BAT ファイルを Windows の「SendTo」フォルダにコピーしておけば、CSV ファイルアイコンを右クリックして「送る」メニューから実行できるようになります。「SendTo」フォルダは、適当なフォルダを開きアドレスバーに「sendto」と入力すると直接開くことができます。

バッチと言いつつ、中身は実質「Windows PowerShell」のスクリプトで、その正規表現機能を使って CSV を一括置換処理しています。一般に正規表現は「重い」です。巨大な CSV ファイルやデータ内容によっては処理に時間がかかったりエラーなるかもしれません。目視によるチェックが可能なデータサイズの範囲での使用にとどめてください。

変換後のCSVファイルは、変換に失敗しているところがないか必ず自分の目で確認してください。もし変換に失敗するケースを見つけたら、コメント等にお知らせください。

【免責】 本ブログ掲載のバッチは、その処理結果の正確性・正当性を保証するものはありません。万一、本バッチの不具合や誤操作により損害や損失が発生した場合も、本ブログの著者は一切の責任を負いかねますのであらかじめご了承ください。
【注意】 セキュリティのため、本記事のバッチに限らず、BAT ファイルは、メールやチャットに添付したり Web リンクや共有フォルダからダウンロードできるようにしないでください。また、そのような経路で入手した BAT ファイルも、内容を確認せずに実行してはいけません。

まとめ

今回は、読者の方にお困りの声があったこともあり、Excel CSV でダブルクォーテーションを復活させる方法を調査しました。

Excel にそのような機能はありませんが、全てクォートを付け直すタイプ4なら、単純なテキスト置換で復旧させることができました。ただそれでも、余計な仕事感は否めませんが。

また、タイプ3は仕様が固定できないので、汎用的な対応方法はなく、個別に考えるしかなさそうです。もし需要があるようなら、タイプ3でも使えるうまい方法がないか別記事で検討したいと思います。

そんな苦労をしなくても、Excel に CSV 形式を保護するか、保存時に指定できる機能があればいいわけで、将来のバージョンでサポートされることを期待したいところです。

正規表現について、興味を持たれた方は一度調べてみてください。 特にパズルの好きな方なら得意になれると思います。 ちょっと難しいようなら、身近なエンジニアに頼む方が早いでしょう。 試験にも出てくる基本スキルですし、頭の体操も大好きな人たちなので、きっと喜んで手伝ってくれると思います。

本記事の内容は以下の環境で検証しました。

  • Windows 10 / PowerShell 5.1
  • 秀丸エディタ Version 8.87 / HMJRE.DLL V5.05
  • サクラエディタ Ver. 2.2.0.1 / bregonig.dll Ver.3.06 with Onigmo 5.15.0
  • EmEditor Free 14.1.0 / Regex++ (Boost 1.52)
  • TeraPad Version 1.09

関連記事

www.shegolab.jp

www.shegolab.jp