PowerShellでTSV／CSVの列を絞り込んで抽出し、（先頭／行末）からＮ行出力する

3月 14, 2021

目的

何十万行のCSVやTSVをPowerBI DesktopやSQL Server SSISで読み込むのですが、不要な列があったり、データ量が多いので、開発時はデータを少なくしたい。
Windows標準機能で実施したい。

やること

２つにわけて記載

Powershellで（TSV／CSV）ファイル（S-JIS）から必要な列のみを取り出して、（先頭／末尾）からN行とりだして、別な（TSV／CSV）ファイルへ出力
1について以下を実装

抽出するカラム指定をパラメータ化
区切り文字をパラメータ化
（先頭／末尾）とN行をパラメータ化
対象ファイルをスクリプトへドラッグドロップで実行

対象ファイルと同一フォルダ内へファイル名の末尾に「_Result」を追記して保存）

処理対象ファイル

今回のサンプルはTSVファイルは下図のような9列。

PersonID、adult name、profile_path、biography、homepage、birthday、deathday、place_of_birth

サンプル１の基本処理内容

1行目

import-csvで読み込む
-Encoding Default でS-JIS指定
-Delimiter "`t"　で区切り文字としてタブを指定
-header @(1..9)　でTSVのヘッダーとして1,2,3・・・,8,9を追加
select "1","3"　で列名（4で追加）が1、３を抽出
export-csv　で「TMDbPersons_Select.txt」に出力

2行目

get-content　で1で作成したTSVを参照
-totalcount　で先頭から10行抽出
select -skip 2　で先頭から2行をスキップ
foreach {$_.Replace('"','')}　で「"」を削除
out-file　で「TMDbPersons_Result.txt」に出力

サンプル１（列位置で指定する）

import-csv C:\Users\aaat\Desktop\TMDbPersons.txt -Encoding Default -Delimiter "`t" -header @(1..9) | select "1","3" | export-csv C:\Users\aaa\Desktop\TMDbPersons_Selected.txt
get-content -totalcount 10 C:\Users\aaa\Desktop\TMDbPersons_Selected.txt | select -skip 2 | foreach {$_.Replace('"','')} | out-file C:\Users\aaa\Desktop\TMDbPersons_Result.txt

サンプル２（列名で抽出）

import-csv C:\Users\utoat\Desktop\TMDbPersons.txt -Encoding Default -Delimiter "`t" | select "PersonID","name" | export-csv C:\Users\utoat\Desktop\TMDbPersons_Selected.txt
get-content -totalcount 10 C:\Users\utoat\Desktop\TMDbPersons_Selected.txt | select -skip 2 | foreach {$_.Replace('"','')} | out-file C:\Users\utoat\Desktop\TMDbPersons_Result.txt

結果確認

1行目で作成された「TMDbPersons_Select.txt」の先頭から35行のサンプル

※1行目にコメント行（#）が追加され、2行目に-headerで指定した1,2,3・・・8,9のヘッダーが追記されています（selectにより1,3列目だけに絞り込まれている。

#TYPE Selected.System.Management.Automation.PSCustomObject

"1","3"

"PersonID","name"

"0","NULL"

"1","George Lucas"

"2","Mark Hamill"

"3","Harrison Ford"

"4","Carrie Fisher"

"5","Peter Cushing"

"6","Anthony Daniels"

"7","Andrew Stanton"

"8","Lee Unkrich"

"10","Bob Peterson"

"13","Albert Brooks"

"18","Brad Garrett"

"19","Allison Janney"

"20","Elizabeth Perkins"

"22","Barry Humphries"

"24","Robert Zemeckis"

"26","Winston Groom"

"27","Eric Roth"

"28","Wendy Finerman"

"29","Steve Tisch"

"30","Steve Starkey"

"31","Tom Hanks"

"32","Robin Wright"

2行目で作成された「TMDbPersons_Result.txt」

PersonID,name

0,NULL

1,George Lucas

2,Mark Hamill

3,Harrison Ford

4,Carrie Fisher

5,Peter Cushing

6,Anthony Daniels

ドラッグ＆ドロップで入力元TSVと同一フォルダに書き出すサンプル

※get-contentのオプション（-totalcount／-last）について動的にできなかったので、Switchで切り替えることにしたのですが、良い方法があったら教えてください。

test.ps1

#パラメータここから--------------------------------------------------------------------------------------------
#読み込むファイルの列数を指定してください　（例）1..9→1,2・・・8,9のように9列分のヘッダーが先頭に追加されます
	$AddHederRange=1..9
#読み込むファイルの区切り文字を指定してください タブ→"`t"　カンマ→","
	$Delimiter="`t"
#抽出する列を選択してください　（例）1列目と3列目を選択する→"1","3"
	$SelectColumn="1","3"
#抽出する行を先頭にするか末尾にするから指定して下さい top→先頭　last→末尾
	$SelectRowMode="top"
#抽出する行数を指定してください
	$SelectRowNumber=10
#パラメータここまで--------------------------------------------------------------------------------------------

foreach ($arg in $args) {
	echo $arg "を"
	$ParentFolder=[System.IO.Path]::GetDirectoryName($arg)
	echo $ParentFolder "に"
	$FileName=[System.IO.Path]::GetFileName($arg)
	$FileNameWithoutExtension = [System.IO.Path]::GetFileNameWithoutExtension($arg)
	$Extension=[System.IO.Path]::GetExtension($arg)
	$SelectedFile=$ParentFolder + "\" + $FileNameWithoutExtension + "_Selected" + $Extension
	$ResultFile=$ParentFolder + "\" + $FileNameWithoutExtension + "_Result" + $Extension
	echo $ResultFile "として出力します。"

	import-csv $FileName -Encoding Default -Delimiter $Delimiter -header @($AddHederRange) | select $SelectColumn | export-csv $SelectedFile
	switch ($SelectRowMode)
	{
		"top" {get-content -totalcount $SelectRowNumber $SelectedFile | select -skip 2 | foreach {$_.Replace('"','')} | out-file $ResultFile}
		"last" {get-content -last $SelectRowNumber $SelectedFile | select -skip 2 | foreach {$_.Replace('"','')} | out-file $ResultFile}
		default {echo "SelectRowModeのパラメータが間違っています。"}
	}

	Remove-Item $SelectedFile
	Read-Host "続けるには Enter キーを押してください。"
}

ドラッグ＆ドロップで実行するための準備

test.ps1へのショートカットを作成し、そのショートカットのプロパティのリンク先に、ローカルスクリプト許可のポリシ―でPowershellから起動するように設定。以下の文字列をスクリプトファイルの手前に配置する。

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy RemoteSigned -File

このブログを検索

湯の如し