How to convert a text file to Excel with Python

Question

I have a text file (textfile.txt) in a folder called DOT and I am trying to convert that file to an Excel file (Excelfile.xls) using Python code. I am not familiar with Python but from other comments I wrote the code below. The code does not work. Could anyone help me get the correct syntax?

book = xlwt.Workbook()
import xlwt
import xlrd
f = open('/DOT/textfile.txt', 'r+')
book.save('/DOT/Excelfile' + '.xls')

Answer 1

This is based on documentation from: https://pypi.python.org/pypi/xlwt

You will need to read the file line by line, format it and write it to the xls file.

import xlwt
import xlrd

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

f = open('/DOT/textfile.txt', 'r+')

data = f.readlines() # read all lines at once
for i in range(len(data)):
  row = data[i].split()  # This will return a line of string data, you may need to convert to other formats depending on your use case

  for j in range(len(row)):
    ws.write(i, j, row[j])  # Write to cell i, j

book.save('/DOT/Excelfile' + '.xls')
f.close()

Here, the data is being read, all the rows at once. Then, each line is being split into a list of data points, and added to a new row in the spreadsheet.

This is not the best/optimal solution but should get you started. Let me know in case there is a bug.