How to read data from Excel and write it to 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 the data written to the text file is always displayed at the first row. Therefore, although I have 20 rows of data in excel, the text file only shows the last data in the excel file at the very first row in the text file. I have '\n' in file.write but, it doesn't seem to work.

Answer 1

You should open the output.txt file with append mode:

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

Also, you should do this before entering the loop, and it should be closed after that loop.

---

Update:

In cases like this one, you could use with instead of closing the file handle at the end.

Also including the good suggestion made by @Josh in his own answer, the code could be this:

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