はじめに

CSV ファイルは広く使われていますが、自社内で閉じているシステムなどでは問題になりにくいですが、外部システムと連携している場合などはデータの中にカンマや改行が入ってしまうと単純な CSV ファイルの扱いでは問題が起きてしまいます。

実は、CSV ファイルを扱うというのは、非常にやっかいなことなのです。

この記事では、PowerShell でこのやっかいな CSV ファイルを Excel にインポートする方法をご紹介します。

CSV ファイルをカンマで文字列分割し Excel にインポートする

まずは、以下のような単純な CSV ファイルを読み込み後、カンマで文字列を分割して Excel にインポートする方法のご紹介です。

001,山田太郎
002,佐藤花子
003,鈴木次郎

この CSV ファイルは UTF-8 で保存されているので、PowerShell のコマンドレットでは扱うことができません。ではどのようにすればよいかというと、.NET Framework の StreamReader を使用します。

.NET Framework のライブラリを自由に使える PowerShell ならではの解決方法ですね。PowerShell すばらしい！

また、文字列の分割には String の Split メソッドを使用します。

Excel ファイルのセル書式設定はデータの前ゼロが消えてしまうため「文字列」に設定します。ファイルの保存形式は、xlsx 形式になります。コメントに記述しましたが、xls 形式に保存するにはファイル保存時の処理でパラメータを指定する必要があります。

あと、インポートする CSV ファイルと、作成する Excel ファイルは PowerShell のスクリプトファイルと同じフォルダパスになるようになっています。

CSV ファイルを扱うソースコードは以下のようになります。

#
# 通常のCSVファイルをExcelにインポートするスクリプト
#

# スクリプトの親フォルダのパスを取得する
$path = Split-Path $MyInvocation.MyCommand.Path -Parent

# CSV ファイルのパスを作成する
$csvPath = Join-Path $path "users.csv"

# Excel の保存先のパスを作成する
$xlsPath = Join-Path $path "users.xlsx"

# CSV ファイルのエンコーディングを指定する
$enc = [System.Text.Encoding]::UTF8

# CSV ファイルをオープンする
$streamReader = New-Object -TypeName System.IO.StreamReader $csvPath, $enc

# Excel を起動する
$xls = New-Object -ComObject Excel.Application

# WorkBook を追加する
$wb = $xls.WorkBooks.Add()

# シートを選択する
$ws = $wb.WorkSheets.Item("Sheet1")

# 変数を初期化する
$i = 1
$j = 1

# １行ずつ最終レコードまで読み込む
While (($line = $streamReader.ReadLine()) -ne $null) {

  # カンマで文字列を分割し配列に格納する
  $fields = $line.Split(",")

   # 配列を順番に処理する
   foreach ($field in $fields) {
 
    # セルの書式を「文字列」にする
    $ws.Cells.Item($i, $j).NumberFormat = "@"

    # セルに値を設定する
    $ws.Cells.Item($i, $j).Value = $field

    # 列を１つ進める
    $j++
  }

  # 行を１つ進める
  $i++

  # 変数初期化
  $j = 1
}

# ファイルが既存の場合警告メッセージを表示しないようにする
$xls.DisplayAlerts = $false

# Excel ファイルを保存する
$wb.SaveAs([ref]$xlsPath.ToString())
#$wb.SaveAs([ref]$xlsPath.ToString(), -4143) # xls形式の時はこちらを使用する

# 警告メッセージの表示を元に戻す
$xls.DisplayAlerts = $true

# WorkBook を閉じる
$wb.Close()

# Excel を終了する
$xls.Quit()

# CSV ファイルを閉じる
$streamReader.Close()

# COM 参照を解放する
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($ws)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($xls)

このスクリプトを実行して作成された Excel ファイルを表示すると、以下のように問題ないことが分かります。

f:id:fnyablog:20180908161635j:plain

では、以下のようなやっかいな CSV ファイルを読み込んでみましょう。データ内にカンマや改行が入っていますね。

"001","山田,太郎"
"002","佐藤,花子"
"003","鈴木,
次郎"

この CSV ファイルを読み込むと以下のように処理がうまくいきません。

f:id:fnyablog:20180908161748j:plain

では、どのようにすればうまくいくのか。

それを次にご紹介します。

やっかいな CSV ファイルをカンマで文字列分割し Excel にインポートする

このやっかいな CSV ファイルを PowerShell で読み込むためには、TextFieldParser というパーサーを使います。

TextFieldParser は .NET Framework のライブラリなのですが、標準では参照設定に組み込まれていないため、スクリプトの最初で参照設定に追加する必要があります（実は、Visual Studio と同じ）。

では、実際のコードを見てみましょう。

#
# やっかいなCSVファイルをExcelにインポートするスクリプト
#

# アセンブリの参照設定を追加する
[void][reflection.assembly]::LoadWithPartialName("Microsoft.VisualBasic")

# スクリプトの親フォルダのパスを取得する
$path = Split-Path $MyInvocation.MyCommand.Path -Parent

# CSV ファイルのパスを作成する
$csvPath = Join-Path $path "users2.csv"

# Excel の保存先のパスを作成する
$xlsPath = Join-Path $path "users.xlsx"

# CSV ファイルのエンコーディングを指定する
$enc = [System.Text.Encoding]::UTF8

# パーサーで CSV ファイルを開く
$parser = New-Object -TypeName Microsoft.VisualBasic.FileIO.TextFieldParser $csvPath, $enc

# CSV ファイルが可変長であることを指定する
$parser.TextFieldType = [Microsoft.VisualBasic.FileIO.FieldType]::Delimited

# CSV ファイルがカンマ区切りであることを指定する
$parser.SetDelimiters(","); 

# Excel を起動する
$xls = New-Object -ComObject Excel.Application

# WorkBook を追加する
$wb = $xls.WorkBooks.Add()

# シートを選択する
$ws = $wb.WorkSheets.Item("Sheet1")

# 変数を初期化する
$i = 1
$j = 1

# 最終レコードまで読み込む
While($parser.EndOfData -eq $false) {
  # カンマでテキストを区切り配列に格納する
  $fields = $parser.ReadFields()

  # 配列を順番に処理する
  foreach ($field in $fields) {

    # セルの書式を「文字列」にする
    $ws.Cells.Item($i, $j).NumberFormat = "@"

   # セルに値を設定する
   $ws.Cells.Item($i, $j).Value = $field

   # 列を１つ進める
   $j++
  }

  # 行を１つ進める
  $i++

  # 変数初期化
  $j = 1
}

# ファイルが既存の場合警告メッセージを表示しないようにする
$xls.DisplayAlerts = $false

# Excel ファイルを保存する
$wb.SaveAs([ref]$xlsPath.ToString())
#$wb.SaveAs([ref]$xlsPath.ToString(), -4143) # xls形式の時はこちらを使用する

# 警告メッセージの表示を元に戻す
$xls.DisplayAlerts = $true

# WorkBook を閉じる
$wb.Close()

# Excel を終了する
$xls.Quit()

# CSV ファイルを閉じる
$parser.Close()

# COM 参照を解放する
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($ws)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($xls)