複数csvファイルをexcelの各シートに書き込む

質問

複数のCSVファイルを各々一つのexcelファイルの各ワークシトに書き込む方法のコードを下記のようにQueryTableを使った形にしてみました。
$csvFile1 = "D:\test_data\test_data1.csv"
$csvFile2 = "D:\test_data\test_data2.csv"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$Book.Worksheets.Add()
$sheet1 = $excel.Worksheets.Item(2)
$sheet2 = $excel.Worksheets.Item(1)

$startRange = "C2"
$outputColumn = $sheet1.Range($startRange).Column
$outputRow = $sheet1.Range($startRange).Row
$QueryTable = $sheet1.QueryTables.Add("TEXT;$csvFile1",$sheet1.cells($outputRow,$outputColumn))
$QueryTable.TextFileCommaDelimiter = $True
$QueryTable.TextFilePlatform = 65001
$QueryTable.TextFileStartRow = 1
$QueryTable.Refresh($false)
$QueryTable.Name = "仮テーブル"
$QueryTable.Delete()

$startRange = "C2"
$outputColumn = $sheet2.Range($startRange).Column
$outputRow = $sheet2.Range($startRange).Row
$QueryTable = $sheet2.QueryTables.Add("TEXT;$csvFile2",$sheet2.cells($outputRow,$outputColumn))
$QueryTable.TextFileCommaDelimiter = $True
$QueryTable.TextFilePlatform = 65001
$QueryTable.TextFileStartRow = 1
$QueryTable.Refresh($false)
$QueryTable.Name = "仮テーブル"
$QueryTable.Delete()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) > $null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) > $null
[gc]::Collect()
これは2個のcsvファイルだから何とかなっていますがファイルが増えた場合これではとても面倒なことになります。私の能力ではコンパクトにコードを纏めることが出来ません。コンパクトな方法をお教えください。別にExport-Excel等使う方法でもかまいません。

回答No.2

luka3 · ベストアンサー

こんな感じになるかと思います。

$csvFiles = @("D:\test_data\test_data1.csv", "D:\test_data\test_data2.csv")

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()

foreach ($index in 0..($csvFiles.Length - 1)) {
# シートを追加（最初のシートは既に存在するので、2番目以降は追加する）
if ($index -eq 0) {
$sheet = $book.Sheets.Item(1)
} else {
$sheet = $book.Sheets.Add()
}
$sheet.Name = "Sheet" + ($index + 1)
$startRange = "C2"
$QueryTable = $sheet.QueryTables.Add("TEXT;"+$csvFiles[$index], $sheet.Range($startRange))
$QueryTable.TextFileCommaDelimiter = $True
$QueryTable.TextFilePlatform = 65001
$QueryTable.TextFileStartRow = 1
$QueryTable.Refresh($false)
$QueryTable.Name = "仮テーブル"
$QueryTable.Delete()
}

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) > $null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($book) > $null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) > $null
Remove-Variable sheet, book, excel
[gc]::Collect()
[GC]::WaitForPendingFinalizers()

お礼

コンパクトなコードにしていただき有り難うございました。
170万行のデータを2つのCSVファイルに分けて吐き出させ（私が取った方法では一つのファイルからの分割に処理時間を要するため）、一つのexcelファイルに納めるために、最初は、Export-Excelで試したのですが、Import-Csvで一度読み込ませるためどうしても私の環境で80秒弱掛かっていました。そこでコード数は増えるもののQueryTableを使ってみたところ40秒強で出来た迄は良かったが、あのようなコードしか書けないが情けなく思っていたところです。本当に助かりました。

回答No.1

type0（@type0）

folderPath = "ここにフォルダのパスを入力"
↓で一応は意図した通りに動いてそうでした。↑はCSVファイルを置いてるパスに書き換えてください。桁落ちなどもあるかは分かりません。

ちなみに、chatGptに
「複数のCSVをEXCELの各シートに取り込むVBAを書いて」
と命令し、出力されたコードなので、私は書いてません。上手く活用すれば楽になります。

Sub ImportCSVFilesToSheets()
Dim folderPath As String
Dim fileName As String
Dim ws As Worksheet
Dim wb As Workbook
Dim fPath As String

'フォルダのパスを取得
folderPath = "ここにフォルダのパスを入力"

'Excelファイルを作成
Set wb = ThisWorkbook

'指定したフォルダ内のすべてのファイルについてループ処理
fileName = Dir(folderPath & "\*.csv")
Do While fileName <> ""
'ファイルを開く
Set ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
fPath = folderPath & "\" & fileName
With ws.QueryTables.Add(Connection:="TEXT;" & fPath, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True 'CSVファイルの区切り文字を設定
.Refresh
End With
ws.Name = Left(fileName, InStr(fileName, ".") - 1) 'シート名をファイル名に設定
fileName = Dir '次のファイルに移動
Loop
End Sub

お礼

外部からコマンドラインで実行させるためとマクロファイルを作りたくないためVBAを使うことはためらっていました。VBSで実行させるときは参考にさせていただきます。

Powered by GRATICA