基于Counter列增量中断拆分并清理传感器CSV文件的技术需求

是否需要将CSV转为xlsx处理？ #

不需要直接转换为xlsx处理。CSV是纯文本格式，直接读写效率远高于xlsx（尤其是5万+行的大文件），转换会额外消耗内存和时间。无论用VBA还是Python，都可以直接对CSV文件操作，无需中转格式。

VBA完善版代码 #

以下代码修复了原代码的问题，新增了按Counter增量中断拆分的逻辑：

Sub MagExCSVClean()
    Dim wb As Workbook, newWb As Workbook
    Dim ws As Worksheet, newWs As Worksheet
    Dim InputFolderPathString As String, OutputFolderPathString As String
    Dim InputFileString As String, OutputFileBase As String
    Dim lastRow As Long, i As Long, groupStart As Long, groupNum As Integer
    Dim prevCounter As Double, currCounter As Double

    '关闭Excel后台优化，提升效率
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    On Error GoTo Cleanup '错误处理

    '选择输入文件夹
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Title = "选择包含待处理CSV文件的文件夹"
        If .Show <> -1 Then GoTo Cleanup
        InputFolderPathString = .SelectedItems(1) & "\"
    End With

    '选择输出文件夹
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Title = "选择处理后文件的保存文件夹"
        If .Show <> -1 Then GoTo Cleanup
        OutputFolderPathString = .SelectedItems(1) & "\"
    End With

    InputFileString = Dir(InputFolderPathString & "*.csv")
    Do While InputFileString <> ""
        Set wb = Workbooks.Open(Filename:=InputFolderPathString & InputFileString)
        Set ws = wb.Sheets(1)
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        OutputFileBase = Left(InputFileString, InStr(1, InputFileString, ".") - 1)
        groupNum = 1
        groupStart = 2 '表头在第1行，数据从第2行开始

        '--- 步骤1：删除日期、时间、经纬度列空白的行 ---
        '清除现有筛选
        On Error Resume Next
        ws.ShowAllData
        On Error GoTo 0
        '筛选日期(列1)、时间(列2)、纬度(列3)、经度(列4)全为空的行
        ws.Range("A1").CurrentRegion.AutoFilter _
            Field:=1, Criteria1:="", _
            Operator:=xlAnd, Field:=2, Criteria2:="", _
            Operator:=xlAnd, Field:=3, Criteria3:="", _
            Operator:=xlAnd, Field:=4, Criteria4:=""
        '删除筛选出的可见行（保留表头）
        Application.DisplayAlerts = False
        ws.Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Application.DisplayAlerts = True
        '清除筛选
        On Error Resume Next
        ws.ShowAllData
        On Error GoTo 0
        '更新删除后的最后行号
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then '无有效数据，跳过
            wb.Close savechanges:=False
            InputFileString = Dir
            GoTo NextFile
        End If

        '--- 步骤2：按Counter连续100增量拆分文件 ---
        prevCounter = ws.Cells(2, "E").Value '假设Counter在E列，需根据实际列位置调整
        For i = 3 To lastRow
            currCounter = ws.Cells(i, "E").Value
            '判断当前Counter与上一行的差值是否为100，否则拆分
            If currCounter - prevCounter <> 100 Then
                '新建工作簿，复制当前组数据
                Set newWb = Workbooks.Add(xlWBATWorksheet)
                Set newWs = newWb.Sheets(1)
                '复制表头
                ws.Rows(1).Copy newWs.Rows(1)
                '复制当前组数据行
                ws.Rows(groupStart & ":" & i - 1).Copy newWs.Rows(2)
                '保存文件
                newWb.SaveAs Filename:=OutputFolderPathString & OutputFileBase & "-" & groupNum & ".csv", FileFormat:=xlCSV
                newWb.Close savechanges:=False
                groupNum = groupNum + 1
                groupStart = i
            End If
            prevCounter = currCounter
        Next i
        '处理最后一组数据
        Set newWb = Workbooks.Add(xlWBATWorksheet)
        Set newWs = newWb.Sheets(1)
        ws.Rows(1).Copy newWs.Rows(1)
        ws.Rows(groupStart & ":" & lastRow).Copy newWs.Rows(2)
        newWb.SaveAs Filename:=OutputFolderPathString & OutputFileBase & "-" & groupNum & ".csv", FileFormat:=xlCSV
        newWb.Close savechanges:=False

        '关闭原文件，无需保存（已拆分输出）
        wb.Close savechanges:=False
NextFile:
        InputFileString = Dir
    Loop

Cleanup:
    '恢复Excel默认设置
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "处理出错：" & Err.Description
End Sub

VBA代码说明 #

• 修复原代码中ws未赋值的问题
• 优化空白行删除逻辑，同时筛选日期、时间、经纬度列全为空的行
• 新增Counter列连续增量判断：需根据实际Counter列位置调整ws.Cells(i, "E")中的列标识
• 每个连续Counter组保存为原文件名-序号.csv，自动递增序号

Python方案（大文件处理更高效） #

使用pandas库处理结构化数据，结合tkinter实现图形化文件夹选择：

import pandas as pd
import os
from tkinter import Tk, filedialog

def select_folder(title):
    """弹出文件夹选择对话框"""
    root = Tk()
    root.withdraw()
    folder_path = filedialog.askdirectory(title=title)
    return folder_path if folder_path else None

def process_csv(input_path, output_folder):
    """处理单个CSV文件：删除空白行 + 按Counter拆分"""
    # 读取CSV文件
    df = pd.read_csv(input_path)
    # 步骤1：删除日期、时间、经纬度列全为空的行（替换为你的实际列名）
    df_clean = df.dropna(subset=['Date', 'Time', 'Latitude', 'Longitude'], how='all')
    if df_clean.empty:
        return
    
    # 步骤2：按Counter连续100增量拆分
    df_clean['diff'] = df_clean['Counter'].diff()
    # 找到断点（差值不等于100的行，第一行diff为NaN，视为新组起点）
    break_points = df_clean[df_clean['diff'] != 100].index.tolist()
    # 添加最后一行的下一个索引，方便拆分最后一组
    break_points.append(df_clean.index[-1] + 1)
    
    # 拆分并保存每个组
    file_base = os.path.splitext(os.path.basename(input_path))[0]
    group_num = 1
    for i in range(len(break_points)-1):
        start_idx = break_points[i]
        end_idx = break_points[i+1]
        group_df = df_clean.loc[start_idx:end_idx-1].drop(columns=['diff'])
        output_path = os.path.join(output_folder, f"{file_base}-{group_num}.csv")
        group_df.to_csv(output_path, index=False)
        group_num += 1

def main():
    input_folder = select_folder("选择包含待处理CSV文件的文件夹")
    if not input_folder:
        print("未选择输入文件夹，程序退出")
        return
    output_folder = select_folder("选择处理后文件的保存文件夹")
    if not output_folder:
        print("未选择输出文件夹，程序退出")
        return
    
    # 遍历输入文件夹下的所有CSV文件
    for filename in os.listdir(input_folder):
        if filename.lower().endswith('.csv'):
            input_path = os.path.join(input_folder, filename)
            print(f"正在处理：{filename}")
            process_csv(input_path, output_folder)
    print("所有文件处理完成！")

if __name__ == "__main__":
    main()

Python代码说明 #

• 用tkinter实现图形化文件夹选择，无需手动输入路径
• 通过dropna删除指定列全为空的行，需替换为你的实际列名
• 计算Counter列差值，找到增量不为100的断点，以此拆分连续组
• 每个组保存为原文件名-序号.csv，自动处理序号递增
• 5万行数据直接读取无压力，若内存不足可改用read_csv(chunksize=10000)分块处理

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