Convert text files to excel files using python

Question

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|

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 ?

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:

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..

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:

enter image description here

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. enter image description here

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

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

There are some libraries that you could use to create excel files phpExcel, jExcel, i think every language has is own librarie (or several). — Sal00m
– Sal00m, Commented Oct 30, 2013 at 8:26
One idea is to change all | for semicolons ; and rename to .csv files so excel will be able to open it as spreadsheet. — Sal00m
– Sal00m, Commented Oct 30, 2013 at 8:27
thanks for response ,but I'm still don't know how to solve it.. — Rahim Jaafar
– Rahim Jaafar, Commented Oct 30, 2013 at 8:32
wow ! its works..thanks a lot, may god bless you Rami Helmy ! — Rahim Jaafar
– Rahim Jaafar, Commented Nov 9, 2013 at 3:42

Community · Accepted Answer · 2017-05-23 12:15:53Z

9

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

Automate conversion txt to 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'))

EDIT

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...

EDIT

strip the string before writing it to the XLS file

EDIT

modified the script to handle the formatting of numbers

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Oct 30, 2013 at 8:32

Rami

7,3201 gold badge24 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Rahim Jaafar Over a year ago

ok,thanks.. i already know how to convert manually like this.But, I finding a script that can be used for converting it, or is there any software that can be convert automatically...?

Rahim Jaafar Over a year ago

i'm sorry, wanna ask.. actually where should I paste codes above? I'm never learn script before.Please help ,thank you

Rami Over a year ago

That's a python script so you need to have Python installed. Depending on your OS, download the appropriate distribution from here: python.org/getit Or try portable Python if you don't want to install it. portablepython.com

Rahim Jaafar Over a year ago

ok thanks already installed portable python.Then , which programs which script that you give above should be pasted?

Rami Over a year ago

That means the the module is not available. Try that python distribution. It has all the libraries pre-intalled including the xlrt. store.continuum.io/cshop/anaconda

|

techraf · Accepted Answer · 2016-04-07 05:36:23Z

0

The simplest way to do this could be like this:

#coding:utf-8
import pandas as pd
import sys
input = sys.argv[1]
pd.read_csv(input, encoding='utf8', sep='\|', dtype='unicode').to_excel('output/' + input + '.xlsx', sheet_name='sheet1', index=False)

edited Apr 7, 2016 at 5:36

techraf

69.2k30 gold badges211 silver badges215 bronze badges

answered Apr 7, 2016 at 4:55

zhf

165 bronze badges

2 Comments

Mwspencer Over a year ago

This looks very simple, but can you actually use read_csv with a pike delimiter. When I tried I received the following error: 'c' engine does not support regex separators. Which I image has to do with the fact that I am trying to read a .txt not a .csv. Just wondering if there is a work around in pandas. Thanks.

oppih Over a year ago

I have got the same error: 'c' engine does not upport regex separators. And I wonder if if relates with that the txt file I'm dealing with is seperated by tab not by '|' ?

Collectives™ on Stack Overflow

Convert text files to excel files using python

2 Answers 2

10 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related