如何用PowerShell修复CSV重复列名并对比周度文件表头差异？

Got it, let's work through these two PowerShell tasks step by step. I'll provide ready-to-use scripts and break down what each part does so you can adapt it to your Windows Server 2012 R2 environment (and it'll work just fine with the latest PowerShell version too).

Problem 1: Replace Duplicate Headers with Correct Ones from correct.csv #

The key here is avoiding CSV parsing issues from duplicate column names (which would break Import-Csv). Instead, we'll read the files as plain text to directly swap out the first line (the header).

Here's the script:

# Define your file paths - adjust these to match your actual locations
$importFile = "import-week10.csv"
$correctHeaderFile = "correct.csv"

# Grab the single correct header line from correct.csv
$correctHeader = Get-Content -Path $correctHeaderFile -TotalCount 1

# Read all lines of the import file, replace the first line with the correct header
$importContent = Get-Content -Path $importFile
$importContent[0] = $correctHeader

# Write the modified content back to the original file (overwrites it)
$importContent | Set-Content -Path $importFile -Encoding UTF8

Write-Host "✅ Successfully updated headers in $importFile"

What this does: #

• Get-Content -TotalCount 1 pulls just the first line (your valid header) from correct.csv without reading the whole file.
• Reading all lines of import-week10.csv lets us bypass any CSV parsing errors caused by duplicate column names.
• Set-Content -Encoding UTF8 ensures consistent character handling, which prevents weird formatting issues in your CSV later.

Problem 2: Compare Headers with Last Week's File and Warn on Changes #

This script checks if the original header of import-week10.csv differs from import-week09.csv and alerts you if there's a mismatch. It also handles cases where last week's file might be missing.

Here's the script:

# Define file paths
$thisWeekFile = "import-week10.csv"
$lastWeekFile = "import-week09.csv"

# First, check if last week's file exists to avoid errors
if (-not (Test-Path -Path $lastWeekFile)) {
    Write-Warning "⚠️ Warning: File $lastWeekFile does not exist. Cannot compare headers."
    exit
}

# Grab the first line (header) from both files
$thisWeekOriginalHeader = Get-Content -Path $thisWeekFile -TotalCount 1
$lastWeekHeader = Get-Content -Path $lastWeekFile -TotalCount 1

# Compare the headers and alert if they don't match
if ($thisWeekOriginalHeader -ne $lastWeekHeader) {
    Write-Warning "⚠️ WARNING: Header has changed from last week's file!"
    Write-Host "Last week's header: $lastWeekHeader"
    Write-Host "This week's original header: $thisWeekOriginalHeader"
} else {
    Write-Host "✅ Header matches last week's file. No changes detected."
}

What this does: #

• Test-Path verifies the previous week's file exists before trying to read it, so you don't get a "file not found" error.
• We only read the first line of each file for comparison, which is fast even with large CSVs.
• If headers differ, you get a clear warning plus both headers to see exactly what changed.

Combine Both Tasks (Optional) #

If you want to run the header check first, then replace the headers (regardless of the check result), you can merge the scripts like this:

# Define all file paths
$importFile = "import-week10.csv"
$correctHeaderFile = "correct.csv"
$lastWeekFile = "import-week09.csv"

# --- Step 1: Compare with last week's header ---
if (Test-Path -Path $lastWeekFile) {
    $thisWeekOriginalHeader = Get-Content -Path $importFile -TotalCount 1
    $lastWeekHeader = Get-Content -Path $lastWeekFile -TotalCount 1

    if ($thisWeekOriginalHeader -ne $lastWeekHeader) {
        Write-Warning "⚠️ WARNING: Original header has changed from last week's file!"
        Write-Host "Last week's header: $lastWeekHeader"
        Write-Host "This week's original header: $thisWeekOriginalHeader"
    } else {
        Write-Host "✅ Original header matches last week's file."
    }
} else {
    Write-Warning "⚠️ Warning: $lastWeekFile not found - skipping header comparison."
}

# --- Step 2: Replace with correct header ---
$correctHeader = Get-Content -Path $correctHeaderFile -TotalCount 1
$importContent = Get-Content -Path $importFile
$importContent[0] = $correctHeader
$importContent | Set-Content -Path $importFile -Encoding UTF8

Write-Host "✅ Successfully updated $importFile with correct headers."

内容的提问来源于stack exchange，提问作者Razvan Zoitanu