如何用PowerShell将多文件逗号分隔行合并至单个Excel表格

核心思路 #

遍历目标文件夹内所有.txt文件，读取每个文件的前5行，用正则提取关键统计字段，将每行的字段整合为单条结构化记录，最后追加到CSV文件（Excel可直接打开或导入）。

完整PowerShell脚本 #

# 配置路径：替换为你的实际路径
$folderPath = "C:\Path\To\Your\TxtFiles"
$outputCsvPath = "C:\Path\To\Save\server_stats.csv"

# 获取所有txt文件
$txtFiles = Get-ChildItem -Path $folderPath -Filter "*.txt"

$results = @()

foreach ($file in $txtFiles) {
    try {
        # 读取文件前5行
        $lines = Get-Content -Path $file.FullName -Head 5 -ErrorAction Stop

        # 解析第一行：时间、运行时长、用户数、负载均值
        $line1Matches = [regex]::Match($lines[0], 'top - (\d{2}:\d{2}:\d{2}) up (.+?), (\d{2}:\d{2}),  (\d+) user.*load average: ([\d.]+), ([\d.]+), ([\d.]+)')
        $recordTime = $line1Matches.Groups[1].Value
        $uptimeDays = $line1Matches.Groups[2].Value
        $uptimeHours = $line1Matches.Groups[3].Value
        $userCount = $line1Matches.Groups[4].Value
        $load1 = $line1Matches.Groups[5].Value
        $load5 = $line1Matches.Groups[6].Value
        $load15 = $line1Matches.Groups[7].Value

        # 解析第二行：任务状态统计
        $line2Matches = [regex]::Match($lines[1], 'Tasks: (\d+) total,   (\d+) running, (\d+) sleeping,   (\d+) stopped,   (\d+) zombie')
        $tasksTotal = $line2Matches.Groups[1].Value
        $tasksRunning = $line2Matches.Groups[2].Value
        $tasksSleeping = $line2Matches.Groups[3].Value
        $tasksStopped = $line2Matches.Groups[4].Value
        $tasksZombie = $line2Matches.Groups[5].Value

        # 解析第三行：CPU使用率
        $line3Matches = [regex]::Match($lines[2], '%Cpu\(s\): ([\d.]+) us,  ([\d.]+) sy,  ([\d.]+) ni, ([\d.]+) id,  ([\d.]+) wa,  ([\d.]+) hi,  ([\d.]+) si,  ([\d.]+) st')
        $cpuUs = $line3Matches.Groups[1].Value
        $cpuSy = $line3Matches.Groups[2].Value
        $cpuNi = $line3Matches.Groups[3].Value
        $cpuId = $line3Matches.Groups[4].Value
        $cpuWa = $line3Matches.Groups[5].Value
        $cpuHi = $line3Matches.Groups[6].Value
        $cpuSi = $line3Matches.Groups[7].Value
        $cpuSt = $line3Matches.Groups[8].Value

        # 解析第四行：内存统计
        $line4Matches = [regex]::Match($lines[3], 'KiB Mem : (\d+) total, (\d+) free,  (\d+) used, (\d+) buff/cache')
        $memTotal = $line4Matches.Groups[1].Value
        $memFree = $line4Matches.Groups[2].Value
        $memUsed = $line4Matches.Groups[3].Value
        $memBuffCache = $line4Matches.Groups[4].Value

        # 解析第五行：交换分区统计
        $line5Matches = [regex]::Match($lines[4], 'KiB Swap:  (\d+) total,  (\d+) free,        (\d+) used. (\d+) avail Mem')
        $swapTotal = $line5Matches.Groups[1].Value
        $swapFree = $line5Matches.Groups[2].Value
        $swapUsed = $line5Matches.Groups[3].Value
        $memAvail = $line5Matches.Groups[4].Value

        # 构建单条记录对象
        $record = [PSCustomObject]@{
            SourceFile      = $file.Name
            RecordTime      = $recordTime
            UptimeDays      = $uptimeDays
            UptimeHours     = $uptimeHours
            UserCount       = $userCount
            LoadAverage1    = $load1
            LoadAverage5    = $load5
            LoadAverage15   = $load15
            TasksTotal      = $tasksTotal
            TasksRunning    = $tasksRunning
            TasksSleeping   = $tasksSleeping
            TasksStopped    = $tasksStopped
            TasksZombie     = $tasksZombie
            CpuUserPercent  = $cpuUs
            CpuSysPercent   = $cpuSy
            CpuNicePercent  = $cpuNi
            CpuIdlePercent  = $cpuId
            CpuWaitPercent  = $cpuWa
            CpuHiPercent    = $cpuHi
            CpuSiPercent    = $cpuSi
            CpuStPercent    = $cpuSt
            MemTotalKiB     = $memTotal
            MemFreeKiB      = $memFree
            MemUsedKiB      = $memUsed
            MemBuffCacheKiB = $memBuffCache
            SwapTotalKiB    = $swapTotal
            SwapFreeKiB     = $swapFree
            SwapUsedKiB     = $swapUsed
            MemAvailKiB     = $memAvail
        }

        $results += $record
    }
    catch {
        Write-Warning "处理文件 $($file.Name) 失败：$_"
    }
}

# 导出到CSV：存在则追加，不存在则新建
if (Test-Path $outputCsvPath) {
    $results | Export-Csv -Path $outputCsvPath -Append -NoTypeInformation -Encoding UTF8
}
else {
    $results | Export-Csv -Path $outputCsvPath -NoTypeInformation -Encoding UTF8
}

Write-Host "处理完成，共生成 $($results.Count) 条记录，保存路径：$outputCsvPath"

使用步骤 #

1. 修改脚本开头的$folderPath和$outputCsvPath为你的实际路径
2. 运行脚本，生成的CSV文件可直接用Excel打开，或通过Excel「数据」-「从文本/CSV」导入

可选优化：直接写入Excel #

若要跳过CSV直接生成Excel文件，先安装ImportExcel模块（运行Install-Module -Name ImportExcel），然后将脚本末尾的CSV导出代码替换为：

$outputExcelPath = "C:\Path\To\Save\server_stats.xlsx"
if (Test-Path $outputExcelPath) {
    $results | Export-Excel -Path $outputExcelPath -Append -WorksheetName "ServerStats"
}
else {
    $results | Export-Excel -Path $outputExcelPath -WorksheetName "ServerStats"
}

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