超大型数据文件字符替换最快方法——Perl脚本优化求助

问题背景 #

使用Windows 11系统，需处理含1900万行的超大型CSV文件，目标是去除双引号内的逗号以便导入SQL Server 19。当前采用Perl脚本处理速度极慢，尝试PowerShell时正则表达式无法正常运行，寻求更快的处理方案。

现有Perl代码如下：

#! /usr/bin/perl -w
use strict;
use warnings;

my $SRC = $ARGV[0];
my $DES = $ARGV[1];
open(SRC,'<',$SRC) or die $!;
open(DES,'>',$DES) or die $!;

my $n_fields = "";
while (<>) {
    s/\s+$//;
    if (/^\#/) { # header line
        my @t = split(/,/);
        $n_fields = scalar(@t); # total number of fields
    } else { # actual data
        my $n_commas = $_ =~s/,/,/g; # total number of commas
        foreach my $i (0 .. $n_commas - $n_fields) { # iterate ($n_commas - $n_fields + 1) times
            s/(\\"[^\\",]+),([^\\"]+\\"/$1\\"$2/g; # single replacement per previous answers
        }
        s/\\"//g; # removal of double quotes (if you want)
    }
    print DES "$_\n";
}

close (DES);
close (SRC);

现有Perl脚本的性能瓶颈 #

1. 循环替换效率极低：脚本需要循环$n_commas - $n_fields次替换双引号内的逗号，每一行重复扫描多次，1900万行的规模下会造成巨大的性能损耗。
2. 正则表达式不严谨：现有正则仅能匹配双引号内无嵌套、逗号前后为非引号/非逗号字符的场景，遇到复杂格式（如引号内连续逗号、逗号在引号首尾）会失效，还会导致不必要的重复扫描。
3. 文件IO未优化：Windows下默认文件缓冲较小，未启用高效IO模式，进一步拖慢处理速度。

优化方案 #

方案1：重构Perl脚本（大幅提速） #

通过一次正则扫描处理所有双引号内的逗号，避免循环，同时优化文件读写：

#! /usr/bin/perl -w
use strict;
use warnings;
use autodie; # 自动处理文件打开错误

my ($src, $des) = @ARGV;
open my $src_fh, '<:raw:perlio', $src; # 开启高效IO模式
open my $des_fh, '>:raw:perlio', $des;

my $n_fields;

while (my $line = <$src_fh>) {
    chomp $line;
    $line =~ s/\s+$//;

    if ($line =~ /^\#/) {
        my @t = split(/,/, $line);
        $n_fields = scalar(@t);
        print $des_fh "$line\n";
        next;
    }

    # 一次扫描替换双引号内所有逗号为占位符（这里用|，可根据需求调整）
    $line =~ s/"([^"]*)"/do { my $s = $1; $s =~ s/,/|/g; "\"$s\"" }/ge;
    # 移除所有双引号（按需保留）
    $line =~ s/"//g;

    print $des_fh "$line\n";
}

close $src_fh;
close $des_fh;

核心优化点：

• 用/ge修饰符实现单次扫描处理所有双引号内的逗号，彻底消除循环开销
• 启用:raw:perlio模式提升文件读写效率
• 正则逻辑更严谨，支持双引号内任意内容（若文件存在嵌套引号，可调整正则适配CSV标准的""转义格式）

方案2：PowerShell正确处理正则 #

调整正则适配PowerShell引擎，同时用StreamReader/StreamWriter提升大文件处理速度：

$srcPath = "input.csv"
$desPath = "output.csv"

# 读取表头获取字段数
$header = Get-Content $srcPath -TotalCount 1
$nFields = ($header -split ',').Count

# 用流处理提升大文件读写效率
$reader = New-Object System.IO.StreamReader($srcPath)
$writer = New-Object System.IO.StreamWriter($desPath)

while ($line = $reader.ReadLine()) {
    $line = $line.TrimEnd()
    if ($line.StartsWith('#')) {
        $writer.WriteLine($line)
        continue
    }

    # 回调函数处理双引号内的逗号替换
    $line = [regex]::Replace($line, '"([^"]*)"', {
        param($match)
        $inner = $match.Groups[1].Value
        $inner -replace ',', '|' # 替换为占位符，按需调整
    })
    # 移除双引号
    $line = $line -replace '"', ''

    $writer.WriteLine($line)
}

$reader.Close()
$writer.Close()

方案3：直接用SQL Server自带工具（跳过预处理） #

SQL Server的BULK INSERT或SSIS本身支持解析带引号的CSV，无需提前预处理，速度最快：

1. BULK INSERT示例：

BULK INSERT YourTargetTable
FROM 'C:\path\to\your\data.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2, -- 若第一行为表头则跳过
    QUOTED_IDENTIFIER = ON, -- 启用引号解析，自动忽略引号内的逗号
    CODEPAGE = '65001' -- UTF-8编码，GBK编码用'936'
);

2. SSIS导入：
   打开SQL Server Data Tools创建SSIS包，使用「平面文件源」，在平面文件连接管理器的「高级」选项卡中设置文本限定符为"，SSIS会自动正确解析字段，忽略引号内的逗号。

方案4：用Gawk处理（Windows下可安装GnuWin32） #

Gawk处理大文件速度极快，适合纯文本格式处理：

BEGIN {
    FS = ","
    OFS = ","
}
/^#/ {
    print $0
    n_fields = NF
    next
}
{
    in_quote = 0
    new_line = ""
    for (i=1; i<=NF; i++) {
        if ($i ~ /^"/) {
            in_quote = 1
            buf = $i
        } else if (in_quote) {
            buf = buf "," $i
            if ($i ~ /"$/) {
                in_quote = 0
                gsub(/,/, "|", buf) # 替换内部逗号为占位符
                gsub(/"/, "", buf) # 移除引号
                new_line = new_line buf OFS
            }
        } else {
            gsub(/"/, "", $i)
            new_line = new_line $i OFS
        }
    }
    sub(OFS "$", "", new_line) # 移除末尾多余分隔符
    print new_line
}

使用方式：安装GnuWin32后，执行gawk -f process.awk input.csv > output.csv

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