使用python将文本文件转换为excel文件 [英] Convert text files to excel files using python

问题描述

我正在开发 INFORMIX 4GL 程序.该程序生成输出文本文件.这是一个输出示例:

I am working on INFORMIX 4GL programs. That programs produce output text files.This is an example of the output:

Lot No|Purchaser name|Billing|Payment|Deposit|Balance|                
J1006|JAUHARI BIN HAMIDI|5285.05|4923.25|0.00|361.80|                 
J1007|LEE, CHIA-JUI AKA LEE, ANDREW J. R.|5366.15|5313.70|0.00|52.45| 
J1008|NAZRIN ANEEZA BINTI NAZARUDDIN|5669.55|5365.30|0.00|304.25|     
J1009|YAZID LUTFI BIN AHMAD LUTFI|3180.05|3022.30|0.00|157.75|  

此文本文件可以手动转换为 excel 文件.但是，我想问一下，有没有可以将 .txt 文件转换为 .xls 文件的脚本?

This text files can manually convert to excel files.But, I wanna ask, is there any script that I can use to convert .txt files to .xls files ?

大家好，现在我已经可以使用名为 Rami Helmy 的用户提供的脚本通过 python 将文本文件转换为 Excel 文件.非常感谢他.但是现在，该脚本将生成多个 Excel 文件取决于关于|"的数量来自文本文件.除此之外，该脚本也只能转换一个文本文件.我将转换所有文本文件，而不说明文本文件的名称.因此，我正在寻找如何将此脚本转换为:

Hi all,now I'm already can convert text files to excell file by python using script that was given from user named Rami Helmy.A big thanks for him.But now,That script will produce more than one excell files depends on the number of '|' from the text files.Beside that,That script also can only convert one text files.I a going to convert all text files without state the name of text files.Therefore,I am looking such a way on how to this script going to:

• 只输出一个excel文件
• 转换用户提供的目录中的所有 .txt 文件.
• 输出excell的文件名是自动从文本文件的文件名复制过来的.

我是 python 新手，希望有人能帮我解决我的问题.谢谢..

I am new in python,hopefully someone can help me to solve my problems.Thank You..

完成了所有任务，但有一些问题..带有绿色标记的列格式为文本文件，因此我无法对该列进行任何计算.该列需要转换为数字格式.除此之外, 输出 Excel 文件包含一个正方形"符号，如下所示:

done all the task,but there was some problem..the column that had green mark is format as textfile,so I can't make any calculation on that column.That column need to convert to number format.other from that, output excell files contains an "square" symbol like this:

那么，如何在转换文件时将绿色标记的列格式设置为数字呢?我怎样才能确保没有这样的方形符号?请帮忙，谢谢...

then, how to make the green mark column format as number when convert file? and how can I ensure that there is no square symbol like that? Please help,thank you...

那个奇怪的方形符号已经消失了，但那个绿色标记仍然存在.

That strange square symbol are already gone but that green mark are still exist.

大家好.我有一个问题要问，我已经得到了 RamiHelmi 提供的脚本，但是扩展文件名会生成如下文件:

Hi all. I had one question to ask, I already got script that was given by RamiHelmi, but the extension file name will produce file such as:

tester.txt --> tester.txt.xls

tester.txt --> tester.txt.xls

因此，我如何删除.txt".在输出文件上，以便它只会产生tester.xls"文件扩展名.希望有人能帮助解决我的问题..谢谢

therefore,how can i remove the '.txt. on the output files so that it will only produce "tester.xls" files extension.Hopefully,someone can help solve my problem..thank you

推荐答案

要自动执行此操作，您可以使用此处描述的 Python 脚本:

To automate that, you can use that python script described here:

自动将 txt 转换为 xls

这是python脚本的更新版本，它将在给定目录中具有您描述的格式的所有文本文件转换为XLS文件并将它们保存在同一目录中:

Here is an updated version of the python script that will convert all the text files having the format that you described in a given directory to XLS files and save them in the same directory:

# mypath should be the complete path for the directory containing the input text files
mypath = raw_input("Please enter the directory path for the input files: ")

from os import listdir
from os.path import isfile, join
textfiles = [ join(mypath,f) for f in listdir(mypath) if isfile(join(mypath,f)) and '.txt' in  f]

def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        return False        

import xlwt
import xlrd

style = xlwt.XFStyle()
style.num_format_str = '#,###0.00'  

for textfile in textfiles:
    f = open(textfile, 'r+')
    row_list = []
    for row in f:
        row_list.append(row.split('|'))
    column_list = zip(*row_list)
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet('Sheet1')
    i = 0
    for column in column_list:
        for item in range(len(column)):
            value = column[item].strip()
            if is_number(value):
                worksheet.write(item, i, float(value), style=style)
            else:
                worksheet.write(item, i, value)
        i+=1
    workbook.save(textfile.replace('.txt', '.xls'))

编辑

上面的脚本将获取在 mypath 变量中指定的给定目录中所有文本文件的列表，然后将每个文本文件转换为名为 generated_xls0.xls 那么下一个文件将被命名为 generated_xls1.xls 等等...

The script above will get a list of all the text files in the given directory specified in mypath variable and then convert each text file to an XLS file named generated_xls0.xls then the next file will be named generated_xls1.xls etc...

编辑

在将字符串写入 XLS 文件之前对其进行剥离

strip the string before writing it to the XLS file

编辑

修改脚本以处理数字的格式

modified the script to handle the formatting of numbers

这篇关于使用python将文本文件转换为excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！