Convert text files to excel files

[rahim_1]

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|  

Hi all,now I'm already can convert text files to excell file by python using this script.

# mypath should be the complete path for the directory containing the input text files
mypath = 'C:\\Temp'

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]


import xlwt
import xlrd
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)):
            worksheet.write(item, i, column[item])
        workbook.save(join(mypath, 'generated_xls' + str(i) + '.xls'))
        i+=1

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:

• output only one excell file
• convert all .txt files from the directory that was given from user.
• output excell's file name are automaticly copied from the file name of text files.

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

[rahim_1]

Done, this is the answer..

# mypath should be the complete path for the directory containing the input text files
mypath = 'C:\\Temp'

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]


import xlwt
import xlrd
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)):
            worksheet.write(item, i, column[item])
        i+=1
    workbook.save(textfile + '.xls')