带字段内换行的竖线分隔文件导入SQL Server的问题

问题分析 #

核心矛盾是：无文本限定符的竖线分隔文件中，某自由输入字段包含用户添加的换行符，导致SQL Server的BULK INSERT误将字段内换行识别为行终止符，拆分了原本完整的记录。你之前的C#方案无效，原因有三点：

1. ReadLine()会自动截断换行符，直接把跨多行的单条记录拆成多个独立行，无法拼接完整记录
2. line.Replace()返回新字符串，但未赋值给原变量，替换操作实际未生效
3. 循环内重复打开StreamWriter，IO开销极大，完全不适用于60万行的大文件

解决方案：预处理文件修复换行问题 #

以下是修正后的C#代码，通过按字符读取并累积完整记录的方式，替换字段内的换行符，生成可正常导入的文件：

string sourcePath = @"C:\Work\OriginalFile.txt";
string targetPath = @"C:\Work\CleanedFile.txt";
const int ExpectedPipeCount = 15; // 已知每条记录的竖线数量
StringBuilder currentRecord = new StringBuilder();
int pipeCounter = 0;

// 一次性打开读写流，避免重复IO操作
using (StreamReader reader = new StreamReader(sourcePath, Encoding.UTF8))
using (StreamWriter writer = new StreamWriter(targetPath, false, Encoding.UTF8))
{
    int charCode;
    while ((charCode = reader.Read()) != -1)
    {
        char currentChar = (char)charCode;

        // 统计当前累积内容中的竖线数量
        if (currentChar == '|')
        {
            pipeCounter++;
        }

        // 将当前字符加入待处理记录
        currentRecord.Append(currentChar);

        // 当累积内容的竖线数量达到预期，判定为完整记录
        if (pipeCounter == ExpectedPipeCount)
        {
            // 替换记录内所有换行符为空格
            string cleanedRecord = currentRecord.ToString()
                .Replace(Environment.NewLine, " ")
                .Replace("\n", " ")
                .Replace("\r", " ");
            
            // 写入处理后的完整记录
            writer.WriteLine(cleanedRecord);
            
            // 重置累积器和计数器，准备处理下一条记录
            currentRecord.Clear();
            pipeCounter = 0;
        }
    }

    // 处理文件末尾可能遗留的不完整记录（若存在）
    if (currentRecord.Length > 0)
    {
        string cleanedRecord = currentRecord.ToString()
            .Replace(Environment.NewLine, " ")
            .Replace("\n", " ")
            .Replace("\r", " ");
        writer.WriteLine(cleanedRecord);
    }
}

代码关键优化点 #

• 按字符读取：避免ReadLine()截断跨多行记录，确保能拼接完整的单条记录
• 累积式处理：仅当竖线数量达到预期时，才处理并写入记录，保证记录完整性
• 单流读写：一次性打开读写文件流，大幅降低IO开销，适配60万行的大文件

导入处理后的文件 #

使用你原有的BULK INSERT语句，将路径替换为处理后的文件即可：

BULK INSERT dbo.TargetTable
FROM 'C:\Work\CleanedFile.txt'
WITH (
    FORMAT = 'CSV'
    , FIELDTERMINATOR = '|'
    , ROWTERMINATOR = '\n'
    , FIRSTROW = 2
)

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