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')
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.
row = data[i].split()I had a similar problem. The txt file’s content was actually separated by “Tab” blanks (get to know this when importing data in Excel).
Searched and tried some answers, but only got this working fine with mine.
https://mail.python.org/pipermail/tutor/2011-May/083411.html
