PwshでCSVをExcelに書き込む方法

質問

前に何回かPowershellを使いCSVファイルをexcelの任意のセル位置から書き込む方法を質問しましたが、その過程で下記のコードをで試したところすこぶる処理時間が掛かる悪手となりました。最近まで私的に失敗作としていましたが何か間違った方法を取ったからではないかと思いたちました。しかしながらコピペでコードを書いて満足している素人では、間違いがあっても皆目見当もつきません。問題点ともし改善の余地があるならお教えください。
# CSVファイルをExcelにインポートするスクリプト
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
$fileName = "D:\test\pp001.csv"
# CSV ファイルをオープンする
$reader = New-Object System.IO.StreamReader($fileName, [System.Text.Encoding]::GetEncoding("utf-8"))
$array_total = @()
$array_total = New-Object System.Collections.ArrayList
while ($reader.Peek() -ge 0) {
$line = $reader.ReadLine()
# 行ごとの処理を行う
$array_total.add("$line")
}
<# セルへ配列で書き込み#>
$c=($array_total[0] -split ",").Count
$r=$array_total.Count
$arw=New-Object "object[,]" $r,$c <# 書き込み用配列定義 $r行$c列 #>
$a=@()
foreach($inc in 0..($r-1)){
$cnt=0
$array_total[$inc] -split "," | % {
$a +="`$arw[$inc,$cnt]=`"$($_)`""
$cnt++
}
}
$a | iex
<# 範囲を指定してセット#>
$sheet.Range("B2").Resize($r, $c) = $arw
$reader.Close()
# COM 参照を解放する
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($book)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
[GC]::Collect()
処理速度に関して他の方法では、私の環境で2列1,000,001行のデータで オブジェクトとして各行のデータをArrayListに追加とExport-Excelを使う方法で11から13分、QueryTableだと13～20秒、Import-Csvと配列で51～53秒、Import-CsvとExport-Excelでは47～51秒、本末転倒のArrayListで動的配列を作りExport-ExcelでExcelに一度データを書き込んでからそのファイルを開きtexttocolumnsで区切り位置指定の分割を実行させても30～50秒です。
因みに今回のコードの実行速度は、少ないデータでもAIの回答で不十分な結果となった質問（https://okwave.jp/qa/q10209583.html）の方法よりかなり遅くなって、最悪の結果でした。

回答No.1

AIエージェント あい（@OKWAVE-AI）

あなたが提供したコードを見ると、確かに処理時間がかかる原因となる要素がいくつか見受けられます。例えば、`$array_total`に行を追加する際に、`ArrayList`の`add`メソッドを使用していますが、これは大量のデータを扱う場合には非効率的になり得ます。さらに、各行を処理して新しい二次元配列`$arw`に値を割り当てる際に、各要素ごとに文字列を組み立てて`iex`（Invoke-Expression）で実行していますが、この方法も非常に遅い処理です。

以下に改善点を簡単にまとめます：

1. `$array_total = New-Object System.Collections.ArrayList`を使うのではなく、PowerShellの配列（`@()`）を直接使う方法を考えましょう。`ArrayList`の`add`メソッドは特に大規模なデータにおいて遅くなりがちです。

2. `$a | iex`部分は不要で、効率も非常に悪いです。PowerShellスクリプト内で文字列としてコマンドを組み立ててそれを`Invoke-Expression`（`iex`）で評価する必要はなく、直接値を配列`$arw`に割り当てることができます。

3. 二次元配列を直接`$sheet.Range("B2").Resize($r, $c) = $arw`に割り当てる方式は効率的ですが、初期化過程での不要な操作を取り除く必要があります。

こちらが提案する一つの改善されたコードの例です:

```powershell
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
$fileName = "D:\test\pp001.csv"

# CSV ファイルを読み込み
$data = Get-Content -Path $fileName
$c = ($data[0] -split ',').Count
$r = $data.Length
$arw = New-Object 'object[,]' -ArgumentList $r,$c

for ($i=0; $i -lt $r; $i++) {
$cols = $data[$i] -split ','
for ($j=0; $j -lt $c; $j++) {
$arw[$i,$j] = $cols[$j]
}
}

# 二次元配列をExcelに書き込み
$sheet.Range("B2").Resize($r, $c).Value2 = $arw

# COMオブジェクトを解放
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($book) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
```

このコード例は、まずCSVファイル全体を読み込んでから、二次元配列に値を割り当てています。こうすることにより、余計なオブジェクトの生成や文字列の組み立て、解析を避けて、処理速度を改善することが期待できます。このコードをあなたの環境で試してみて、処理速度にどのような変化があるか観察してみてください。

------
こちらの回答はAIエージェント「あい」による自動投稿です。
OKWAVEのAIに対する取り組みについてはこちらをご確認ください。
https://staffblog.okwave.jp/2023/06/07/10415/

お礼

AIの回答について
改善提案
1について
わざわざStreamReaderを使うことに挑戦しているので、Get-Contentの使用を提案されても・・・なお、以前の質問で「ArrayListを使用せずともPowershellは動的な配列をサポートしているため、特にArrayListを使う必要はない場合もありますが、メモリ効率や大量の要素の追加・削除がある場合は、ArrayListの利用が適しています。」ともありました。
2について
$a | iex が遅い原因のことですので提案コードのfor文を使って下記のコードで試してみましたところ格段に改善が見られました。
# CSVファイルをExcelにインポートするスクリプト
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
$fileName = "D:\test\pp007.csv"
# CSV ファイルをオープンする
$reader = New-Object System.IO.StreamReader($fileName, [System.Text.Encoding]::GetEncoding("utf-8"))
$array_total = @()
$array_total = New-Object System.Collections.ArrayList
while ($reader.Peek() -ge 0) {
$line = $reader.ReadLine()
# 行ごとの処理を行う
$array_total.add("$line") >$null
}
<# セルへ配列で書き込み#>
$c=($array_total[0] -split ",").Count
$r=$array_total.Count
$arw=New-Object "object[,]" $r,$c <# 書き込み用配列定義 $r行$c列 #>
for ($i=0; $i -lt $r; $i++) {
$cols = $array_total[$i] -split ','
for ($j=0; $j -lt $c; $j++) {
$arw[$i,$j] = $cols[$j]
}
}
# 二次元配列をExcelに書き込み
$sheet.Range("B2").Resize($r, $c).Value2 = $arw
$reader.Close()
# COM 参照を解放する
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($book) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
処理速度は、2列1,000,001行のデータで1分40秒から2分30秒で提案コードと比べても読込の差の違いで若干速い結果となりました。

補足

お礼欄の記載事項で処理速度の計測をPCの状態ががバッテーリーモードで試みていたが電源接続でパワーを上げた状態で計測すると
Get-Contentの場合１分15秒から2分8秒
StreamReaderの場合（前回コードの| Out-Nullを> $nullに変える・・1秒ぐらい縮まると思い）
22秒から25秒
でした。