シーゴの Excel 研究室

タイトル変更しました (旧称:今日を乗り切るExcel研究所)

【PowerShell】CSV の前処理の前処理をするスクリプト(その2)

今回も PowerShell を使った CSV の前処理の前処理のスクリプトの続きです。

値にカンマや改行を含む CSV

前回の記事で、CSV 形式のテキストの整形処理をする PowerShell スクリプトを作成しました。

大抵はそれで事足りると思うのですが、 一部のスクリプトについて、CSV 形式によっては対応しきれないものがあります。

フィールドが単純にカンマと改行で区切れれば問題ないのですが、クォートされて値にカンマや改行コードが含まれるような複雑な CSV は扱えません。

本記事ではクォートされたフィールドの処理にも対応した改善版のスクリプトを紹介します。

ただし、正規表現を使った置換処理を多用しているため、非常に重たくなっています。 巨大な CSV ファイルでは実用的なパフォーマンスが得られないかもしれませんので、ご承知おきください。

PowerShell について詳しいわけでなく調べながらなので、もし間違いやもっといいやり方・定石などがありましたらコメントや Twitter にてご指摘くだされば有難いです。

CSV の正規表現

本記事での CSV 形式の解析には PowerShell の正規表現エンジンを用います。

CSV にマッチする正規表現のパターンというのもいろいろ考えられるのですが、本記事では以下のパターンを基本として採用します。

(?s)(?!$)(( *("?)((""|.)*?)\3 *)(, *("?)((""|.)*?)\7 *)*)(\r?\n|$)

このパターンは CSV テキストのレコード(行ではなく)にマッチして、各フィールドをキャプチャします。

対応可能な CSV 形式は以下の通り。

  • レコード区切りは CRLF か LF
  • フィールド区切りはカンマ(,)
  • フィールド値はダブルクオーテーション(")で囲まれる(クォートされる)ことがある
    • クオート内のカンマはフィールド区切りと見做さない
    • クオート内の改行はレコード区切りと見做さない
    • クオート内にあるダブルクオーテーション自体は二重化("")によりエスケープされていること
  • フィールドの前後に半角スペースがあっても良い

これで、フィールド値にカンマや改行コードを含むような CSV ファイルでも処理できるようになるわけですが、その代償として

  • 複雑な正規表現を使い、コールバックで置換をしているので処理速度が遅い
  • CSV ファイル全体を一つの文字列としてロードするので十分なメモリが必要

つまり非常に重たくなります。 あまり巨大な CSV ファイルの処理に実用的ではないかもしれません。

 



 

CSV 前処理の前処理をするスクリプト

列数を揃える

行によってカンマの数が揃っていない CVS ファイルに対し、フィールドの追加・削除を行って、指定の列数に揃えます。

最初の引数で列数を指定します。

フィールド内にカンマや改行があっても正しく処理されます。

function resize_csv([int]$num, [string]$file, [string]$newFile) {
    $pat = '(?s)(?!$)(?<F> *("?)(?:""|.)*?\1 *)(?:,(?<F> *("?)(?:""|.)*?\2 *))*(?<N>\r?\n|$)'
    $rep = {
        $flds = @() + $args[0].Groups["F"].Captures.Value
        [array]::Resize([ref]$flds, $num)
        ($flds -join ",") + $args[0].Groups["N"].Value
    }

    $csv = (Get-Content -Raw -Encoding UTF8 $file) + ""
    $csv = [regex]::Replace($csv, $pat, $rep)
    New-Item -Path $newFile -Value $csv > $null # BOM なし UTF-8 で保存
}

【使い方】

resize_csv 列数 入力ファイル名 出力ファイル名
  • 指定の「列数」より少ないフィールドをもつ(カンマが足りない)行では空のフィールドが追加されます
  • 指定の「列数」より多いフィールドを持つ(カンマが余分)行では余分なフィールドが削除されます
  • 改行を含むフィールドはダブルクォーテーションで囲まれている必要があります
  • 入力エンコードは UTF-8 (BOM 付き可)です
  • 出力エンコードは BOM なし UTF-8 です
  • 「出力ファイル名」がすでに存在する場合は上書きされずにエラーとなります

【使用例】

PS > resize_csv 10 org.csv resized.csv

列の切り出し

CSV からの任意の列を切り出します。

抽出したい列の位置を配列で指定します。列の位置は 0 から始まる番号です。 つまり最初の列には 0 を指定します。

フィールド内にカンマや改行があっても正しく処理されます。

function cut_csv([int[]]$cols, [string]$file, [string]$newFile) {
    $pat = '(?s)(?!$)(?<F> *("?)(?:""|.)*?\1 *)(?:,(?<F> *("?)(?:""|.)*?\2 *))*(?<N>\r?\n|$)'
    $rep = {
        $flds = @() + $args[0].Groups["F"].Captures[$cols].Value
        ($flds -join ",") + $args[0].Groups["N"].Value
    }

    $csv = (Get-Content -Raw -Encoding UTF8 $file) + ""
    $csv = [regex]::Replace($csv, $pat, $rep)
    New-Item -Path $newFile -Value $csv > $null # BOM なし UTF-8 で保存
}

【使い方】

cut_csv 列位置の配列 入力ファイル名 出力ファイル名
  • 「列位置の配列」には抽出したい列番号(0列目からの番号)を PowerShell の配列(カンマ区切りや範囲)で指定します
  • 改行を含むフィールドはダブルクォーテーションで囲まれている必要があります
  • 入力エンコードは UTF-8 (BOM 付き可)です
  • 出力エンコードは BOM なし UTF-8 です
  • 「出力ファイル名」がすでに存在する場合は上書きされずにエラーとなります。

【使用例】

PS C:¥> cut_csv 2,4,9 in.csv out.csv # , 区切りで列の位置を指定
PS C:¥> cut_csv 9,4,2 in.csv out.csv # 順序も変えられる
PS C:¥> cut_csv (2..9) in.csv out.csv # (x..y) で範囲を指定
PS C:¥> cut_csv (2..5 + 7..9) in.csv out.csv # + で複数範囲を指定
PS C:¥> cut_csv (2,5 + 7..9) in.csv out.csv # + で位置と範囲を指定
PS C:¥> cut_csv (,5 + 7..9) in.csv out.csv # 先頭に1列だけ追加したいときは , が必要 

列指定でクオート囲み

列を指定して、フィールド値をダブルクォーテーションで囲みます。

抽出したい列の位置を配列で指定します。列の位置は 0 から始まる番号です。

逆に引数で指定されなかった列はクオートされず、もともとダブルクォーテーションで囲まれていたとしても外されます。 ただしクオートが必須な場合、つまりカンマ、改行コード、ダブルクォーテーションが含まれる場合は例外的にそのまま温存されます。

また、空の配列 @() を指定すると、クオート必須以外は全てのフィールドでクオートなしを指定したこととなります。 つまり、Excel の保存する CSV と同等の形式になります。

function quote_csv([int[]]$cols, [string]$file, [string]$newFile) {
    $pat = '(?s)(?!$)(?: *("?)(?<V>(?:""|.)*?)\1 *)(?:, *("?)(?<V>(?:""|.)*?)\2 *)*(?<N>\r?\n|$)'    
    $rep = {
        $vals = $args[0].Groups["V"].Captures.Value -replace '""','"'
        $flds = for ($c=0; $c -lt $vals.Count; $c++) {
            $val = $vals[$c]
            $specified = $cols.Contains($c)
            $needed = $val -match ',|\r?\n|"'
            if ($specified -or $needed) {
                '"' + ($val -replace '"','""') + '"'
            } else {
                $val
            }
        }
        ($flds -join ",") + $args[0].Groups["N"].Value
    }

    $csv = (Get-Content -Raw -Encoding UTF8 $file) + ""
    $csv = [regex]::Replace($csv, $pat, $rep)
    New-Item -Path $newFile -Value $csv > $null # BOM なし UTF-8 で保存
}

【使い方】

quote_csv 列位置の配列 入力ファイル名 出力ファイル名
  • 「列位置の配列」には抽出したい列位置(0列目からの番号)を PowerShell の配列(カンマ区切りや範囲)で指定します
  • フィールドの前後にあった空白は除去されます
  • 入力エンコードは UTF-8 (BOM 付き可)です
  • 出力エンコードは BOM なし UTF-8 です
  • 「出力ファイル名」がすでに存在する場合は上書きされずにエラーとなります。

【使用例】

PS C:¥> quote_csv 2,4,9 in.csv out.csv # , 区切りで列の位置を指定
PS C:¥> quote_csv (2..9) in.csv out.csv # (x..y) で範囲を指定
PS C:¥> quote_csv @() in.csv out.csv # @() は空の配列で指定なし
PS C:¥> quote_csv (2..5 + 7..9) in.csv out.csv # + で複数範囲を指定
PS C:¥> quote_csv (2,5 + 7..9) in.csv out.csv # + で位置と範囲を指定
PS C:¥> quote_csv (,5 + 7..9) in.csv out.csv # 先頭に1列だけ除きたい場合は「,」 が必要 

 



TSV ⇒ CSV変換

TAB 区切りデータのファイルをカンマ(,)区切りに変換します。

フィールド値にカンマが含まれる場合、フィールドをダブルクオーテーション(”)で囲みます。

フィールド内改行があっても正しく処理されます。

function tsv2csv([string]$file, [string]$newFile) {
    $pat = '(?s)(?!$)(?<F> *("?)(?:""|.)*?\1 *)(?:\t(?<F> *("?)(?:""|.)*?\2 *))*(?<N>\r?\n|$)'
    $rep = {
        if ($args[0].Value.Contains(",")) {
            $flds = $args[0].Groups["F"].Captures.Value
            $flds = $flds -replace '^((?> *))(?!")(.*?,.*?)( *)$','$1"$2"$3'
            ($flds -join ",") + $args[0].Groups["N"].Value
        } else {
            $args[0].Value -replace "\t",","
        }
    }
    
    $tsv = (Get-Content -LiteralPath $file -Raw -Encoding UTF8) + ""
    $csv = [regex]::Replace($tsv, $pat, $rep)
    New-Item -Path $newFile -Value $csv > $null # BOM なし UTF-8 で保存
}

【使い方】

tsv2csv TSVファイル名 CSVファイル名
  • カンマを含むフィールドはダブルクォーテーションの囲みを追加します
  • フィールド内改行があっても、ダブルクォーテーションで囲まれていれば問題ありません
  • フィールド値内に TAB が含まれると正常に処理できません
  • 入力エンコードは UTF-8 (BOM 付き可)です
  • 出力エンコードは BOM なし UTF-8 です
  • 「CSVファイル名」がすでに存在する場合は上書きされずにエラーとなります。

【使用例】

PS C:¥> tsv2csv data.tsv data.csv

免責

本記事に掲載されている PowerShell スクリプトは、本記事のために新たに作成されたものであり、運用実績がありません。 できうる限り動作確認は行っていますが、筆者の想定しきれないデータ内容や使用方法などにより、期待した結果やパフォーマンスが得られない可能性があります。
したがって本記事に掲載されている PowerShell スクリプトは、あくまで個人の責任の範囲でのご使用をお願いします。 本記事掲載のスクリプトには特に使用条件や制限はありませんが、実務でのご利用の際には十分ご注意の上、必ず結果の検証を行ってください。
なお、本記事記載のスクリプトの使用によって発生したいかなる損害・損失についても、筆者は責任を負いかねますので予めご了承ください。

参考資料

関連記事

www.shegolab.jp

www.shegolab.jp

更新履歴

  • [2022/08/15] 投稿
  • [2022/08/29] その2へのリンクを追加