修改PowerShell的BCP脚本实现SQL Server多行数据转单行CSV导出

需求：使用PowerShell调用BCP工具从SQL Server导出数据到CSV文件，要求CSV以逗号为分隔符，同时将源表中字段内的多行内容转换为单行格式。

当前使用的脚本 #

$tableList =@(
"Test1",
"Sample",
"Access"
)

foreach ($table in $tableList)
{
    Write-Host $table

    $selectQuery = "select * from Test.$table"
    $queryOutPath = "C:\Test\Test_$table\Test_$table`_20230612.csv"
    $mkdirPath = "C:\Test\Test_$table"

    Write-Host $selectQuery
    Write-Host $queryOutPath

    # Create the directory if it doesn't exist
    if (-not (Test-Path -Path $mkdirPath -PathType Container))
    {
        New-Item -Path $mkdirPath -ItemType Directory | Out-Null
    }

    # SQL Server credentials
    $server = "Some Server"
    $database = "TestDB"
    $username = "TestUser"
    $password = "TestPassword"

    # Export data using BCP with credentials
    & 'bcp' $selectQuery queryout $queryOutPath -S $server -d $database -U $username -P $password  -c -t "," -k -r "`"\n"

}

源数据与期望格式 #

源数据导出后的示例 #

12,other,iutguop,oihdi\,1,15,2014-08-11 17:09:50,itgiug
9,test
UPS
P.O. Box 8769870
VA 986987
1-800-769-XXX

期望的目标格式 #

12,other,iutguop,oihdi\,1,15,2014-08-11 17:09:50,itgiug
9,test UPS P.O. Box 8769870 VA 986987 1-800-769-XXX

修改后的脚本 #

方案1：在SQL查询阶段替换字段内的换行符（推荐） #

这种方式直接在导出前处理数据，避免后续文件修改的兼容性问题：

$tableList =@(
"Test1",
"Sample",
"Access"
)

foreach ($table in $tableList)
{
    Write-Host $table

    # 动态生成查询：替换所有字段中的换行符为空格
    $server = "Some Server"
    $database = "TestDB"
    $username = "TestUser"
    $password = "TestPassword"
    
    # 获取表的所有列名
    $columnQuery = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='Test' AND TABLE_NAME='$table'"
    $columns = & 'sqlcmd' -S $server -d $database -U $username -P $password -Q $columnQuery -h -1 -W
    $selectColumns = $columns | ForEach-Object { "REPLACE(REPLACE($_[0], CHAR(13), ''), CHAR(10), ' ') AS $_[0]" } -join ", "
    $selectQuery = "SELECT $selectColumns FROM Test.$table"

    $queryOutPath = "C:\Test\Test_$table\Test_$table`_20230612.csv"
    $mkdirPath = "C:\Test\Test_$table"

    Write-Host $selectQuery
    Write-Host $queryOutPath

    # 创建目录（如果不存在）
    if (-not (Test-Path -Path $mkdirPath -PathType Container))
    {
        New-Item -Path $mkdirPath -ItemType Directory | Out-Null
    }

    # 使用BCP导出，修正行终止符为标准换行
    & 'bcp' $selectQuery queryout $queryOutPath -S $server -d $database -U $username -P $password -c -t "," -k -r "`n"

}

方案2：导出后用PowerShell处理文件 #

如果无法修改SQL查询，可以在导出后对CSV文件进行处理，将不属于新数据行的换行替换为空格：

$tableList =@(
"Test1",
"Sample",
"Access"
)

foreach ($table in $tableList)
{
    Write-Host $table

    $selectQuery = "select * from Test.$table"
    $queryOutPath = "C:\Test\Test_$table\Test_$table`_20230612.csv"
    $mkdirPath = "C:\Test\Test_$table"

    Write-Host $selectQuery
    Write-Host $queryOutPath

    # 创建目录（如果不存在）
    if (-not (Test-Path -Path $mkdirPath -PathType Container))
    {
        New-Item -Path $mkdirPath -ItemType Directory | Out-Null
    }

    # SQL Server credentials
    $server = "Some Server"
    $database = "TestDB"
    $username = "TestUser"
    $password = "TestPassword"

    # 使用BCP导出，修正行终止符为标准换行
    & 'bcp' $selectQuery queryout $queryOutPath -S $server -d $database -U $username -P $password -c -t "," -k -r "`n"

    # 处理CSV文件：将非起始行的换行替换为空格
    $content = Get-Content -Path $queryOutPath -Raw
    # 匹配不以数字开头的换行（假设数据行以数字开头，可根据实际调整正则）
    $processedContent = $content -replace '(?<!^\d+)(\r?\n)', ' '
    Set-Content -Path $queryOutPath -Value $processedContent -Encoding UTF8
}

关键修改说明 #

1. 修正BCP行终止符：原脚本中的-r ""\n"存在语法问题，改为标准的-r "n"，确保数据行正确分隔。
2. 字段内换行处理：
   • 方案1通过SQL查询，使用REPLACE函数将字段中的回车符（CHAR(13)）和换行符（CHAR(10)）替换为空格，从根源上避免导出时的换行问题。
   • 方案2在导出后通过PowerShell正则匹配，将不属于新数据行的换行替换为空格，适合无法修改SQL查询的场景。

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