如何用Python批量处理杂乱CSV文件并转换为可读取格式？

Hey there! I totally feel your pain—dealing with messy CSV files that Excel handles effortlessly but Python chokes on is the worst. Let’s break down how to automate that Excel "Save As UTF-8 CSV" process without manually clicking through hundreds of files, then merge everything into a single DataFrame.

First, Understand the Problem #

Your messy CSV uses spaces between quoted fields as separators (like "Field1" "Field2" instead of "Field1","Field2") and likely has encoding quirks. Excel’s CSV parser is super forgiving, which is why it works when you open it manually. We need to replicate that logic in Python.

Method 1: Pure Python (No External Tools) #

This uses Python’s built-in csv module to properly parse quoted fields, then restructures the data into a standard CSV. Perfect if you want full control.

Step-by-Step Code #

import csv
import os

def convert_single_messy_csv(input_path, output_path):
    # Read the messy CSV and parse quoted fields separated by spaces
    with open(input_path, 'r', encoding='utf-8', errors='replace') as f:
        reader = csv.reader(f, delimiter=' ', quotechar='"')
        # Flatten all fields (in case the file is one long line)
        all_fields = [field.strip() for row in reader for field in row if field.strip()]
    
    # Split into device info, headers, and data rows
    # From your example, there are 29 data columns (count headers in your normal CSV)
    num_data_columns = 29
    device_info = all_fields[0]
    headers = all_fields[1 : 1 + num_data_columns]
    # Group remaining fields into rows of 29 columns each
    data_rows = [all_fields[i : i + num_data_columns] for i in range(1 + num_data_columns, len(all_fields), num_data_columns)]
    
    # Write the cleaned CSV
    with open(output_path, 'w', encoding='utf-8', newline='') as f:
        writer = csv.writer(f)
        # First row: device info + empty columns to match header count
        writer.writerow([device_info] + [''] * (num_data_columns - 1))
        # Header row
        writer.writerow(headers)
        # Data rows
        writer.writerows(data_rows)

# Batch convert all CSVs in a folder
def batch_convert_messy_csvs(input_folder, output_folder):
    os.makedirs(output_folder, exist_ok=True)
    for filename in os.listdir(input_folder):
        if filename.lower().endswith('.csv'):
            input_path = os.path.join(input_folder, filename)
            output_path = os.path.join(output_folder, f"cleaned_{filename}")
            convert_single_messy_csv(input_path, output_path)
            print(f"Converted: {filename}")

Pros & Cons #

• ✅ No external dependencies (uses Python’s standard library)
• ✅ Full control over how fields are parsed
• ❌ You need to know the exact number of data columns (count from your normal CSV)
• ❌ Might need tweaks if your files have varying column counts

Method 2: Use in2csv (CSVKit Tool) #

in2csv is part of the csvkit library, built specifically to convert messy, non-standard CSV files into valid ones. It’s super easy to use.

Step 1: Install CSVKit #

pip install csvkit

Step 2: Convert via Command Line or Python #

Command Line (Quickest)

in2csv --delimiter " " --quotechar '"' path/to/messy.csv > path/to/cleaned.csv

Python Script (For Batch Processing)

import subprocess
import os

def batch_convert_with_in2csv(input_folder, output_folder):
    os.makedirs(output_folder, exist_ok=True)
    for filename in os.listdir(input_folder):
        if filename.lower().endswith('.csv'):
            input_path = os.path.join(input_folder, filename)
            output_path = os.path.join(output_folder, f"cleaned_{filename}")
            # Run in2csv command
            cmd = [
                'in2csv',
                '--delimiter', ' ',
                '--quotechar', '"',
                input_path
            ]
            with open(output_path, 'w', encoding='utf-8') as f:
                subprocess.run(cmd, stdout=f, check=True)
            print(f"Converted: {filename}")

Pros & Cons #

• ✅ Handles most non-standard CSV cases automatically
• ✅ No need to hardcode column counts
• ❌ Requires installing an external library
• ❌ Less control over the output structure compared to pure Python

Method 3: Automate Excel (Exact Replica of Manual Process) #

If the above methods fail, this is your fallback—we’ll use Python to control Excel and replicate the "Save As UTF-8 CSV" action exactly like you do manually. Works for any CSV Excel can open.

Step-by-Step Code #

import win32com.client
import os

def convert_with_excel(input_path, output_path):
    # Launch Excel in background
    excel = win32com.client.Dispatch("Excel.Application")
    excel.Visible = False
    try:
        # Open the messy CSV (Local=True handles regional settings)
        workbook = excel.Workbooks.Open(input_path, Local=True)
        # Save as UTF-8 CSV (FileFormat=62 is the code for UTF-8 CSV)
        workbook.SaveAs(
            Filename=output_path,
            FileFormat=62,
            Local=True
        )
        workbook.Close(SaveChanges=False)
    finally:
        # Make sure Excel closes even if there's an error
        excel.Quit()

# Batch convert all CSVs in a folder
def batch_convert_with_excel(input_folder, output_folder):
    os.makedirs(output_folder, exist_ok=True)
    for filename in os.listdir(input_folder):
        if filename.lower().endswith('.csv'):
            input_path = os.path.join(input_folder, filename)
            output_path = os.path.join(output_folder, f"cleaned_{filename}")
            convert_with_excel(input_path, output_path)
            print(f"Converted: {filename}")

Pros & Cons #

• ✅ Exact same result as manually opening/saving in Excel
• ✅ Handles even the most stubborn messy CSVs
• ❌ Requires Excel installed (Windows-only for most cases)
• ❌ Slower than pure Python methods due to Excel overhead

Merge All Cleaned CSVs into One DataFrame #

Once you have all cleaned CSVs, merging them is straightforward with pandas:

import pandas as pd
import os

def merge_cleaned_csvs(cleaned_folder, merged_output_path):
    dfs = []
    for filename in os.listdir(cleaned_folder):
        if filename.lower().endswith('.csv'):
            file_path = os.path.join(cleaned_folder, filename)
            # Skip the first row (device info) or keep it as a column
            df = pd.read_csv(file_path, skiprows=1)
            # Optional: Add device info as a column
            # with open(file_path, 'r') as f:
            #     device_info = f.readline().split(',')[0]
            # df['Device Info'] = device_info
            dfs.append(df)
    
    # Combine all DataFrames
    merged_df = pd.concat(dfs, ignore_index=True)
    # Save to CSV
    merged_df.to_csv(merged_output_path, index=False, encoding='utf-8')
    print(f"Merged {len(dfs)} files into {merged_output_path}")

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