XML转CSV解析异常：部分行格式不符合预期（PowerShell环境）

问题说明 #

使用PowerShell脚本将XML文件转换为CSV时，部分行的Comment字段内容会溢出到后续行中，但直接用Excel打开原XML文件时，Comment内容能正常显示在对应单元格内。

原PowerShell脚本 #

[XML]$inputFile = Get-Content -Path 'C:\Users\Desktop\Temp - 01D\XML_InstanceLog - 1.xml'
$inputFile.Document.ChildNodes | Export-Csv "C:\Users\Desktop\Temp - 01D\XML_InstanceLog - 1.csv" -NoTypeInformation -Delimiter:"|" -Encoding:UTF8
Get-Content -Path 'C:\Users\Desktop\Temp - 01D\XML_InstanceLog - 1.csv'   

XML示例数据 #

<?xml version="1.0" encoding="utf-8"?>
<Document>
  <Row InsCompare="" RequestId="" FormId="762" DTC="24-Jan-2023 04:05:07 PM" ReportingDate="30-Sep-2022" Status="8" UserId="abc123" InstanceDocPath="" EncryptDocPath="" ErrorDocPath="ABC220930R31802M_24-01-23_04-05-08_Instance.html" RenderedExcelDocPath="" IsExtract="true" IsInstance="true" IsCims="true">
    <Params />
    <Comment></Comment>
  </Row>
  <Row InsCompare="" RequestId="" FormId="762" DTC="24-Jan-2023 04:15:06 PM" ReportingDate="30-Sep-2022" Status="3" UserId="abc123" InstanceDocPath="" EncryptDocPath="" ErrorDocPath="" RenderedExcelDocPath="" IsExtract="true" IsInstance="true" IsCims="true">
    <Params />
    <Comment>Some of the queries does not executed successfully: StressedMSME: This table contains cells that are outside the range of cells defined in this spreadsheet.   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at CommonClass.ClsSqlQuerys.FunPubStaReturnDataSet(Int32 ProviderId, String ParamStrQuery)
   at WebZeal.Controllers.CreateInstanceController.RightPubInsertInstanceLog(Int32 DDLFormId, DateTime ParamDTRptDate, Boolean PropPubBoolIsExtract, Boolean PropPubBoolIsInstance, ClsParameters[] Param)</Comment>
  </Row>
</Document>

问题原因 #

问题出在Comment字段包含多行文本（如示例中的栈跟踪换行）。Export-Csv会自动用双引号包裹包含特殊字符（换行、分隔符）的字段，符合CSV规范，因此Excel打开时能正确识别。但用Get-Content或普通文本编辑器查看时，字段内的换行符会被解析为新行，导致视觉上的内容溢出。

解决方案 #

方案1：替换Comment字段中的换行符 #

如果需要让CSV文件在文本查看时也保持单行结构，可在导出前替换Comment内的换行符为空格或其他分隔符：

[XML]$inputFile = Get-Content -Path 'C:\Users\Desktop\Temp - 01D\XML_InstanceLog - 1.xml'
$processedRows = $inputFile.Document.ChildNodes | ForEach-Object {
    # 将换行符替换为空格，也可换成其他符号如"；"
    $cleanComment = $_.Comment.InnerText -replace "`r`n|`n|`r", " "
    # 构建包含所有属性的自定义对象
    [PSCustomObject]@{
        InsCompare          = $_.InsCompare
        RequestId           = $_.RequestId
        FormId              = $_.FormId
        DTC                 = $_.DTC
        ReportingDate       = $_.ReportingDate
        Status              = $_.Status
        UserId              = $_.UserId
        InstanceDocPath     = $_.InstanceDocPath
        EncryptDocPath      = $_.EncryptDocPath
        ErrorDocPath        = $_.ErrorDocPath
        RenderedExcelDocPath= $_.RenderedExcelDocPath
        IsExtract           = $_.IsExtract
        IsInstance          = $_.IsInstance
        IsCims              = $_.IsCims
        Comment             = $cleanComment
    }
}
# 导出处理后的结果
$processedRows | Export-Csv "C:\Users\Desktop\Temp - 01D\XML_InstanceLog - 1.csv" -NoTypeInformation -Delimiter:"|" -Encoding UTF8
Get-Content -Path 'C:\Users\Desktop\Temp - 01D\XML_InstanceLog - 1.csv'

方案2：确认CSV文件本身合规 #

如果仅需Excel能正常识别，原脚本生成的CSV其实是符合规范的——Export-Csv已经用双引号包裹了带换行的Comment字段。此时用文本查看时的“溢出”只是视觉效果，不影响Excel等工具的解析。

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