PowershellでCSVをexcelに書き込む

質問

Powershellを使いCSVファイルをexcelの任意のセル位置から書き込むためにQueryTablesを使わない方法として下記コードをとあるサイトを見て継ぎ接ぎで作りましたがデータ量が多くなると処理時間を要します。
「QueryTablesよりStreamReaderのほうが
大容量（100MB以上）のテキストファイルを処理する場合、Get-ContentやImport-Csvなどのコマンドレットではなく、StreamReaderを使用することが推奨されています。なぜなら、PowerShellのパイプ処理は遅く、大量のメモリを消費するため、効率的な処理ができないからです。
StreamReaderは.NET Frameworkのオブジェクトを生成して使用するため、コードの記述量が増えるかもしれませんが、大容量ファイルの読み込みには最適な手法です。」
とありより良い方法と思いましたが私のコードのの書き方かどうか分かりませんが期待外れでした。因みに2列、1,000,001行でもQueryTablesなら15秒ぐらいですがこれだと10,001行で50秒位掛かりる状況でした。
# 通常のCSVファイルをExcelにインポートするスクリプト
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
$csvPath = "D:\test\pp004.csv"
# CSV ファイルのエンコーディングを指定する
$enc = [System.Text.Encoding]::UTF8
# CSV ファイルをオープンする
$streamReader = New-Object -TypeName System.IO.StreamReader $csvPath, $enc
# 変数を初期化する・・書き出し位置の指定
$i = 2
$j = 3
# １行ずつ最終レコードまで読み込む
While (($line = $streamReader.ReadLine()) -ne $null) {
# カンマで文字列を分割し配列に格納する
$fields = $line.Split(",")
# 配列を順番に処理する
foreach ($field in $fields) {
# セルの書式を「文字列」にする
#$sheet.Cells.Item($i, $j).NumberFormat = "@"
# セルに値を設定する
$sheet.Cells.Item($i, $j).Value = $field
# 列を１つ進める
$j++
}
# 行を１つ進める
$i++
# 変数初期化
$j = 3
}
# CSV ファイルを閉じる
$streamReader.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()

もしこのコードに問題があればお教えください。
よろしくお願いします。

回答No.1

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

回答できます。

このコードはCSVファイルをExcelにインポートするためのスクリプトですが、データ量が多い場合に処理時間がかかるとのことですね。

以下の点について、コードの改善案をご提案いたします。

1. Excelのオートフィルタを無効化する
大量のデータを書き込む場合、Excelのオートフィルタが有効になっていると処理が遅くなることがあります。書き込み前に以下のコードを追加し、オートフィルタを無効化してみてください。

```powershell
$sheet.AutoFilterMode = $false
```

2. 再計算の自動更新を無効化する
Excelはデータを書き込むと自動的に再計算を行いますが、大量のデータを処理する場合は再計算に時間がかかることがあります。以下のコードを追加して、再計算の自動更新を無効にしてみてください。

```powershell
$excel.Calculation = [Microsoft.Office.Interop.Excel.XlCalculation]::xlCalculationManual
```

3. データの書き込み範囲を指定する
現在のコードでは、すべてのデータを順番に書き込んでいますが、データの量が増えると書き込み時間がかかることがあります。データの書き込み範囲を指定することで、処理時間を短縮できます。以下のコードを追加して、データの書き込み範囲を指定してみてください。

```powershell
$dataRange = $sheet.Range("A2").Resize($i-2, $j-3)
$dataRange.Value = $data
```

また、コード内の`$enc = [System.Text.Encoding]::UTF8`という行ですが、CSVファイルのエンコーディングがUTF-8である場合はそのままで問題ありません。エンコーディングが違う場合は、`$enc`の値を適切なエンコーディングに変更してください。

以上がコードの改善案です。試してみてください。もし他に何か質問があればお気軽にどうぞ。

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

補足

$sheet.AutoFilterMode = $false
の場合パフォーマンスに変化無し
$excel.Calculation = [Microsoft.Office.Interop.Excel.XlCalculation]::xlCalculationManual
エラーが出てPC自体の動きがおかしくなった。再起動の時間が長く続いたので電源を落とし強制終了させた。
私のやり方が悪いのかどうか分からないが・・・・・