Python实现从Excel取值修改拆分后TXT文件名格式

现有Python脚本已实现以下功能：

• 将指定目录下的大TXT文件（如TABNAME_C.txt、TABNAME_D.txt）按每1000行拆分，生成TABNAME_C_1.txt、TABNAME_C_2.txt这类拆分文件
• 能读取包含TABNAME、SCHEMA、QUERY列的Excel文件，并将对应QUERY写入拆分后的文件

待实现功能：当拆分后文件的TABNAME1（从文件名提取的前缀）与Excel中的TABNAME匹配时，将拆分文件重命名为SCHEMA_TABNAME_C_1.txt的格式。

import re
import os
import shutil
import pandas as pd

lines_per_file = 1000
smallfile = None

root_path = 'D:/xlsx/WWW/'

if os.path.exists(root_path):
    files = []
    for name in os.listdir(root_path):
        if os.path.isfile(os.path.join(root_path, name)):
            files.append(os.path.join(root_path, name))
    # print(files) #list all files in directory

    for ii in files:
        if ii.endswith(".txt"):  # only txt files
            with open(ii, 'r') as bigfile:
                b = os.path.abspath(ii)
                print('name2', b)
                name1 = str(os.path.basename(ii).split(".")[0])
                name2 = str(name1 + '_{}.txt')
                #
                print('name1', name1)
                for lineno, line in enumerate(bigfile):
                    if lineno % lines_per_file == 0:
                        if smallfile:
                            smallfile.close()
                        small_filename = name2.format(lineno // lines_per_file + 1)
                        smallfile = open(os.path.join(root_path, small_filename), "w")
                    smallfile.write(line)
                if smallfile:
                    smallfile.close()

if os.path.exists(root_path):
    files = []
    for name in os.listdir(root_path):
        if os.path.isfile(os.path.join(root_path, name)):
            files.append(os.path.join(root_path, name))
    print('files', files)  # list all files in directory

    # removing empty newlines
    for bb in files:
        if bb.endswith(".txt"):  # only txt files
            with open(bb, 'r') as f:
                content = f.read()
                f.close()
                content1 = content.split("\n")
                s = "\n".join(content1[:-1])
                f = open(bb, "w+")
                for i in range(len(s)):
                    f.write(s[i])
                f.close()

            with open(bb, 'r') as p:
                content = p.read()
                p.close()
                file = []
                name1 = str(os.path.basename(bb).rsplit('_', 1)[0])
                print("NAME1", name1)
                file.append(name1)
                dd = pd.DataFrame(file, columns=['TABNAME1'])
                print(dd)
                df = pd.read_excel('D:/xlsx/FILE.xlsx')
                print(df)
                c = pd.merge(dd, df, how='inner', left_on='TABNAME1', right_on='TABNAME')
                g = c.loc[:, "SCHEMA"]

import os
import re
import pandas as pd

lines_per_file = 1000
root_path = 'D:/xlsx/WWW/'
excel_path = 'D:/xlsx/FILE.xlsx'

# 提前读取Excel并构建TABNAME到SCHEMA的映射，避免重复读取
try:
    df_excel = pd.read_excel(excel_path)
    tabname_schema_map = dict(zip(df_excel['TABNAME'], df_excel['SCHEMA']))
    tabname_query_map = dict(zip(df_excel['TABNAME'], df_excel['QUERY']))
except Exception as e:
    print(f"读取Excel失败: {str(e)}")
    exit()

# 步骤1：拆分大TXT文件
if os.path.exists(root_path):
    # 获取原始大TXT文件（排除已拆分的带数字后缀的文件）
    raw_txt_files = [
        os.path.join(root_path, f) for f in os.listdir(root_path)
        if os.path.isfile(os.path.join(root_path, f)) 
        and f.endswith('.txt') 
        and not re.search(r'_\d+\.txt$', f)
    ]

    for big_file in raw_txt_files:
        file_prefix = os.path.basename(big_file).split('.')[0]
        smallfile = None

        with open(big_file, 'r', encoding='utf-8') as f:
            for lineno, line in enumerate(f, start=1):
                # 每1000行生成新文件
                if lineno % lines_per_file == 1:
                    if smallfile:
                        smallfile.close()
                    small_filename = f"{file_prefix}_{(lineno // lines_per_file) + 1}.txt"
                    smallfile = open(os.path.join(root_path, small_filename), 'w', encoding='utf-8')
                
                # 跳过空行，避免无效内容
                if line.strip():
                    smallfile.write(line)
            
            if smallfile:
                smallfile.close()

# 步骤2：重命名拆分文件并写入QUERY
if os.path.exists(root_path):
    # 获取所有拆分后的TXT文件
    split_files = [
        os.path.join(root_path, f) for f in os.listdir(root_path)
        if os.path.isfile(os.path.join(root_path, f)) 
        and f.endswith('.txt') 
        and re.search(r'_\d+\.txt$', f)
    ]

    for split_file in split_files:
        file_basename = os.path.basename(split_file)
        # 提取用于匹配的TABNAME1
        tabname1 = file_basename.rsplit('_', 1)[0]

        # 匹配SCHEMA并执行重命名
        if tabname1 in tabname_schema_map:
            schema = tabname_schema_map[tabname1]
            new_filename = f"{schema}_{file_basename}"
            new_file_path = os.path.join(root_path, new_filename)

            if not os.path.exists(new_file_path):
                os.rename(split_file, new_file_path)
                print(f"已重命名: {file_basename} -> {new_filename}")
            else:
                print(f"文件已存在，跳过重命名: {new_filename}")
            
            # 将对应的QUERY写入文件（示例：写入文件开头）
            query = tabname_query_map[tabname1]
            with open(new_file_path, 'r+', encoding='utf-8') as f:
                content = f.read()
                f.seek(0, 0)
                f.write(f"{query}\n{content}")
        else:
            print(f"未找到匹配的TABNAME，跳过: {tabname1}")

• 提前读取Excel映射：一次性读取Excel并构建TABNAME到SCHEMA、QUERY的字典映射，避免循环中重复读取文件，大幅提升效率
• 拆分逻辑优化：拆分时自动排除已拆分的小文件，防止重复处理；同时直接跳过空行，简化后续空行处理步骤
• 重命名逻辑：通过字典快速匹配SCHEMA，直接生成目标文件名并执行重命名
• QUERY写入整合：重命名完成后直接写入对应QUERY，无需单独处理文件内容

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