pip install pandas openpyxl 

import os
import pandas as pd

def txt_col_to_excel(
    txt_folder,       # TXT文件所在文件夾路徑
    excel_path,       # 輸出Excel文件路徑（如"result.xlsx"）
    target_col_index, # 要提取的列索引（從0開始，如第2列填1）
    sep="\s+",        # TXT文件分隔符，默認匹配任意空格（含多個空格/制表符）
    encoding="utf-8"  # TXT文件編碼（常見gbk/utf-8）
):
    """
    批量讀取TXT文件，提取指定列寫入Excel單個Sheet
    """
    # 初始化匯總數(shù)據(jù)列表
    all_data = []
    
    # 遍歷文件夾下所有TXT文件
    for filename in os.listdir(txt_folder):
        # 僅處理.txt后綴文件
        if not filename.endswith(".txt"):
            continue
        
        # 拼接完整文件路徑
        txt_path = os.path.join(txt_folder, filename)
        print(f"正在處理文件：{filename}")
        
        try:
            # 1. 讀取TXT文件為DataFrame
            # sep="," 對應逗號分隔；sep="\t" 對應制表符；sep="|" 對應豎線分隔
            df = pd.read_csv(
                txt_path,
                sep=sep,
                encoding=encoding,
                header=None,  # TXT無表頭時設為None，有表頭則設為0
                on_bad_lines="skip"  # 跳過格式錯誤的行
            )
            
            # 2. 提取指定列，添加"來源文件"列便于溯源
            target_col = df.iloc[:, target_col_index]  # iloc[:,n] 提取第n列（索引從0開始）
            temp_df = pd.DataFrame({
                "來源文件": filename,
                "目標列數(shù)據(jù)": target_col
            })
            
            # 3. 追加到匯總列表
            all_data.append(temp_df)
        
        except Exception as e:
            print(f"處理文件 {filename} 失?。簕e}")
            continue
    
    # 4. 合并所有數(shù)據(jù)并寫入Excel
    if all_data:
        final_df = pd.concat(all_data, ignore_index=True)
        # index=False 不寫入行索引；engine="openpyxl" 支持.xlsx格式
        final_df.to_excel(excel_path, sheet_name="匯總數(shù)據(jù)", index=False, engine="openpyxl")
        print(f"所有數(shù)據(jù)已寫入Excel：{excel_path}")
    else:
        print("未找到有效TXT文件或提取數(shù)據(jù)為空！")

# 示例調用
if __name__ == "__main__":
    # 配置參數(shù)（根據(jù)實際情況修改）
    TXT_FOLDER = r"E:\test_txt"  # TXT文件所在文件夾（絕對路徑）
    EXCEL_PATH = "提取結果.xlsx"  # 輸出Excel路徑
    TARGET_COL_INDEX = 1         # 提取第2列（索引從0開始）
    SEP = "\s+"                  # TXT分隔符（如逗號分隔則改為","）
    ENCODING = "utf-8"           # 若TXT亂碼，嘗試改為"gbk"
    
    # 執(zhí)行函數(shù)
    txt_col_to_excel(TXT_FOLDER, EXCEL_PATH, TARGET_COL_INDEX, SEP, ENCODING)

import os
import pandas as pd

def txt_col_to_excel_by_sheet(
    txt_folder,
    excel_path,
    target_col_index,
    sep="\s+",
    encoding="utf-8"
):
    # 創(chuàng)建Excel寫入器
    with pd.ExcelWriter(excel_path, engine="openpyxl") as writer:
        for filename in os.listdir(txt_folder):
            if not filename.endswith(".txt"):
                continue
            
            txt_path = os.path.join(txt_folder, filename)
            print(f"正在處理文件：{filename}")
            
            try:
                df = pd.read_csv(
                    txt_path,
                    sep=sep,
                    encoding=encoding,
                    header=None,
                    on_bad_lines="skip"
                )
                
                # 提取指定列，重命名列名
                target_df = df.iloc[:, [target_col_index]]  # 保留列結構
                target_df.columns = ["目標列數(shù)據(jù)"]
                
                # Sheet名：去掉.txt后綴
                sheet_name = filename.replace(".txt", "")
                # 寫入對應Sheet
                target_df.to_excel(writer, sheet_name=sheet_name, index=False)
            
            except Exception as e:
                print(f"處理文件 {filename} 失?。簕e}")
                continue
    
    print(f"所有文件已分Sheet寫入Excel：{excel_path}")

# 示例調用
if __name__ == "__main__":
    TXT_FOLDER = r"E:\test_txt"
    EXCEL_PATH = "按文件分Sheet結果.xlsx"
    TARGET_COL_INDEX = 1
    SEP = "\s+"
    ENCODING = "utf-8"
    
    txt_col_to_excel_by_sheet(TXT_FOLDER, EXCEL_PATH, TARGET_COL_INDEX, SEP, ENCODING)

import chardet
with open("test.txt", "rb") as f:
    result = chardet.detect(f.read())
print("文件編碼：", result["encoding"])  # 輸出如gbk/utf-8

df = pd.read_csv("test.txt", sep="\s+", header=None)
print("TXT文件列數(shù)：", df.shape[1])  # 輸出列數(shù)，索引范圍0~列數(shù)-1

for root, dirs, files in os.walk(txt_folder):
    for filename in files:
        if filename.endswith(".txt"):
            txt_path = os.path.join(root, filename)
            # 后續(xù)處理邏輯不變

target_df = target_df.drop_duplicates()  # 去重
target_df = target_df.dropna()           # 刪除空值行