Python高效实现Excel与TXT文本文件数据转换指南

import pandas as pd

# 读取Excel文件（自动识别第一个sheet）
df = pd.read_excel('input.xlsx')

# 保存为TXT（默认制表符分隔）
df.to_csv('output.txt', sep='\t', index=False, header=False)

import pandas as pd

# 读取TXT文件（自动推断分隔符）
df = pd.read_csv('input.txt', sep='\t')  # 明确指定分隔符更可靠

# 保存为Excel（自动创建.xlsx文件）
df.to_excel('output.xlsx', index=False)

import pandas as pd

# 读取所有sheet
excel_file = pd.ExcelFile('multi_sheet.xlsx')
all_sheets = {sheet: pd.read_excel(excel_file, sheet_name=sheet) 
              for sheet in excel_file.sheet_names}

# 将每个sheet保存为单独TXT文件
for sheet_name, df in all_sheets.items():
    df.to_csv(f'{sheet_name}.txt', sep='\t', index=False)

import pandas as pd

df = pd.read_excel('fixed_width.xlsx')

with open('output_fixed.txt', 'w') as f:
    for _, row in df.iterrows():
        # 假设需要：列1(10字符)、列2(15字符)、列3(8字符)
        line = f"{str(row['col1']):<10}{str(row['col2']):<15}{str(row['col3']):<8}\n"
        f.write(line)

import pandas as pd

chunk_size = 10000  # 每次处理1万行

# Excel转TXT（分块）
with pd.ExcelFile('large_file.xlsx') as excel:
    for i, chunk in enumerate(pd.read_excel(excel, chunksize=chunk_size)):
        chunk.to_csv(f'output_part_{i}.txt', sep='\t', index=False)

# TXT转Excel（分块合并）
all_data = []
for i in range(10):  # 假设有10个分块文件
    df = pd.read_csv(f'input_part_{i}.txt', sep='\t')
    all_data.append(df)

pd.concat(all_data).to_excel('combined_output.xlsx', index=False)

# 定义列数据类型（减少内存占用）
dtypes = {
    'ID': 'int32',
    'Name': 'string',
    'Price': 'float32',
    'Date': 'datetime64[ns]'
}

df = pd.read_csv('data.txt', sep='\t', dtype=dtypes)

import pandas as pd
from concurrent.futuses import ThreadPoolExecutor
import os

def convert_file(file_path):
    if file_path.endswith('.xlsx'):
        df = pd.read_excel(file_path)
        txt_path = file_path.replace('.xlsx', '.txt')
        df.to_csv(txt_path, sep='\t', index=False)
        return f"Converted: {file_path} → {txt_path}"

# 获取所有Excel文件
excel_files = [f for f in os.listdir() if f.endswith('.xlsx')]

# 使用4个线程并行处理
with ThreadPoolExecutor(max_workers=4) as executor:
    results = list(executor.map(convert_file, excel_files))

for result in results:
    print(result)

# 读取时指定编码
df = pd.read_csv('input.txt', sep='\t', encoding='gbk')  # 常见中文编码

# 写入时指定编码
df.to_csv('output.txt', sep='\t', encoding='utf-8-sig')  # 带BOM的UTF-8

# 读取时转换日期列
df = pd.read_excel('input.xlsx', parse_dates=['DateColumn'])

# 或读取后转换
df['DateColumn'] = pd.to_datetime(df['DateColumn'], unit='D', origin='1899-12-30')

import pandas as pd
from datetime import datetime

def process_bank_statement(txt_path):
    # 自定义读取函数（处理固定宽度）
    def parse_line(line):
        return {
            'date': line[0:8],
            'type': line[8:12],
            'amount': float(line[12:22])/100,
            'balance': float(line[22:32])/100,
            'remark': line[32:].strip()
        }
    
    # 读取TXT
    with open(txt_path, 'r', encoding='gbk') as f:
        lines = f.readlines()[1:]  # 跳过表头
    
    data = [parse_line(line) for line in lines if line.strip()]
    df = pd.DataFrame(data)
    
    # 转换日期格式
    df['date'] = pd.to_datetime(df['date'], format='%Y%m%d')
    
    # 添加分析列
    df['day_of_week'] = df['date'].dt.day_name()
    df['amount_category'] = pd.cut(df['amount'], 
                                  bins=[-1e6, -1000, 0, 1000, 1e6],
                                  labels=['大额支出','支出','收入','大额收入'])
    
    # 保存Excel
    output_path = f"processed_{datetime.now().strftime('%Y%m%d')}.xlsx"
    with pd.ExcelWriter(output_path) as writer:
        df.to_excel(writer, sheet_name='原始数据', index=False)
        
        # 添加汇总表
        summary = df.groupby(['day_of_week', 'amount_category']).size().unstack()
        summary.to_excel(writer, sheet_name='汇总分析')
    
    return output_path

# 使用示例
processed_file = process_bank_statement('bank_statement.txt')
print(f"处理完成，结果已保存至：{processed_file}")