PowerShell解析文本转CSV：跨行字符串拆分问题求助

这种方法通过逐行遍历文本，跟踪当前所在的SECTION，识别UPDATE/INSERT语句并提取对应内容，逻辑直观易理解：

$inputPath = 'C:\Example.txt'
$outputPath = 'C:\Example.csv'
# 读取全部文本并按空行分割成独立块，过滤空内容
$content = Get-Content $inputPath -Raw -Encoding UTF8
$blocks = $content -split '(?m)^\s*$' | Where-Object { $_ -match '\S' }

$result = @()
$currentSection = $null

foreach ($block in $blocks) {
    $lines = $block -split "`n" | ForEach-Object { $_.Trim() } | Where-Object { $_ -match '\S' }
    
    # 识别SECTION标记（--PRINT 开头的行）
    if ($lines -match '^--PRINT ') {
        $currentSection = ($lines | Where-Object { $_ -match '^--PRINT ' }).Split(' ')[-1]
        continue
    }

    # 处理UPDATE/INSERT操作块
    if ($lines[0] -match '^(UPDATE|INSERT)') {
        $operation = $matches[1]
        $table = $null
        $changeBlock = $null

        if ($operation -eq 'UPDATE') {
            # 提取表名
            $table = $lines[0].Split(' ', 3)[1]
            # 提取SET之后的变更内容，合并为单行
            $changeBlock = ($lines | Where-Object { $_ -match '^SET' } | ForEach-Object { $_ -replace '^SET ', '' }) -join ' '
            # 替换多余空格为单个空格
            $changeBlock = $changeBlock -replace '\s+', ' '
        }
        else { # INSERT操作
            # 从INSERT语句中提取表名（兼容[dbo].[Table_X]格式）
            $table = ($lines[0] -split '\[dbo\]\.|\[|\]' | Where-Object { $_ -match '^Table_' })[0]
            # 提取括号开始到结尾的内容，合并为单行
            $changeStart = $lines[0].IndexOf('(')
            $changeBlock = $lines[0].Substring($changeStart) + ' ' + ($lines | Select-Object -Skip 1) -join ' '
            $changeBlock = $changeBlock -replace '\s+', ' '
        }

        # 创建结果对象
        $result += [PSCustomObject]@{
            'SECTION'       = $currentSection
            'UPDATE/INSERT' = $operation
            'TABLE'         = $table
            'CHANGE BLOCK'  = $changeBlock.Trim()
        }
    }
}

# 导出CSV
$result | Export-Csv -Path $outputPath -NoTypeInformation -Encoding UTF8

如果想用更简洁的方式处理，可利用多行正则一次性匹配SECTION和对应的操作块：

$inputPath = 'C:\Example.txt'
$outputPath = 'C:\Example.csv'
$content = Get-Content $inputPath -Raw -Encoding UTF8

# 正则模式：(?s)开启单行模式，匹配SECTION和后续所有操作内容
$regex = @'
(?s)--PRINT (\w+)\s+((?:UPDATE|INSERT).+?)(?=(?:--PRINT|\Z))
'@

$matches = [regex]::Matches($content, $regex)
$result = @()

foreach ($match in $matches) {
    $section = $match.Groups[1].Value
    # 拆分当前SECTION下的多个UPDATE/INSERT操作
    $operationChunks = $match.Groups[2].Value -split '(?m)^(UPDATE|INSERT)' | Where-Object { $_ -match '\S' }

    for ($i=0; $i -lt $operationChunks.Count; $i+=2) {
        $operation = $operationChunks[$i]
        $chunkContent = $operationChunks[$i+1].Trim()
        $table = $null
        $changeBlock = $null

        if ($operation -eq 'UPDATE') {
            $table = ($chunkContent -split ' ', 2)[0]
            $changeBlock = ($chunkContent -split '(?m)^SET ', 2)[1].Trim() -replace '\s+', ' '
        }
        else {
            $table = ($chunkContent -split '\[dbo\]\.|\[|\]' | Where-Object { $_ -match '^Table_' })[0]
            $changeBlock = ($chunkContent -split '(?=\()', 2)[1].Trim() -replace '\s+', ' '
        }

        $result += [PSCustomObject]@{
            'SECTION'       = $section
            'UPDATE/INSERT' = $operation
            'TABLE'         = $table
            'CHANGE BLOCK'  = $changeBlock
        }
    }
}

$result | Export-Csv -Path $outputPath -NoTypeInformation -Encoding UTF8

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