1

i have a file contain more than 950,000 rows , When I convert file from Text to xls, it gives this error during conversion. How can I modify the code to resolve this error:

ValueError: row index was 65536, not allowed by .xls format

Also if i convert the text file to xlsx format it gives the same error

Any guidance would be greatly appreciated.

How do I edit the code to resolve this error?

import xlwt
import io

book = xlwt.Workbook()
ws = book.add_sheet('First Sheet')  # Add a sheet

with io.open("data.txt", mode="r", encoding="utf-8") as f:
for row_idx, row in enumerate(f):
    row = row.rstrip()

 ws.write(row_idx, 0, row[0:8])
.
.
.
  # and so on...

book.save("excelfile.xls")# if i edit book.save("excelfile.xlxs") it gives the same error
AHMAD ALJAMAL
  • 21
  • 4
  • 1
    Possible duplicate of [ValueError: row index was 65536, not allowed by .xls format](https://stackoverflow.com/questions/45741670/valueerror-row-index-was-65536-not-allowed-by-xls-format) – Fabrizio Sep 05 '19 at 21:16
  • @Fabrizio,I checked and there is no repetition ! – AHMAD ALJAMAL Sep 05 '19 at 21:20
  • xlwt only writes to xls format..try openpyxl.. this is definitely a duplicate of the above link – Derek Eden Sep 05 '19 at 22:24
  • @DerekEden, how to conducting in my code , please? – AHMAD ALJAMAL Sep 05 '19 at 22:50
  • you'd have to do it in openpyxl .. there's a simple example in the docs https://openpyxl.readthedocs.io/en/stable/usage.html .. on a side note if it's just one (or a few) txt files you want to convert you can import text data into excel under the data tab, or paste it in and do text to columns – Derek Eden Sep 06 '19 at 00:42

1 Answers1

-1

It appears that you're trying to build an Excel spreadsheet longer than 2^16 rows. Excel does not support such long spreadsheets; it's a software limitation. You need to check your file length and stop the spreadsheet construction before you hit that point. You may want to continue to another sheet (tab), or another file. Without knowing your actual application, I can't suggest a particular solution.

Prune
  • 76,765
  • 14
  • 60
  • 81
  • This is an item for you to research. Please follow the posting guidelines in the help documentation, as suggested when you created this account. [On topic](https://stackoverflow.com/help/on-topic), [how to ask](https://stackoverflow.com/help/how-to-ask), and ... [the perfect question](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) apply here. – Prune Sep 05 '19 at 22:06