CSVファイルを使っていると様々なトラブルに会います。Pythonとか使えば簡単に対処できるかもれませんが端末に必ずしも入っているとは限りません。しかし、Windows端末ならPowerShellは必ず入っています。そのためPowerShellを使ってデータをきれいにする方法を調べてみました。
トラブル1：文字の中にダブルクォーテーション"が入っている
下の例のように文字の中に半角のダブルクォーテーションが入っていると、CSVファイルとしてうまく読み取ることができません。
"番号","本の名称","値段","備考"
"0123","Pythonの"基礎"文法","2000",""
対策1：文字をダブルクォーテーション"で囲まない
そもそも文字が"で囲まれているため文字中に"が使えないので、文字を囲んでいる"を消せば使えるとの考えです。ただし、単に文字を囲んでいる"を消すと別の問題（トラブル3）が発生しますので、区切り文字を,からタブ\tに変換もします。手順は下のとおりです。
1.","をタブ\tに変換します。
2.文頭の"と文末の"を消します。
なお、本文中にタブが使われていないことと、データがない場合でもダブルクォーテーションで囲まれていることが必要です。
$OutputEncoding = [ System.Text.Encoding]::UTF8
$Input | foreach { $_ -replace '","',"`t" -replace '^"|"$','' }
> Get-Content test1.csv -Encoding UTF8 | .\conv1-1.ps1
番号 本の名称 値段 備考
0123 Pythonの"基礎"文法 2000
対策2：ダブルクォーテーションを二重化する
CSV形式でダブルクォーテーションを表現する時はダブルクォーテーションを2つ並べる（二重化する）のが正しい書き方らしい。二重化への変換手順は下のとおりです。
1.","をタブ\tに変換します。
2.文頭の"と文末の"を消します。
3.残った"を""に全て変換します。
4.タブ\tを","に戻します。
5.文頭と文末に"を追加します。
なお、本文中にタブが使われていないことと、データがない場合でもダブルクォーテーションで囲まれていることが必要です。
$OutputEncoding = [ System.Text.Encoding]::UTF8
$Input | foreach { '"'+($_ -replace '","',"`t" -replace '^"|"$','' -replace '"','""' -replace "`t",'","')+'"' }
> Get-Content .\test.csv -Encoding UTF8 | .\conv1-2.ps1
"番号","本の名称","値段","備考"
"0123","Pythonの""基礎""文法","2000",""
※元から二重化されている場合は、更に二重化されるので気をつけてください。
ドラブル2：文字の中に改行コード\r\nが入っている
ダブルクォーテーションがない形式の場合、文字の中に改行コードが入っているとうまく取り込むことができません。
番号,本の名称,値段,備考
B001,PowerShellの初歩,1000,
B002,PowerShellの基本
～文法編～,2000,
B003,PowerShellの応用
～サーバ編～,3000,
対策：次の行と結合する
1行毎のカンマの個数を調べ、足りなければ次の行や更にその次の行と結合するという考えです。改行を消してそのままくっつけてしまうと文字がくっついてしまうので、改行コードをスペースに変換しています。
$OutputEncoding = [ System.Text.Encoding]::UTF8
$c = $null
$Input | foreach {
if ($c -eq $null) { $number = $_.Length - ($_ -replace ',','').Length; $c = '' }
$c = "$c $_"
if (($c.Length - ($c -replace ',','').Length) -ge $number) { $c.Trim(); $c = '' } }
if ($c -ne '') { $c.Trim() } #最終行でカンマが足りない場合もあるので、念の為入れておきます
t> Get-Content .\test2.csv -Encoding UTF8 | .\conv2.ps1
番号,本の名称,値段,備考
B001,PowerShellの初歩,1000,
B002,PowerShellの基本 ～文法編～,2000,
B003,PowerShellの応用 ～サーバ編～,3000,
ドラブル3：文字の中にカンマ,が入っている
ダブルクォーテーションがない形式の場合、文字の中にカンマが入っていてもうまく取り込むことができません。
番号,本の名称,値段,備考
B001,PowerShellの初歩,1000,
B002,PowerShellの基本,文法編,2000,
B003,PowerShellの応用,サーバ編,3000,
対策
カンマが何番目の文字に入っているものなのかを自動的に調べるのは極めて困難です。ここではカンマが入る可能性がある文字を「本の名称」だけと決めます。
対策1：区切り文字にカンマ,を使わない。
区切り記号のカンマと文字の中のカンマが同じなので、区切り記号のカンマをタブに変えれば良いとの考えです。
Param($Index)
$OutputEncoding = [ System.Text.Encoding]::UTF8
$c = $null
$Index--
$Input | foreach {
if ($c -eq $null) { $number = $_.Length - ($_ -replace ',','').Length; $c = '' }
$l = 0; for($i = 0; $i -lt $Index; $i++){ $l = $_.IndexOf(',',$l)+1 }
$r = $_.length; for($i = 0; $i -lt ($number - $Index); $i++){ $r = $_.LastIndexOf(',',$r)-1 }
($_.substring(0,$l) -replace ',',"`t")+$_.substring($l,$r-$l+1)+($_.substring($r+1,$_.length-$r-1) -replace ',',"`t") }
> Get-Content test3.csv -Encoding UTF8 | .\conv3-1.ps1 2
番号 本の名称 値段 備考
B001 PowerShellの初歩 1000
B002 PowerShellの基本,文法編 2000
B003 PowerShellの応用,サーバ編 3000
対策2：ダブルクォーテーションで囲む
全ての文字の前後をダブルクォーテーションで囲むやり方です。なお、これだけだと別の問題（トラブル1）を引き起こす可能性があります。
Param($Index)
$OutputEncoding = [ System.Text.Encoding]::UTF8
$c = $null
$Index--
$Input | foreach {
if ($c -eq $null) { $number = $_.Length - ($_ -replace ',','').Length; $c = '' }
$l = 0; for($i = 0; $i -lt $Index; $i++){ $l = $_.IndexOf(',',$l)+1 }
$r = $_.length; for($i = 0; $i -lt ($number - $Index); $i++){ $r = $_.LastIndexOf(',',$r)-1 }
'"'+($_.substring(0,$l) -replace ',','","')+$_.substring($l,$r-$l+1)+($_.substring($r+1,$_.length-$r-1) -replace ',','","')+'"' }
> Get-Content test3.csv -Encoding UTF8 | .\conv3-2.ps1 2
"番号","本の名称","値段","備考"
"B001","PowerShellの初歩","1000",""
"B002","PowerShellの基本,文法編","2000",""
"B003","PowerShellの応用,サーバ編","3000",""
まとめると
パイプでつなげれば上に書かれた複数のトラブルに対応できます。スクリプトの実行順番には気をつけましょう。
番号,本の名称,値段,備考
C001,PowerShellとは？,1000,
C002,PowerShellの"基礎"文法,2000,
C003,PowerShellの基本
～文法編～,3000,
C004,PowerShellの応用,サーバ編,4000,
C005,PowerShellの未来,5000,
> Get-Content test4.csv -Encoding UTF8 | .\conv2.ps1 | .\conv3-2.ps1 2 | .\conv1-2.ps1
"番号","本の名称","値段","備考"
"C001","PowerShellとは？","1000",""
"C002","PowerShellの""基礎""文法","2000",""
"C003","PowerShellの基本 ～文法編～","3000",""
"C004","PowerShellの応用,サーバ編","4000",""
"C005","PowerShellの未来","5000",""