****************************************************
powershell -ExecutionPolicy Bypass -File "列結合.ps1"
（list.txtに記載したファイル名を対象に列結合する）
****************************************************
$scriptPath = Split-Path -Parent $MyInvocation.MyCommand.Path
$listPath = Join-Path $scriptPath "list.txt"
if (-not (Test-Path $listPath)) {
Write-Error "Error: list.txt not found."
exit
}
$csvLines = Get-Content -LiteralPath $listPath
$csvFiles = @()
foreach ($line in $csvLines) {
if (-not [string]::IsNullOrWhiteSpace($line)) {
$cleanLine = $line.Trim()
$fullPath = Join-Path $scriptPath $cleanLine
if (Test-Path $fullPath) {
$csvFiles += Get-Item -LiteralPath $fullPath
}
}
}
if ($csvFiles.Count -lt 2) {
Write-Error "Error: Needs 2 or more valid CSV files from list.txt."
exit
}
$headers = 0..99 | ForEach-Object { "H$_" }
$firstCsv = Import-Csv -LiteralPath $csvFiles[0].FullName -Header $headers
$baseCsv = @()
$firstFileName = [string]$csvFiles[0].BaseName
foreach ($row in $firstCsv) {
$properties = [ordered]@{
"Col1" = $row.H0
$firstFileName = $row.H2
}
$baseCsv += New-Object PSObject -Property $properties
}
for ($fileIdx = 1; $fileIdx -lt $csvFiles.Count; $fileIdx++) {
$nextCsv = Import-Csv -LiteralPath $csvFiles[$fileIdx].FullName -Header $headers
$nextFileName = [string]$csvFiles[$fileIdx].BaseName
for ($rowIdx = 0; $rowIdx -lt $baseCsv.Count; $rowIdx++) {
$nextValue = $nextCsv[$rowIdx].H2
$baseCsv[$rowIdx] | Add-Member -MemberType NoteProperty -Name $nextFileName -Value $nextValue -Force
}
}
$tempCsv = "$scriptPath\temp_combined.csv"
$baseCsv | Export-Csv -LiteralPath $tempCsv -NoTypeInformation -Encoding utf8
$currentDate = Get-Date -Format "yyyyMMdd"
$excelFileName = "OUTPUT_${firstFileName}_${currentDate}.xls"
$excelPath = Join-Path $scriptPath $excelFileName
try {
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$workbook = $excel.Workbooks.Open($tempCsv)
$worksheet = $workbook.Sheets.Item(1)
$worksheet.UsedRange.Columns.AutoFit() | Out-Null
$workbook.SaveAs($excelPath, 56)
$workbook.Close()
}
finally {
if ($excel) {
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel -ErrorAction SilentlyContinue
}
if (Test-Path $tempCsv) { Remove-Item $tempCsv }
}
PR
