将许多复杂的txt文件转换为excel.xls并将其名称保存在python脚本中 [英] Convert many complex txt files into excel.xls and save their names in python script

问题描述

我在同一文件夹中有许多具有标准格式的txt文件:

I have many txt files with the standard format in the same folder:

Name: 321; 
Score:100; Used Time: 1:09:308;
GTime: 6/28/2024 10:04:18 PM;
Core Version : 21.0.0.0;
Software Version : 21.0.0.0;
AppID: 0S0; MapDispName: Future City; MapName:MapName123;
Key:A0000-abcde-Q0000-F0000-00H00;  REG Date : 2/27/2021 1:16:34 PM; Expiry : 7/7/2024 12:00:00 AM

我想使用python脚本将这些文本文件转换为excle.xls(表).同时，我也想保存文本文件名.

I would like to convert those text files into an excle.xls (table) using a python script. At the same time, I would like to save text filenames as well.

Team ID, Result, Used Time,Software Ver, Core Ver, AppID, Key, REG Date, Expiry,MapName,TXTName
321,100,69.308s,21.0.0.0,21.0.0.0,0S0,A0000-abcde-Q0000-F0000-00H00,2/27/2021 1:16:34 PM,7/7/2024 12:00:00 AM,MapName123,TXTName1

我的部分代码如下，但无法正常工作. TypeError:倒数第二行中+:"dict"和"str" 不受支持的操作数类型.

Part of my code as below, but it's not working. TypeError: unsupported operand type(s) for +: 'dict' and 'str' in the penultimate line.

list_drr=[]
xls_name=None
for path, file_dir, files in os.walk(file_name_path):
    for file_name in files:
        list_drr.append(os.path.join(path, file_name))
    for dir in file_dir:
        list_drr.append(os.path.join(path, dir))#

excel_data= ExcelData(xls_name,"")
excel_datas= excel_data.readExcel()
print(excel_datas)
excel_header=['Team ID', 'Result', 'Used Time', 'Software Ver', 'Core Version', 'AppID', 'Key', 'REG Date', 'Expiry','MapName','TXTName']

file= WFile(excel_header)
for drr in list_drr:
    file_datas= getFileData(drr)
    file_datas=file_datas[:7]
    data_list=[]
    for  data in file_datas:
        lis= data.split(";")
        for li in lis:
            data_list.append(li)

    data_dic={}
    for data in data_list:
        pos= data.find(":")
        ddq=data[:pos].strip()
        data_dic[ddq]=data[pos+1:].strip()
    file.write((data_dic) + (os.path.basename(file_name)))
file.save("excel.xls")

请告知我该怎么办，谢谢.

Please advise what should I do, thanks.

如下更新了wri_file.py.

import xlwt

class WFile():
    def __init__(self,head_name):
        super().__init__()
        self.head_name=head_name
        self.index=0
        self. workbook =xlwt. Workbook(encoding='utf-8')
        self. worksheet =   self.workbook.add_sheet('Sheet1')
        self.line =len( self.head_name)

        for i in range( self.line):
            self.worksheet.write( self.index,i ,self.head_name[i])

        self.index+=1

    def write(self,d):
        for i in range( self.line):
            name=d.get(self.head_name[i])
            self.worksheet.write( self.index,i ,(name))
        self.index += 1

    def writes(self, d):
        for i in range(self.line):
            self.worksheet.write(self.index, i,d[i])
        self.index += 1

    def save(self,name):
        self.workbook.save(name)

推荐答案

您正试图添加 file_name 作为要写入的另一个条目.构建字典后，一种更好的方法是在编写之前添加新的键值对:

You are trying to add file_name as another entry to be written. As you have constructed a dictionary, a better approach would be to add a new key value pair before writing:

data_dic['TXTName'] = os.path.basename(drr)
file.write(data_dic)

由于您的输出标头与文本文件中的键不一致，因此您需要创建一个字典在两者之间进行映射，例如 field_mapping

As your output header is not consistent with the keys in your text files, you need to create a dictionary to map between the two, e.g. field_mapping

您还将目录名存储到文件列表中吗?一种更简单的方法是只使用 glob.glob()，它也可以递归工作，并且如果需要，也可以仅在 .txt 文件上使用.

You were also storing directory names into your list of file? A simpler approach is to just use glob.glob() which also can work recursively and also just on .txt files if needed.

import glob
import xlwt

# {Your header : Key in text file}
field_mapping = {
    'Team ID' : 'Name',
    'Result' : 'Score',
    'Used Time' : 'Used Time',
    'Core Version' : 'Core Version',
    'Software Ver' : 'Software Version',
    'MapName' : 'MapName',
    'AppID' : 'AppID',
    'Key' : 'Key',
    'REG Date' : 'REG Date',
    'Expiry' : 'Expiry',
    'TXTName' : 'TXTName',
}

def getFileData(drr):
    with open(drr) as f_input  :
        return f_input.readlines()

class WFile():
    def __init__(self,head_name):
        super().__init__()
        self.head_name = head_name
        self.index = 0
        self.workbook = xlwt.Workbook(encoding='utf-8')
        self.worksheet = self.workbook.add_sheet('Sheet1')
        self.line = len(self.head_name)

        for i in range(self.line):
            self.worksheet.write(self.index, i, self.head_name[i])

        self.index += 1

    def write(self, d):
        for i, v in enumerate(self.head_name):
            name = d.get(field_mapping[v])
            self.worksheet.write(self.index, i, (name))
        self.index += 1

    def save(self,name):
        self.workbook.save(name)


file_name_path = r"\my file\location\*.txt"    # or just * for all files
excel_header = ['Team ID', 'Result', 'Used Time', 'Software Ver', 'Core Version', 'AppID', 'Key', 'REG Date', 'Expiry','MapName','TXTName']
file = WFile(excel_header)

for file_name in glob.glob(file_name_path, recursive=True):
    file_datas = getFileData(file_name)
    file_datas = file_datas[:7]
    data_list = []

    for data in file_datas:
        for li in data.split(";"):
            data_list.append(li)

    data_dic = {}

    for data in data_list:
        pos = data.find(":")
        ddq = data[:pos].strip()
        data_dic[ddq] = data[pos+1:].strip()

    data_dic['TXTName'] = os.path.basename(file_name)
    file.write(data_dic)

file.save("excel.xls")

为您提供旧式输出的 .xls 文件:

Giving you an old style output .xls file of:

您应该考虑将更新的 .xlsx 格式与 openpyxl 之类的库一起使用.

You should consider using the newer .xlsx format, with a library such as openpyxl.

import openpyxl
from itertools import islice
import glob
import os

field_mapping = {
    'Name' : 'Team ID',
    'Score' : 'Result',
    'Used Time' : 'Used Time',
    'Core Version' : 'Core Ver',
    'Software Version' : 'Software Ver',
    'AppID' : 'AppID',
    'Key' : 'Key',
    'REG Date' : 'REG Date',
    'Expiry' : 'Expiry',
    'TXTName' : 'TXTName'
}

wb = openpyxl.Workbook()
ws = wb.active
ws.append(list(field_mapping.values()))     # write a header

for filename in glob.glob('/my folder/*.txt', recursive=True):
    key_values = {}

    with open(filename) as f_input:
        for field in ''.join(islice(f_input, 0, 7)).replace('\n', '').split(';'):
            key, value = map(str.strip, field.split(':', 1))    # only split on the first colon
            key_values[field_mapping.get(key, None)] = value

    key_values['TXTName'] = os.path.basename(filename)
    output_row = [value for key, value in key_values.items() if key]    # Ignore key=None
    ws.append(output_row)

wb.save('excel.xlsx')

这篇关于将许多复杂的txt文件转换为excel.xls并将其名称保存在python脚本中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！