How to read data from Excel and write it to a text file line by line?

Question

I want to write code to get data from Excel and write it to a text file. Here is the code I have:

import xlrd import os.path wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx')) wb.sheet_names() sh = wb.sheet_by_index(0) i = 1 while sh.cell(i,11).value != 0: Load = sh.cell(i,11).value D1 = sh.cell(i,13).value D2 = sh.cell(i,14).value D3 = sh.cell(i,15).value D4 = sh.cell(i,16).value D5 = sh.cell(i,17).value D6 = sh.cell(i,18).value D7 = sh.cell(i,19).value DB1 = str(Load) + " " + str(D1) + " " + str(D2) + " " + str(D3)+ " " + str(D4)+ " " + str(D5)+ " " + str(D6)+ " " + str(D7) file = open("Output.txt", "w") file.write(DB1 + '\n') file.close i = i + 1 

The problem with this code is that the data written to the text file is always displayed on the first line. Therefore, although I have 20 lines of data in excel, the text file only shows the latest data in the excel file in the first line in the text file. I have '\n' in file.write , but it doesn't seem to work.

Answer 1

You should open output.txt file with append mode :

 file = open("Output.txt", "a") 

In addition, you must do this before entering the cycle, and it must be closed after this cycle.

---

Update

In cases like this, you can use with instead of closing the file descriptor at the end.

Including a good suggestion made by @Josh in his own answer , the code could be as follows:

 import xlrd import os.path wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx')) wb.sheet_names() sh = wb.sheet_by_index(0) i = 1 with open("Output.txt", "a") as my_file: while sh.cell(i,11).value != 0: Load = sh.cell(i,11).value all_d = sh.col_values(i, 13, 19) DB1 = Load + " " + (" ".join(all_d)) my_file.write(DB1 + '\n') i += 1 

Answer 2

 import xlrd workbook=xlrd.open_workbook("xxx.xls") sh=workbook.sheet_by_name("test1") print sh.nrows print sh.ncols n=0 i=0 file=open("xxx.txt","w") for n in range(sh.nrows): for i in range(sh.ncols): data =sh.cell_value(n,i)+" " print data, file.write(data+" ") print file.write("\n") this code is working properly for writing into text file 

Answer 3

You open and close your output file for each row in an Excel worksheet.

Open the file before the while loop, and then close it when you finish the loop.

 import xlrd import os.path wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx')) wb.sheet_names() sh = wb.sheet_by_index(0) i = 1 file = open("Output.txt", "w") while sh.cell(i,11).value != 0: Load = sh.cell(i,11).value D1 = sh.cell(i,13).value D2 = sh.cell(i,14).value D3 = sh.cell(i,15).value D4 = sh.cell(i,16).value D5 = sh.cell(i,17).value D6 = sh.cell(i,18).value D7 = sh.cell(i,19).value DB1 = str(Load) + " " + str(D1) + " " + str(D2) + " " + str(D3)+ " " + str(D4)+ " " + str(D5)+ " " + str(D6)+ " " + str(D7) file.write(DB1 + '\n') i = i + 1 file.close 

Answer 4

xlrd really has a nice feature for capturing the values ​​of a group of cells in a column or row. Using this, you can greatly simplify your code (and I'm sure their function is more efficient). Thus, your code can become:

 import xlrd import os.path wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx')) wb.sheet_names() sh = wb.sheet_by_index(0) i = 1 my_file = open("Output.txt", "a") while sh.cell(i,11).value != 0: Load = sh.cell(i,11).value all_d = sh.col_values(i, 13, 19) DB1 = Load+" "+(" ".join(all_d)) my_file.write(DB1 + '\n') i += 1 file.close