Hi I have a PS script that is giving me this error when it gets to a sheet with duplicate headers. Duplicate column headers found on row '1' in columns '12 53'. Column headers must be unique, if this is not a requirement please use the '-NoHeader' or '-HeaderName
' parameter.
The duplicate headers causing the error are not ones listed in my $expectcolumns section. What can I do to update my script to ignore duplicate headers but still bring in the data? Currently it ignores the sheet altogether.
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
}
