【PowerShell】複数のCSVファイルを1つのExcelファイルに纏める

コード

ここでは例として、
　・デスクトップ配下のフォルダ「test」配下にある
　・全てのCSVファイルを
　・1つのExcelファイルへ纏める方法
を紹介します。

# 対象フォルダを指定
$targetFolder = "C:\Users\user\Desktop\test"
# 出力するExcelファイルのパス
$outputExcelFile = "C:\Users\user\Desktop\mergeCsv.xlsx"
# シート名
$loadSheetName  = "csv"
# 書き出し開始セル
$startRange = "B2"
# テーブル名を指定
$tableName = "テストテーブル"
# 1ファイル目かどうかを判定するためのカウンタ変数
$fileCount = 0

$ws = New-Object -ComObject Wscript.Shell

try
{
    # 既にExcelファイルが存在したら削除
    if (Test-Path $outputExcelFile) {
        Remove-item -Force $outputExcelFile -ErrorAction Stop
    }

    # ExcelのCOMコンポーネントを取得
    $excel = New-Object -ComObject Excel.Application

    # ブックを新規作成しオブジェクトを取得
    $book = $excel.Workbooks.Add()
    # ブックの1シート目のオブジェクトを取得
    $sheet = $book.Worksheets.Item(1)
    # シート名を変更
    $sheet.name = $loadSheetName 
    # 処理したファイル数を示すカウンタ変数
    $fileCount = 0
    # 出力列(書き出し開始セルの列)
    $outputColumn = $sheet.Range($startRange).Column
    # 出力行(書き出し開始セルの行)
    $outputRow = $sheet.Range($startRange).Row

    # 読み込むファイルの形式を【2:文字列】と指定するための配列を作成
    $arrDataType = @()
    for ($i=0; $i -lt 255; $i++){
        $arrDataType += 2
    }

    # 指定フォルダ配下のcsvファイルのリストを取得
    $fileList = (Get-ChildItem $targetFolder -Filter *.csv -File -ErrorAction Stop).FullName

    # csvファイルの数だけ繰り返し
    foreach($file in $fileList){
        # カウンタ変数をカウントアップ
        $fileCount = $fileCount + 1
        # 「QueryTableオブジェクト(＝クエリと接続)」を作成
        $QueryTable = $sheet.QueryTables.Add("TEXT;$file",$sheet.cells($outputRow,$outputColumn))
        # 区切り文字に「カンマ区切り」を指定
        $QueryTable.TextFileCommaDelimiter = $True
        # 文字コードに「Shift_JIS」を指定
        $QueryTable.TextFilePlatform = 932
        # 1ファイル目のみ、(1行目を含め)まるっと出力
        # 1ファイル目以外は、1行目を飛ばして1行ずつ出力
        if ($fileCount -eq 1) {  
            $QueryTable.TextFileStartRow = 1
        } else {
            $QueryTable.TextFileStartRow = 2
        }
        # 読み込むファイルの形式を指定
        $QueryTable.TextFileColumnDataTypes = $arrDataType[0..255]
        # 読み込み実行
        $QueryTable.Refresh($false)
        # 名前を指定(後続処理で削除できるようにするため)
        $QueryTable.Name = "仮テーブル"
        # 作成された「QueryTableオブジェクト(＝クエリと接続)」を削除
        $QueryTable.Delete()
        # 次の出力行を取得
        $outputRow = $sheet.Cells($sheet.Rows.Count, $outputColumn).End(-4162).Row + 1
    }

    # 上記で作成されてしまう名前定義(仮テーブル)を削除
    foreach($n in $book.Names){
        If ($n.Name -Like $loadSheetName + "!" + "仮テーブル*") {
            $n.Delete()
        }
    }

    # 表をテーブル化
    $sheet.ListObjects.Add(1,$sheet.Range($startRange).CurrentRegion,0,1).Name = $tableName
    # ブックを保存
    $book.SaveAs($outputExcelFile)

    $ws.popup("複数のCSVファイルをマージして`nExcelファイルを作成しました。`n対象ファイル数：" + $fileCount)
}
catch
{
    $ws.popup("エラー : " + $PSItem)
}
finally
{
    # Excelを終了
    $excel.Quit()
    [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null
}

実行結果

複数のCSVファイルを1つのExcelファイルに纏めることができました。

参考

上記のコードは以下の記事を参考にして作成しました。

●CSVファイルをExcelファイルにする