0

I want to converting the text files to Excel .xls files using python. I have used existing python script by seeing this website. but it is showing something like that more than 65656 records is not supported and.xls format not supported. Can anyone help me for this one?

Here is existing python script for converting text file to excel file:

mypath ='S://Input'
from os import listdir
from os.path import isfile, join
import xlwt
import xlrd

textfiles = [join(mypath,f) for f in listdir(mypath)
                if isfile(join(mypath,f)) and '.txt' in  f]
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'))
martineau
  • 119,623
  • 25
  • 170
  • 301
user 98
  • 167
  • 2
  • 12
  • Read them in with [`pandas`](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html) and save them to Excel with [`df.to_excel`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html) – Trenton McKinney Oct 15 '19 at 18:44
  • Try using a different third-party module, such as [OpenPyXL](https://openpyxl.readthedocs.io/en/stable/). – martineau Oct 15 '19 at 18:49
  • If you don't have a specific need for .xls over .xlsx, I'd suggest an alternate module (as well as those mentioned, consider [xlsxwriter](https://xlsxwriter.readthedocs.io/)). If you do need .xls, and input file is over 65656 row, you'll have to work around that (limit of .xls format). – tegancp Oct 15 '19 at 19:46

1 Answers1

0

See this question Here for reading data from a txt file into a pandas dataframe.

You can use the following code

data = pd.read_csv(FullFilePath, sep="\t", header=None)
data.columns = ["a", "b", "c", "etc."]

To read your file into a data frame

Then once the data is in a data frame, use the to_excel method linked here

with the code:

data.to_excel(FullOutputPath, index = False)
DBA108642
  • 1,995
  • 1
  • 18
  • 55