Export-Excel模块列丢失、空表及日期拆分问题排查求助

1. 现有流水线流程 #

1.1 SQL查询导出为TXT #

通过sqlcmd执行查询并将结果导出至TXT文件：

sqlcmd -S $(Server_prod) -i "G:\DB_Automation\xxx\Night_Batch_Report_4.sql" -o "G:\DB_Automation\SQL_Queries\xxx\4th_init.txt"

1.2 初始PowerShell转Excel脚本 #

使用PowerShell将TXT内容转换为XLSX格式：

- task: PowerShell@2
  displayName: Tranfer 4st night job --> Excel
  inputs:
    targetType: 'inline'
    script: |
      $rawData = Get-Content -Path 'G:\DB_Automation\SQL_Queries\Results\Night_batch\1st_init.txt' | Where-Object {$_ -match '\S'}
      $delimiter = [cultureinfo]::CurrentCulture.TextInfo.ListSeparator 
      ($rawData -replace '\s+' , $delimiter) | Set-Content -Path 'G:\DB_Automation\SQL_Queries\Results\Night_batch\theNewFile.csv'
      Import-Csv -Path 'G:\DB_Automation\SQL_Queries\Results\Night_batch\theNewFile.csv' | Export-Excel -Path 'G:\DB_Automation\SQL_Queries\Results\Night_batch\batch_audit_report_$(Build.BuildNumber)_prod_Night.xlsx' -Autosize -WorkSheetname '04-Check all task completed'

2. 初始问题 #

TXT文件内容完整，但最终导出的XLSX丢失部分列。

3. 更新后的PowerShell脚本 #

调整脚本后尝试解决列丢失问题：

$text = Get-Content -Raw -Path 'G:\DB_Automation\xxx\Night_batch\8th_init.txt' 
$rawData = ($text -split "\(\d* rows affected\)")[1].Trim() -split "\n"
$delimiter = [cultureinfo]::CurrentCulture.TextInfo.ListSeparator 
$data = ($rawData -replace '\s+' , $delimiter) | ConvertFrom-Csv
$data | Export-Excel -Path 'G:\DB_Automation\xxx\Night_batch\batch_audit_report_$(Build.BuildNumber)_prod_Night.xlsx' -Autosize -WorkSheetname '08-t_team_role_conn' 

4. 新出现的问题 #

• 部分查询正常导出，部分查询生成空Excel表；
• 日期时间值被拆分为日期、时间两列，未保留为单个单元格内容。

5. 问题排查与解决方案 #

5.1 空Excel表问题处理 #

原因

1. 正则\(\d* rows affected\)匹配失败：非英文环境下sqlcmd的行数提示文本不同，或查询无结果时无该提示，导致[1]索引取到空值；
2. SQL查询本身无返回行；
3. 文件编码不匹配，Get-Content -Raw读取内容异常。

解决方案

# 1. 兼容多语言环境的行数提示，增加容错判断
$rowPattern = '\(\d+ rows? affected\)'
$splitParts = $text -split $rowPattern, 0, 'IgnoreCase'
$rawData = if ($splitParts.Count -ge 2) {
    ($splitParts[1].Trim() -split "\n") | Where-Object { $_ -match '\S' }
} else {
    ($text -split "\n") | Where-Object { $_ -match '\S' -and $_ -notmatch $rowPattern }
}

# 2. 指定sqlcmd默认编码读取文件
$text = Get-Content -Raw -Path 'G:\DB_Automation\xxx\Night_batch\8th_init.txt' -Encoding Unicode

# 3. 空数据时写入提示，避免生成完全空的工作表
if ($data.Count -eq 0) {
    $emptyRow = [PSCustomObject]@{ "提示" = "该查询无返回结果" }
    $emptyRow | Export-Excel -Path 'G:\DB_Automation\xxx\Night_batch\batch_audit_report_$(Build.BuildNumber)_prod_Night.xlsx' -Autosize -WorkSheetname '08-t_team_role_conn'
} else {
    $data | Export-Excel -Path 'G:\DB_Automation\xxx\Night_batch\batch_audit_report_$(Build.BuildNumber)_prod_Night.xlsx' -Autosize -WorkSheetname '08-t_team_role_conn'
}

5.2 日期时间拆分问题处理 #

原因

\s+正则会将日期与时间之间的单个空格替换为分隔符，导致单个日期时间值被拆分为两列。

解决方案

方案1：精准替换多空格分隔符

仅替换2个及以上连续空格为分隔符，保留日期时间内部的单个空格：

$data = ($rawData -replace '\s{2,}' , $delimiter) | ConvertFrom-Csv

方案2：直接用sqlcmd导出CSV（推荐）

跳过TXT转CSV步骤，让sqlcmd直接导出标准CSV格式，从根源避免格式问题：

sqlcmd -S $(Server_prod) -i "G:\DB_Automation\xxx\Night_Batch_Report_4.sql" -o "G:\DB_Automation\SQL_Queries\xxx\4th_init.csv" -s "," -W -h-1

参数说明：

• -s ","：指定列分隔符为逗号
• -W：去除列的多余空格
• -h-1：不输出系统表头（若SQL查询已包含自定义表头，可省略该参数）

内容的提问来源于stack exchange，提问作者Michał Picheta