你好，我有一个 PS 脚本，当它到达具有重复标题的工作表时，会出现此错误。
在行“1”的列“12 53”中发现重复的列标题。列标题必须是唯一的，如果这不是必需的，请使用“-NoHeader”或“-HeaderName”参数。
导致错误的重复标题不是我的 $expectcolumns 部分中列出的标题。我该怎么做才能更新我的脚本以忽略重复标题但仍引入数据？目前它完全忽略了工作表。
Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear();
# Specify the minimum creation date for files to be processed
$minCreationDate = Get-Date "2022-01-01"
# This folder count will determine which folder within the rootfolder the process starts at.
# Edit this if you want to continue from a specific location
$folderCount = 0
# Import the Import-Excel module
Import-Module ImportExcel
# Specify the root folder where your Excel files are located
$rootFolder = "File Path"
# Specify the output file path for the merged data
$outputFolderPath = "File Path"
# Define the sheet names using variables
$Final_PaymentHW = "Final_Payment HW"
$sheetNames = @(
$Final_PaymentHW,
"Final_Pension",
"Final_Other",
"Final_Correction1",
"Final_Correction2",
"Final_Correction3",
"Final_Correction4"
)
# Define the expected column order
$expectedColumns = @(
"Cal Day Posted",
"Fiscal Period Expense",
"Fiscal Year Expense",
"Cal Month Calculated",
"Cal Year Calculated",
"Cost Cente",
"Cost Center",
"PERNR",
"SSN",
"Last name",
"First name",
"EE SG name",
"Position Title",
"Status",
"EE Portion",
"Ded Due",
"Ded Diff",
"HW",
"Dental",
"Vision",
"STD",
"Life",
"Dif HW",
"Dif Dental",
"Dif Vision",
"Dif Std",
"Dif Life",
"Total HW",
"Disability",
"Dif Disability",
"Dependent",
"Dif Dependent",
"Deductions",
"Dif Deductions",
"Supplemental",
"Dif Supplemental",
"Pen Rate",
"Pen CBU Type",
"Pen Total CBU",
"Pension",
"Dif Pension",
"Supp Pen Rate",
"Supp Pen CBU Type",
"Supp Pen Total CBU",
"Supp Pension",
"MCTF",
"Paid MCTF",
"Due MCTF",
"Dif MCTF",
"Education",
"Dif Education",
"Legal",
"Dif Legal",
"Training",
"Dif Training",
"401K",
"Dif 401k",
"Child Care",
"Dif Child Care",
"Job Security",
"Dif Job Security",
"LTEF",
"Dif LTEF",
"Admin",
"Dif Admin",
"Safety",
"Dif Safety",
"Housing",
"Dif Housing",
"Comment"
)
# For each folder in rootfolder, create a break so that we can restart with a designated folder count
foreach ($folder in (Get-ChildItem -Path $rootFolder -Directory)) {
# Array to store data from each sheet
$allData = @()
# Get subfolder $folderCount from rootFolder
$folder = Get-ChildItem -Path $rootFolder -Directory | Select-Object -Index $folderCount
Write-Host "Folder: $($folder.Name) Count: $folderCount Started"
# Get all Excel files in subfolders
$excelFiles = Get-ChildItem -Path $folder.FullName -Filter *.xlsx -Recurse | Where-Object { $_.CreationTime -ge $minCreationDate }
# Loop through each Excel file
foreach ($excelFile in $excelFiles) {
# Loop through each sheet in the current Excel file
foreach ($sheetName in $sheetNames) {
try {
# Import data from the specified sheet
$sheetData = Import-Excel -Path $excelFile.FullName -WorksheetName $sheetName -ErrorAction Stop
# Loop through each row in the sheetData
foreach ($row in $sheetData) {
# Create a custom object with the desired column order
$customObject = [PSCustomObject]@{
'File' = $excelFile.Name
'Sheet' = $sheetName
}
# Add other columns dynamically, ignoring case
foreach ($column in $expectedColumns) {
$columnNameLower = $column.ToLower()
$customObject | Add-Member -MemberType NoteProperty -Name $column -Value $row.($columnNameLower)
}
$allData += $customObject
}
} catch {
Write-Warning "Error importing data from sheet '$sheetName' in file '$($excelFile.FullName)': $_"
}
}
}
# Save the merged data to a new Excel file
Write-Host "Folder: $($folder.Name) Count: $($folderCount) Finished"
$outputFilePath = "$($outputFolderPath)$($folder.Name).xlsx"
Write-Host "$($outputFilePath) has been created."
$allData | Export-Excel -Path $outputFilePath -AutoSize
$folderCount += 1
}