シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > データ-CSV > Excel に消された CSV のダブルクォーテーションを元に戻したい

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

データ-CSV Windows

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

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

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

その一つに今回取り上げる「ダブルクォーテーションが消えてしまう問題」があります。

CSV の各フィールドを囲んでいたダブルクォーテーション「"」が、Excel で保存するだけで失われ、フィールドがハダカにされてしまうのです。

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

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

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

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

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

一言で CSV といっても、システムによっていろいろ微妙な形式の違い（つまり方言）があり、解釈が異なるのです。 形式の違いには何種類もありますが、今回のダブルクォーテーションの扱いについて言えば、大まかに次の４つのポリシーに分けられます。

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

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

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

ただ、そのようなフィールドデータはそう多くはないので、タイプ２で保存されたCSVは、ぱっと見ではクォートなしのタイプ１と変わりません。

Excel はクォート囲みの多いタイプ３や４の CSV 形式を含め、全てのタイプ問題なく開くことができるのですが、元にあったダブルクォーテーションはすっぱりと捨て去られてしまうことになります。

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

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

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

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

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

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

テキスト置換でダブルクォート付き CSV に変換することができました。 ただし、この技が使えるのは、もともと CSV にクォートが一切無いタイプ１の時だけに限られます。

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

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

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

えぇ、できますよ、できますとも、頭の体操が「前方2回半ひねり」レベルになりますが・・・、

それとはそれとして、どうも気が乗りませんね。

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

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

　

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

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

Excel で保存した CSV ファイルをこの BAT ファイルにドラッグ＆ドロップすると、全てのフィールドがダブルクォーテーションで囲まれた CSV （タイプ４）に変換されたファイルが出力されます。元からあるダブルクォーテーションはそのままで、壊される心配はありません。

@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」は、Excel でのファイル保存時に「ファイルの種類」から「CSV UTF-8 (カンマ区切り)(*.csv)」を選んだときの形式で、Excel 2016 からサポートされているものです。BOM 付き UTF-8 ついては以下の記事を参考にしてください。

シフトJISのテキストファイルをUTF-8に変換するバッチ - シーゴの Excel 研究室

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

巨大な CSV ファイルやデータ内容によっては処理に時間がかかったりエラーなるかもしれません。目視によるチェックが可能なデータサイズの範囲での使用にとどめてください。

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

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

まとめ

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

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

タイプ３は実際の CSV によって仕様がバラバラなので、汎用的な対応方法はなく、個別に考えるしかなさそうです。とはいえもし需要があるようなら、完全でなくてもタイプ３で使えるうまい方法がないか考えて別記事にします。

【追記】 2022/10/03
列指定でクォートできるバッチを作成しました。お試しください。 www.shegolab.jp

そもそも、そんな苦労をしなくても、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

www.shegolab.jp