如何高效将5GB级SQL日志文件转换为CSV格式？

Got it, dealing with a 5GB log file that has repeating headers and separator lines is no small feat—especially since you can’t ask the client to re-export it. The key here is using tools that handle large files efficiently without hogging your system’s memory. Let’s walk through the best approaches:

方法1：使用awk（推荐，高效处理大文件） #

Awk is built for text processing and excels at handling large files line-by-line, which makes it perfect for your 5GB log. Here’s a script that will strip out duplicate headers/separators and convert the data to CSV:

完整脚本（保存为convert.awk） #

BEGIN {
    # 设置输入字段分隔符为空格（自动忽略连续空格），输出分隔符为逗号
    FS = " "
    OFS = ","
}

# 匹配表头行：只在第一次遇到时输出，之后跳过
/^Header 1 Header 2 Header 3 Header 4$/ {
    if (!printed_header) {
        print $1, $2, $3, $4
        printed_header = 1
    }
    next
}

# 匹配分隔线行：直接跳过
/^-------- -------- -------- --------$/ {
    next
}

# 处理数据行：转换为CSV格式输出
{
    print $1, $2, $3, $4
}

运行命令 #

awk -f convert.awk your_input.log > output.csv

如果你不想单独保存脚本，也可以直接用一行命令：

awk 'BEGIN { FS=" "; OFS="," } /^Header 1 Header 2 Header 3 Header 4$/ { if (!printed_header) { print $1,$2,$3,$4; printed_header=1; next } } /^-------- -------- -------- --------$/ { next } { print $1,$2,$3,$4 }' your_input.log > output.csv

方法2：使用Python脚本（适合自定义逻辑） #

If you need more flexibility (like handling edge cases with special characters), a Python script that reads the file line-by-line will work without loading the entire 5GB into memory:

import sys

def log_to_csv(input_path, output_path):
    printed_header = False
    with open(input_path, 'r') as infile, open(output_path, 'w') as outfile:
        for line in infile:
            line = line.strip()
            # 跳过空行
            if not line:
                continue
            # 处理表头：只输出一次
            if line == "Header 1 Header 2 Header 3 Header 4":
                if not printed_header:
                    outfile.write(line.replace(' ', ',') + '\n')
                    printed_header = True
                continue
            # 跳过分隔线
            if line == "-------- -------- -------- --------":
                continue
            # 转换数据行为CSV
            outfile.write(line.replace(' ', ',') + '\n')

if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("Usage: python convert.py input.log output.csv")
        sys.exit(1)
    log_to_csv(sys.argv[1], sys.argv[2])

运行命令 #

python convert.py your_input.log output.csv

关键注意事项 #

• 处理特殊字符：如果你的数据字段包含逗号或空格，你’ll need to wrap fields in double quotes to keep CSV valid. For example, modify the awk script to print quoted fields:

  BEGIN { FS=" "; OFS="," }
  /^Header 1 Header 2 Header 3 Header 4$/ {
      if (!printed_header) {
          for (i=1; i<=NF; i++) printf "\"%s\"%s", $i, (i==NF?"\n":OFS)
          printed_header = 1
      }
      next
  }
  /^-------- -------- -------- --------$/ { next }
  {
      for (i=1; i<=NF; i++) printf "\"%s\"%s", $i, (i==NF?"\n":OFS)
  }
  

• 验证输出：After conversion, spot-check the CSV to ensure headers are only present once and data lines are correctly formatted.

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