PowerShell拆分CSV文件输出格式异常问题求助

问题现象 #

我编写的按指定列拆分CSV文件的PowerShell代码，输出的CSV文件呈现键值对格式，而非标准的逗号分隔行列格式：

错误输出示例：

Company : ABC
Desc : DEF
Region : GHI

期望的标准CSV格式：

Company,Desc,Region
ABC,DEF,GHI

原代码 #

function fBreakFiles() {
    param(
        [String]$pDirectory,
        [String]$pFileNameString,
        [String]$pDateColumnName,
        [String]$pNewFileString,
        [String]$pLogFile,
        [String]$pDateFormat
    )

    Get-ChildItem $pDirectory -Force | Where-Object { $_.Name -ilike "$pFileNameString" } | ForEach-Object {
        $FilePath = $_.FullName
        Write-Output "################ $(Get-Date -Format 'yyyy/MM/dd hh:mm:ss:fff') :: Starting to extract source file at location $FilePath  `r`n" | Out-File $pLogFile -Append

        # Open the CSV file for reading
        $streamReader = [System.IO.StreamReader]::new($FilePath)

        # Get header line
        $header = $streamReader.ReadLine()

        # Set the buffer size based on your requirements
        $bufferSize = 2000
        $buffer = @()

        while ($streamReader.Peek() -ge 0) {
            # Read a block of lines into the buffer
            $buffer = 1..$bufferSize | ForEach-Object { $streamReader.ReadLine() }

            # Group the buffer data by required column
            $csvString = $buffer -join "`n"
            $csvObject = $csvString | ConvertFrom-Csv -Header ($header -split ',')
            $groupedData = $csvObject | Group-Object { $_.$pDateColumnName.Trim() }

            foreach ($group in $groupedData) {
                $DateString = ([datetime]::parseexact(($group.name), $pDateFormat, $null)).ToString("yyyy-MM-dd")
                $outputPath = Join-Path $pDirectory "$pNewFileString$DateString.csv"
                if ($Date -ne $DateString)
                {
                    $header | Out-File -Append -FilePath $outputPath
                }
                $Date = $DateString
                $group.Group | Out-File -Append -FilePath $outputPath
            }
        }
    }
}

问题原因与修复 #

直接使用Out-File输出$group.Group时，PowerShell会将对象以默认的键值对格式序列化写入文件。需要用ConvertTo-Csv将对象转换回标准CSV格式，同时去掉自动添加的类型信息。

另外原代码中存在一个变量错误：$pSourceDirectory未定义，应替换为参数$pDirectory。

修改后的关键代码段 #

在写入数据行的部分，替换原有的$group.Group | Out-File...为以下内容：

# 将对象转换为CSV格式，跳过表头（已单独写入）
$group.Group | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append -FilePath $outputPath

完整修复后的代码 #

function fBreakFiles() {
    param(
        [String]$pDirectory,
        [String]$pFileNameString,
        [String]$pDateColumnName,
        [String]$pNewFileString,
        [String]$pLogFile,
        [String]$pDateFormat
    )

    Get-ChildItem $pDirectory -Force | Where-Object { $_.Name -ilike "$pFileNameString" } | ForEach-Object {
        $FilePath = $_.FullName
        Write-Output "################ $(Get-Date -Format 'yyyy/MM/dd hh:mm:ss:fff') :: Starting to extract source file at location $FilePath  `r`n" | Out-File $pLogFile -Append

        # Open the CSV file for reading
        $streamReader = [System.IO.StreamReader]::new($FilePath)

        # Get header line
        $header = $streamReader.ReadLine()

        # Set the buffer size based on your requirements
        $bufferSize = 2000
        $buffer = @()

        while ($streamReader.Peek() -ge 0) {
            # Read a block of lines into the buffer
            $buffer = 1..$bufferSize | ForEach-Object { $streamReader.ReadLine() }

            # Group the buffer data by required column
            $csvString = $buffer -join "`n"
            $csvObject = $csvString | ConvertFrom-Csv -Header ($header -split ',')
            $groupedData = $csvObject | Group-Object { $_.$pDateColumnName.Trim() }

            foreach ($group in $groupedData) {
                $DateString = ([datetime]::parseexact(($group.name), $pDateFormat, $null)).ToString("yyyy-MM-dd")
                $outputPath = Join-Path $pDirectory "$pNewFileString$DateString.csv"
                if ($Date -ne $DateString)
                {
                    $header | Out-File -Append -FilePath $outputPath
                }
                $Date = $DateString
                # 转换为标准CSV格式并写入，跳过自动生成的表头
                $group.Group | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Append -FilePath $outputPath
            }
        }
    }
}

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