excel转换 txt竖线分隔符 为 xlsx

2023年4月10日 星期一 更新

def txt_to_csv(txt_path):

    # 打开txt文件并读取内容
    with open(txt_path, 'r', encoding='utf-8') as file:
        txt_data = file.read()

    # 将txt内容按行分割，然后按竖线分隔符分割每行数据，并将其转换为列表
    txt_list = [line.split('|') for line in txt_data.split('\n')]

    csv_path = txt_path.replace('.txt', '.csv')
    # 将列表写入csv文件
    with open(csv_path, 'w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        writer.writerows(txt_list)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

import csv
import openpyxl

txt_path = r'C:\Users\admin\Documents\SAP\SAP GUI\i.txt'

# 打开 txt 文件并读取数据
with open(txt_path, 'r', encoding='utf-8') as f:
    reader = csv.reader(f, delimiter='|')
    data = list(reader)

# 创建 Excel 文件并写入数据
wb = openpyxl.Workbook()
ws = wb.active
for row in data:
    ws.append(row)
wb.save('filename.xlsx')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

import win32com.client, time


xlApp = win32com.client.DispatchEx("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = 0
# 宏所在的xls
wb = xlApp.Workbooks.OpenText(Filename= r"E:\code\dock_project\tiptop\1.txt",
    Origin= 950, # 指定的编码,繁体中文是950
    OtherChar = "|",
        DataType = 1,
        )
# wb.SaveAs(r"E:\code\dock_project\tiptop\1.xlsx", FileFormat=51)
xlApp.ActiveWorkbook.SaveAs( Filename = r"E:\code\dock_project\tiptop\1.xlsx",
        FileFormat = 51)

xlApp.Quit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

vba 录屏 转成 python 代码

参考
https://learn.microsoft.com/zh-cn/office/vba/api/excel.workbooks.opentext