-1

I found this useful script to convert a list of .txt files to xls files. I'm still a Python newbie and having some trouble with fixing the error in my script. The script used is below:

mypath = "I give my path here"

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'))

The script produces output files for the first 10 files which are all less than 1MB. The script fails en produces the following error message for a file that is ~42MB:

 File
 "C:\Users\me\AppData\Local\Continuum\anaconda3\lib\site-packages\xlwt\Row.py",
 line 37, in __init__
     raise ValueError("row index was %r, not allowed by .xls format" % rowx)
 ValueError: row index was 65536, not allowed by .xls format

Would anyone be able to assist me with the error?

FlyingTeller
  • 17,638
  • 3
  • 38
  • 53
Atlas_Apple
  • 53
  • 8
  • 5
    Try with `.xlsx` format. `.xls` only allows a max row index of 65536 where as `.xlsx` (Excel 2007 onwards) allows 1048576 rows. – Xnkr Aug 23 '18 at 11:21
  • What do you want to happen when the input has more than 65535 rows? – Stop harming Monica Aug 23 '18 at 11:27
  • I would ideally want it to still write the output to a single file but if it exceeds the row count of 1048576 in xlsx then I'll split it into multiple files. – Atlas_Apple Aug 23 '18 at 11:30
  • the xlwt module was written for use with .xls. How would I go about changing the output to be in a .xlsx format? If I change the last line of the code to replace the .txt to .xlsx it doesn't work – Atlas_Apple Aug 23 '18 at 11:38

1 Answers1

1

You get the error message because the file format .xls only allows 65536 rows (indexing starts at 0).

You should consider using either the .xlsx format or if you need the backwards compatibility either use .xlsx as a "fallback" format if the row count exceeds 65536, or split the output into multiple files.

c4ffrey
  • 89
  • 11