使用PowerShell清理CSV文件多余逗号并补全部门信息

需求说明 #

现有一份需导入SQL的CSV文件，格式不符合要求且无法修改生成它的Excel源文件，需通过PowerShell完成以下处理：

• 移除每行中的多余逗号
• 将每行开头的逗号占位替换为对应的部门名称
• 删除仅包含部门名称加大量逗号的行（如Psychology ,,,,,,,,,,,,,,,,,,,,,,,这类行）

示例对比 #

原始格式：

Department,,,,,,First Name,,,,Last Name,,,,,,,School Year,Enrolment Status
Psychology ,,,,,,,,,,,,,,,,,,,,,,, (Remove this line)
,,,,,,Jane,,,,Doe,,,,,,,2022,Enrolled
,,,,,,Jeff,,,,Dane,,,,,,,2019,Enrolled
,,,,,,Tate,,,,Anderson,,,,,,,2019,Not Enrolled
,,,,,,Daphne,,,,Miller,,,,,,,2021,Enrolled
,,,,,,Cora,,,,Dame,,,,,,,2022,Enrolled
Computer Science ,,,,,,,,,,,,,,,,,,,,,,, (Remove this line)
,,,,,,Dora,,,,Explorer,,,,,,,2022,Not Enrolled
,,,,,,Peppa,,,,Diggs,,,,,,,2020,Enrolled
,,,,,,Conrad,,,,Strat,,,,,,,2020,Enrolled
,,,,,,Kat,Noir,,,,2019,,,,,,,Enrolled
,,,,,,Lance,,,,Bug,2018,,,,,,,Enrolled

目标格式：

Department,First Name,Last Name,School Year,Enrolment Status
Psychology,Jane,Doe,2022,Enrolled
Psychology,Jeff,Dane,2019,Enrolled
Psychology,Tate,Anderson,2019,Not Enrolled
Psychology,Daphne,Miller,2021,Enrolled
Psychology,Cora,Dame,2022,Enrolled
Computer Science,Dora,Explorer,2022,Not Enrolled
Computer Science,Peppa,Diggs,2020,Enrolled
Computer Science,Conrad,Strat,2020,Enrolled
Computer Science,Kat,Noir,2019,Enrolled
Computer Science,Lance,Bug,2018,Enrolled

PowerShell脚本实现 #

# 定义输入输出文件路径
$inputPath = "C:\path\to\your\source.csv"
$outputPath = "C:\path\to\your\processed.csv"

# 初始化变量跟踪当前部门
$currentDepartment = $null

# 逐行读取并处理文件
Get-Content -Path $inputPath | ForEach-Object {
    $line = $_
    # 处理表头行：清理连续逗号，保留正确表头格式
    if ($line -match '^Department') {
        $cleanHeader = $line -replace ',+', ',' -replace '^,|,$'
        $cleanHeader
        return
    }
    # 识别部门标记行：提取部门名称，跳过该行不输出
    if ($line -match '^([a-zA-Z\s]+)\s*,+') {
        $currentDepartment = $matches[1].Trim()
        return
    }
    # 处理数据行：替换开头逗号为当前部门，清理多余逗号
    if ($currentDepartment -and $line -match ',') {
        $cleanLine = $line -replace ',+', ',' -replace '^,|,$'
        $processedLine = "$currentDepartment,$cleanLine" -replace ',+', ','
        $processedLine
    }
} | Set-Content -Path $outputPath -Encoding UTF8

脚本说明 #

1. 路径配置：修改$inputPath和$outputPath为实际文件路径
2. 表头处理：匹配表头行，将连续多个逗号替换为单个，同时去除首尾多余逗号，保证表头格式合规
3. 部门标记行识别：通过正则匹配提取部门名称，记录当前部门后跳过该行，不写入输出文件
4. 数据行处理：先清理行内连续逗号与首尾逗号，再在行首拼接当前部门名称，最后再次清理可能出现的连续逗号，确保格式统一
5. 编码设置：使用UTF8编码输出，避免导入SQL时出现乱码问题

内容的提问来源于stack exchange，提问作者Kruskal W