【PowerShell】CSV の前処理の前処理をするスクリプト（その１）

今回は Excel ではなくて、PowerShell という技術者向けの話題です。

PowerShell でテキスト処理

データ分析には「前処理」の作業が付き物ですが、CSV データに関してはその前にファイル形式としての体裁を整える、つまり「前処理の前処理」が必要になることがままあります。

結局やることはテキストファイルの加工なので、 Windows 上なら機能豊富なシェル環境であるPowerShell を使えば簡単に出来るはずです。

と思ってやってみたら、決して簡単なことではありませんでした。

PowerShell は言語やコマンドが高機能なわりにかなりのクセがあって意外と使いづらく、単純なテキスト処理でもなかなか一筋縄にはいかないのです。調査や試行錯誤で、だいぶ時間をはたいてしまいました。

今回、記憶が蒸発する前に、先々 CVS 前処理でありそうなあらかたの処理を「コピペで使えるスクリプト」として作成しておくことにします。

間違いや、もっといい方法・定石等があればコメントか Twitter で突っ込んで頂けると有難いです。

また、ほかにもスクリプトがあったらうれしいよくやる前処理があれば一考しますのでお知らせください。

PowerShell でテキスト処理
CSV 前処理の前処理のスクリプト
免責
関連記事
参考資料
更新履歴

CSV 前処理の前処理のスクリプト

PowerShell スクリプトを PS1 ファイルとして実行させたいところですが、実行ポリシー（ExecutionPolicy）がどうのこうので、どうもお気軽ではありません。

今回やる前処理のようなスクリプトは、必要になったときににサクッと一回実行できればいいだけの代物です。

そこで、本記事ではスクリプトをコピペで使い捨てできる関数として用意することにしました。

基本的な使い方

PowerShell コンソールを開きます
本記事掲載の使いたいスクリプトを丸ごとコンソール上にコピー＆ペーストします
スクリプトで定義されている関数名をコマンドとして実行します

引数のチェックなどは省略しているので使うときに注意してください。

【PowerShell メモ】
エクスプローラで今開いているフォルダーのディレクトリで PowerShell をサクッと開けたいところです。方法はいくつかあって、どれもちょっとした隠しコマンドのようで知らないとわかりにくいです。以下のうち使いやすいものをひとつ覚えておけば便利でしょう。

アクセスキー： Alt + F⇒R

マウス操作：フォルダウィンドウで「Shift + 右クリック」してコンテキストメニューに出てくる「PowerShell ウィンドウをここで開く」を選択

アドレスバー：アドレスバー（Alt+D）で powershell と入力

BOM のチェック

ファイルに BOM 付きかどうかを判定する関数です。

指定ファイルのエンコーディングが UTF-8 であり、先頭に BOM がある場合にTrueを返します。

function has_bom([string]$file) {
    $bom = @(239,187,191)
    $tip = Get-Content -LiteralPath $file -First 3 -Encoding Byte
    $null -eq (Compare-Object -SyncWindow 0 $tip $bom)
}

【使い方】

has_bom ファイル名

【使用例】

PS C:¥> has_bom data.csv
True

BOM の除去

BOM 付き UTF-8 ファイルの BOM を除去するコマンドです。

指定ファイルが BOM 付き UTF-8 であれば、標準の BOM なし UTF-8 ファイルとして別にコピーします。

もともと BOM なしだったファイルは何も加工されず、単なるコピーとなります。

function remove_bom([string]$file, [string]$newFile) {
    $content = Get-Content -LiteralPath $file -Raw -Encoding UTF8
    # BOM なし UTF-8 で一括保存
    New-Item -Path $newFile -ItemType File -Value $content > $null
}

【使い方】

remove_bom BOM付きファイル名 コピー先ファイル名

「コピー先ファイル名」がすでに存在する場合は上書きされずにエラーとなります。

【使用例】

PS C:¥> remove_bom data.cvs data.nobom.csv

【PowerShell メモ】
PowerShell のコマンドレットは、UTF-8 でもテキスト保存できますが、いわゆる「BOM 付き UTF-8」になってしまいます。
例外的に New-Item コマンドレットを使えば「（BOM なしの）標準 UTF-8」として保存することができます。

New-Item (Microsoft.PowerShell.Management) - PowerShell | Microsoft Docs

ただし、New-Item はデータを一括保存しかできず、パイプラインを使った追記のようなことはできません。これは巨大なデータを扱う際に問題となるかもしれません。
　
実は最新バージョンの PowerShell 7.2 では状況が改善され、標準のコマンドレットでも標準 UTF-8 で保存できるようです。 Set-Command などで指定できるエンコーディングに標準 UTF-8（utf8NoBOM）が追加されています。
また、CSV 関連のコマンドレットも機能が増えて使い勝手が良くなっているようです。
ただし最新の PowerShell 7.2 を使うにはシステムに別途インストールする作業が必要になります。
Windows 11 で PowerShell もアップグレードされたのではと期待したのですが、古い PowerShell 5.1 のままのようです。

複数 CSV ファイルの連結

複数の CSV ファイルを連結（結合）するコマンドです。このとき CSV のヘッダー行に配慮します。

各ファイルが先頭行に共通のヘッダー行を持っている前提で、最初のファイルからのみ先頭行を出力し、後続ファイルでは先頭の１行目をスキップします。

function cat_csv([string[]]$files, [string]$newFile) {
    # ワイルドカードを展開しておく
    $files = Get-Item -Path $files

    # 最初のファイルのヘッダ行のみ # BOM なし UTF-8 で保存
    $first = Get-Content -First 1 -LiteralPath $files[0] -Encoding UTF8 | 
        Out-String
    New-Item -Path $newFile -Value $first > $null
    
    # 先頭行を飛ばして追記
    foreach ($f in $files) {
        Get-Content -LiteralPath $f -Encoding UTF8 |
            Select-Object -Skip 1 |
            Add-Content -Path $newFile -Encoding UTF8
    }
}

【使い方】

cat_csv ファイル名の配列 結合後ファイル名

「ファイル名の配列」は、複数ファイル名をカンマ区切りで指定するか、ワイルドカードを使います。
入力エンコードは UTF-8 （BOM 付き可）です
出力エンコードは BOM なし UTF-8 です
改行コードは CRLF 前提です。
「結合後ファイル名」がすでに存在する場合は上書きされずにエラーとなります。

【使用例】

# 複数ファイル名をカンマ区切りで渡す
PS C:¥> cat_csv data01.csv,data02.csv,data03.csv all.csv 
# ワイルドカード使用可
PS C:¥> cat_csv .¥*.csv all.csv
# パイプライン入力可
PS C:¥> Get-Item .¥*.csv | csv -dst all.csv

行内改行のエスケープ

行内改行をエスケープするコマンドレットです。

テキストフィールド中に改行文字があった場合、それをエスケープ表現（¥n）に置換します。

function esc_nl([string]$file, [string]$newFile) {
    # 置換後文字
    $alt = "\n"
    # ダブルクオーテーションで囲まれたテキストフィールドを抽出する正規表現
    $textField = '(?s)"((""|.)*?)"'
    # CSV 一括読み込み
    $csv = Get-Content -LiteralPath $file -Raw -Encoding UTF8
    # 改行コード置換
    $csv = [regex]::Replace($csv, $textField, {$args[0].Value -replace "\r?\n", $alt})
    # BOM なし UTF-8 で保存
    New-Item -Path $newFile -Value $csv > $null
}

【使い方】

esc_nl 入力ファイル名 出力ファイル名

改行を含むテキストフィールドはダブルクォーテーション（"）で囲まれている必要があります。
「"」を含むテキストフィールドもフィールド全体をダブルクォーテーションで囲まれている必要があります。データ内の「"」は「”"」に２重化されている必要があります。
入力エンコードは UTF-8 （BOM 付き可）です
出力エンコードは BOM なし UTF-8 です
「出力ファイル名」がすでに存在する場合は上書きされずにエラーとなります。
置換文字にスペースなど別の文字を使用したい場合には、3行目の$alt変数の代入文で変更してください。

【使用例】

PS C:¥>  esc_nl data.csv data.esc.csv

TSV ⇒ CSV変換

TAB 区切りデータのファイルをカンマ（,）区切りに変換します。カンマを含むフィールド値に配慮します。

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

function tsv2csv([string]$tsvFile, [string]$csvFile) {
    # TSV を CSV に変換するフィルター
    filter _t2c {
        $flds = $_ -split "\t"
        $flds = $flds -replace '^( *)(?!")(.*?,.*?)( *)$','$1"$2"$3'
        $flds -join ","
    }

    # BOM なし UTF-8 で保存したいがために先頭行を書き分ける
    $first = Get-Content -First 1 -LiteralPath $tsvFile -Encoding UTF8 |
        _t2c |
        Out-String
    New-Item -Path $csvFile -Value $first > $null

    # 残りを追記
    Get-Content -LiteralPath $file -Encoding UTF8 |
        Select-Object -Skip 1 |
        _t2c |
        Out-File -FilePath $newFile -Encoding UTF8 -Append 
}

【制限事項】
このスクリプトでは次のようなデータが許される TSV 形式には対応しません。

フィールド内に TAB を含む

フィールド内に改行コードがあり、かつカンマが含まれる ⇒ （その２） をお試し下さい

これらのデータが含まれると、CSV 出力が壊される可能性がありますので注意してください。

【使い方】

tsv2csv TSVファイル名 CSVファイル名

ただしフィールド値自体に TAB が含まれると正常に処理できません
入力エンコードは UTF-8 （BOM 付き可）です
出力エンコードは BOM なし UTF-8 です
「CSVファイル名」がすでに存在する場合は上書きされずにエラーとなります。

【使用例】

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

列数を揃える

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

最初の引数で列数を指定してください。

function resize_csv([int]$num, [string]$file, [string]$newFile) {
    # 列を抽出するフィルター
    filter _resize {
        $flds = $_ -split ","
        [array]::resize([ref]$flds, $num)
        $flds -join ","
    }

    # BOM なし UTF-8 で保存したいがために先頭行を書き分ける
    $first = Get-Content -First 1 -LiteralPath $file -Encoding UTF8 |
        _resize |
        Out-String
    New-Item -Path $newFile -Value $first > $null

    # 残りを追記
    Get-Content -LiteralPath $file -Encoding UTF8 |
        Select-Object -Skip 1 |
        _resize |
        Out-File -FilePath $newFile -Encoding UTF8 -Append 
}

【制限事項】
このスクリプトは単純な CSV 形式のファイルのみに対応しており、以下ようなデータを扱えません。

フィールド値にカンマが含まれる

フィールド値に改行コードが含まれる

上記データを許す CSV では、不正な結果になったりデータが壊れたりする可能性があるのでご注意ください。

フィールド値にカンマや改行コードが含まれる CSV にも対応できるスクリプトは次の記事で紹介します。

【PowerShell】CSV の前処理の前処理をするスクリプト（その２）

【使い方】

resize_csv 列数 入力ファイル名 出力ファイル名

指定の「列数」よりカンマの少ない行では空のフィールドが追加されます
指定の「列数」より多い列を持つ行の余分なフィールドは削除されます
入力エンコードは UTF-8 （BOM 付き可）です
出力エンコードは BOM なし UTF-8 です
「出力ファイル名」がすでに存在する場合は上書きされずにエラーとなります。

【使用例】

PS > resize_csv 10 org.csv resized.csv

列の切り出し

CSV からの任意の列を切り出します。 Linux コマンドの cut -f のようなものです。

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

function cut_csv([int[]]$cols, [string]$file, [string]$newFile) {    
    # 列を抽出するフィルター
    filter _cut {($_ -split ",")[$cols] -join ","}    
    
    # BOM なし UTF-8 で保存したいがために初回を書き分ける
    $first = Get-Content -First 1 -LiteralPath $file -Encoding UTF8 |
        ForEach-Object { $_ | _cut } | Out-String
    New-Item -Path $newFile -Value $first > $null

    # 残りを追記
    Get-Content -LiteralPath $file -Encoding UTF8 |
        Select-Object -Skip 1 |
        _cut |
        Out-File -FilePath $newFile -Encoding UTF8 -Append 
}

【制限事項】
このスクリプトは単純な CSV 形式のファイルのみに対応しており、以下ようなデータを扱えません。

フィールド値にカンマが含まれる

フィールド値に改行コードが含まれる

上記データを許す CSV では、不正な結果になったりデータが壊れたりする可能性があるのでご注意ください。

フィールド値にカンマや改行コードが含まれる CSV にも対応できるスクリプトは次の記事で紹介します。

【PowerShell】CSV の前処理の前処理をするスクリプト（その２）

【使い方】

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 # 先頭に１列だけ追加したいときは , が必要

免責

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

www.shegolab.jp

参考資料

更新履歴

[2022/08/15] 投稿
[2022/08/29] その２へのリンクを追加
[2022/10/01] 誤記修正
- アドレスバー選択ショートカットは Ctrl+D ではなく Alt+D
- 一部、出力ファイルの上書き可になっていたするスクリプトを、説明に合わせて上書き不可に修正

シーゴの Excel 研究室

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

【PowerShell】CSV の前処理の前処理をするスクリプト（その１）

PowerShell でテキスト処理

CSV 前処理の前処理のスクリプト

基本的な使い方

BOM のチェック

BOM の除去

複数 CSV ファイルの連結

行内改行のエスケープ

TSV ⇒ CSV変換

列数を揃える

列の切り出し

免責

関連記事

参考資料

更新履歴