CSV文件数据清洗与格式转换教程：Python实现行删除、分隔符及编码转换

import csv

input_file_path = 'input.csv'
output_file_path = 'output.csv'
new_header = ['column1', 'column2', 'column3', 'column4', 'column5', 'column6', 'column7', 'column8']

with open(input_file_path, 'r', encoding="utf-16") as read_file, \
     open(output_file_path, 'w', newline='', encoding="utf-8") as write_file:

    reader = csv.reader(read_file, delimiter=",")
    writer = csv.writer(write_file, delimiter=";")

    # 写入新文件头
    writer.writerow(new_header)

    # 后续处理逻辑将在此处添加

    # ... (前面的文件打开和writer.writerow代码)

    for row in reader:
        # 过滤掉只有单个字段的行，这些行通常是空行或不含分隔符的无效行
        if len(row) == 1 and not row[0].strip(): # 进一步检查是否为空字符串
            continue

        # 也可以根据更具体的模式过滤，例如只包含连字符的行
        # if len(row) == 1 and all(c == '-' for c in row[0].strip()):
        #     continue

        # 假设前10行是无效的，也可以使用enumerate硬编码跳过
        # if reader.line_num <= 10: # line_num从1开始计数
        #     continue

        # 后续的数据处理和写入逻辑将在此处添加

    # ... (前面的文件打开、writer.writerow和过滤逻辑)

    for row in reader:
        # 过滤逻辑 (同上)
        if len(row) == 1 and not row[0].strip():
            continue

        # 替换数据字段中的连字符
        new_row = [field.replace('-', ';') for field in row]

        # 将处理后的行写入输出文件
        writer.writerow(new_row)

import csv

def process_csv_file(input_path, output_path, new_header_columns):
    """
    处理CSV文件，执行以下操作：
    1. 删除前导无效行（基于字段数量判断）。
    2. 添加新的文件头。
    3. 更改分隔符从逗号(,)到分号(;)。
    4. 替换数据字段中所有的连字符(-)为分号(;)。
    5. 转换文件编码从UTF-16到UTF-8。

    Args:
        input_path (str): 输入CSV文件的路径。
        output_path (str): 输出CSV文件的路径。
        new_header_columns (list): 包含新文件头列名的列表。
    """
    try:
        with open(input_path, 'r', encoding="utf-16") as read_file, \
             open(output_path, 'w', newline='', encoding="utf-8") as write_file:

            reader = csv.reader(read_file, delimiter=",")
            writer = csv.writer(write_file, delimiter=";")

            # 1. 写入新文件头
            writer.writerow(new_header_columns)

            # 2. 逐行读取、过滤、处理和写入
            for row in reader:
                # 过滤逻辑：跳过空行或只有单个字段（且内容为空或全为连字符）的行
                # 这种过滤方式假设有效数据行至少包含两个字段
                # 或者包含非空白的单个字段
                if len(row) == 0: # 真正的空行，csv.reader可能解析为[]
                    continue
                if len(row) == 1 and not row[0].strip(): # 只有一个字段且内容为空白
                    continue
                if len(row) == 1 and all(c == '-' for c in row[0].strip()): # 只有一个字段且全为连字符
                    continue

                # 3. 替换数据字段中的连字符
                processed_row = [field.replace('-', ';') for field in row]

                # 4. 写入处理后的行
                writer.writerow(processed_row)

        print(f"CSV文件处理完成：'{input_path}' 已转换为 '{output_path}'")
    except FileNotFoundError:
        print(f"错误：文件 '{input_path}' 未找到。")
    except Exception as e:
        print(f"处理文件时发生错误：{e}")

# 示例用法
input_csv_file = 'input.csv'
output_csv_file = 'output.csv'
custom_header = ['cars', 'Date', 'Daypart', 'Avg_000', 'Share'] # 根据实际数据调整列名

# 运行处理函数
process_csv_file(input_csv_file, output_csv_file, custom_header)

empty line
empty line
empty line
empty line
empty line
------------------------------------
empty line
empty line
empty line
cars,Date,Daypart,'000 (avg),Share (excluded: Other))
car1,2022-01-01,02:00-02:00,24.459,4.3
car1,2022-01-01,02:01-02:01,20.967,3.7
car1,2022-01-01,02:02-02:02,20.967,3.8
car1,2022-01-01,02:03-02:03,13.029,2.3
car1,2022-01-01,02:04-02:04,13.029,2.3
car1,2022-01-01,02:05-02:05,10.621,1.9
car1,2022-01-01,02:06-02:06,10.621,2.0
car1,2022-01-01,02:07-02:07,10.621,2.0
car1,2022-01-01,02:08-02:08,10.621,2.0