如何在PowerShell中导入表格格式TXT并导出为CSV（含自定义处理）

我有一个表格格式的sample.txt文件，内容如下：

Testing line1 in file                                         

DATE    M    MA    S    FD    FU    PE    SS    AS    NO    OO    CD
05/31/23    FM    0    000    Account name 1         403    30.75    0.000    0.00    0    0    2
04/31/23    FM    0    000    Account name 2         403    30.75    0.000    0.00    0    0    2
03/31/23    FM    0    000    Account name 3         403    30.75    0.000    0.00    0    0    2

需要将其转换为CSV文件，要求：

• 跳过前2行
• 添加新列Name和data
  最终输出的FINAL_OUTPUT.csv格式如下：

"DATE","PE","SS","AS","NO","OO","CD","Name","data"
"May","30.75","0.000","0.00","0","0","2","FM,0,000,Account name 1,403","data1" 
"April","30.75","0.000","0.00","0","0","2","FM,0,000,Account name 2,403","data1"
"March","30.75","0.000","0.00","0","0","2","FM,0,000,Account name 3,403","data1"

我尝试了以下代码但未达到预期效果，求正确的PowerShell实现：

$data = format-table 'E:\sample.txt'  |
    Select-Object -Skip 2 |
    Out-String |
    ConvertFrom-StringData
 [PSCustomObject]$data | Export-Csv 'E:\out1.txt' -NoType

$tempcsv=import-csv -path E:\out1.txt -Header 
"DATE”,”M”,”MA”,”S”,”FD”,”FU”,”PE”,”SS”,”AS”,”NO “,”OO”,”CD"| select -skip 2
$tempcsv | select DATE,M,MA, S ,FD,FU,PE,SS ,AS,NO,OO,CD| Group-Object M,MA,S,FD,FU| 
select Name,
@{ Name = 'PE'; Expression = { ($_.Group | Measure-Object -Property PE -Sum).Sum } },
@{ Name = 'SS'; Expression = { ($_.Group | Measure-Object -Property SS -Sum).Sum } },
@{ Name = 'AS'; Expression = { ($_.Group | Measure-Object -Property AS -Sum).Sum } },
@{ Name = 'NO'; Expression = { ($_.Group | Measure-Object -Property NO -Sum).Sum } },
@{ Name = 'OO'; Expression = { ($_.Group | Measure-Object -Property OO -Sum).Sum } },
@{ Name = 'CD'; Expression = { ($_.Group | Measure-Object -Property CD -Sum).Sum } }| 
EXPORT-CSV -path E:\FINAL_OUTPUT.txt
Write-host "FINAL FILE converSion completed"

以下代码可满足需求，核心逻辑是处理空格分隔的文本数据，转换日期格式并构造目标列：

# 定义文件路径
$inputPath = "E:\sample.txt"
$outputPath = "E:\FINAL_OUTPUT.csv"

# 读取文件、过滤行并处理数据
$processedData = Get-Content $inputPath | Select-Object -Skip 2 | ForEach-Object {
    # 跳过空行
    if ([string]::IsNullOrWhiteSpace($_)) { return }
    
    # 按任意多空格分割行数据，过滤空元素
    $columns = $_ -split '\s+' | Where-Object { $_ }
    
    # 将日期转换为月份全称
    $date = [DateTime]::ParseExact($columns[0], 'MM/dd/yy', $null)
    $monthName = $date.ToString('MMMM')
    
    # 拼接Name列内容
    $name = "$($columns[1]),$($columns[2]),$($columns[3]),$($columns[4]),$($columns[5])"
    
    # 构造符合输出要求的自定义对象
    [PSCustomObject]@{
        DATE = $monthName
        PE   = $columns[6]
        SS   = $columns[7]
        AS   = $columns[8]
        NO   = $columns[9]
        OO   = $columns[10]
        CD   = $columns[11]
        Name = $name
        data = "data1"
    }
}

# 导出为CSV，确保所有列被引号包裹
$processedData | Export-Csv -Path $outputPath -NoTypeInformation -UseQuotes Always

Write-Host "FINAL FILE conversion completed"

关键说明 #

• 数据分割：用-split '\s+'处理列间的任意多空格，避免因空格数量不一致导致的分割错误
• 日期转换：通过ParseExact精准解析日期格式，再转为月份全称
• 列构造：直接拼接指定列生成Name值，同时添加固定值的data列
• CSV导出：-UseQuotes Always确保所有列都被引号包裹，完全匹配目标格式

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