将逗号分隔值拆分为固定列数的目标表

基础概念

将逗号分隔值（CSV）拆分为固定列数的目标表，通常是指将一行文本中以逗号分隔的多个值拆分并插入到具有固定列数的数据库表中。这个过程涉及到数据解析、转换和加载（ETL）。

类型

手动拆分：通过编写脚本或程序手动解析CSV文件并插入到目标表中。
工具拆分：使用ETL工具（如Apache NiFi、Talend等）自动完成数据拆分和加载。
数据库内置函数：利用数据库内置的字符串处理函数（如MySQL的SUBSTRING_INDEX）进行拆分。

应用场景

数据导入：将CSV文件导入到数据库中，用于数据分析和报表生成。
数据迁移：将旧系统的数据格式转换为新系统所需的数据格式。
数据清洗：对CSV数据进行预处理，去除无效数据，确保数据质量。

遇到的问题及解决方法

问题1：数据格式不一致

原因：CSV文件中的某些行可能包含不同数量的值，导致拆分失败。

解决方法：

在拆分前进行数据验证，确保每行的列数一致。
使用TRY...CATCH块捕获异常，并记录错误行以便后续处理。

import csv

input_file = 'data.csv'
output_file = 'output.csv'

with open(input_file, 'r') as infile, open(output_file, 'w', newline='') as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    for row in reader:
        if len(row) != 3:  # 假设目标表有3列
            print(f"Invalid row: {row}")
            continue
        writer.writerow(row)

问题2：特殊字符处理

原因：CSV文件中的某些值可能包含逗号、引号等特殊字符，导致解析错误。

解决方法：

使用CSV解析库（如Python的csv模块）自动处理特殊字符。
在读取CSV文件时指定分隔符和引号字符。

import csv

input_file = 'data.csv'
output_file = 'output.csv'

with open(input_file, 'r', newline='') as infile, open(output_file, 'w', newline='') as outfile:
    reader = csv.reader(infile, delimiter=',', quotechar='"')
    writer = csv.writer(outfile)
    for row in reader:
        writer.writerow(row)

问题3：性能瓶颈

原因：处理大量数据时，拆分和加载过程可能会非常耗时。

解决方法：

使用批量插入操作，减少数据库操作次数。
利用多线程或多进程并行处理数据。

import csv
import psycopg2

input_file = 'data.csv'
output_table = 'target_table'

conn = psycopg2.connect(database="yourdb", user="youruser", password="yourpass", host="yourhost", port="yourport")
cursor = conn.cursor()

with open(input_file, 'r') as infile:
    reader = csv.reader(infile)
    next(reader)  # 跳过表头
    batch_size = 1000
    batch = []
    for row in reader:
        batch.append(row)
        if len(batch) >= batch_size:
            cursor.executemany(f"INSERT INTO {output_table} (col1, col2, col3) VALUES (%s, %s, %s)", batch)
            batch = []
    if batch:
        cursor.executemany(f"INSERT INTO {output_table} (col1, col2, col3) VALUES (%s, %s, %s)", batch)

conn.commit()
cursor.close()
conn.close()