CODEWITHSUNDEEP
pythonxlrd
Pexe
Pexe

Reputation: 91

Loop through an excel file, find certain cell values and write into text files

I am making text files out of information from an existing excel file. Not all of the information in the excel file is supposed to be written in the text files, but I want my code to loop through the file and choose information from rows according to certain cell values.

Here is my code so far, however, when I run the code nothing happens and the text files are not generated. I do not get an error message either. Does anyone know what I am missing?

import xlrd
xlsfilename = 'Myexcelfile.xls'
book = xlrd.open_workbook(xlsfilename)
book.sheet_by_index(0)
number_rows = 275
number_lines = 1

for row in range(number_rows):
    for col in 1, :     #Column where the cell value decides wether or not information in the row should be added to the text file.
        value = book.sheets()[0].cell(row, col).value
        if value == 19:   #Rows where 19 is the cell value in column 1 is to be focused on.
            txtfilename = 'Mytextfile' + str(row) + '.txt'
            with open(txtfilename, "w") as f:
                d={} #Creating a dictionary to for Subject number (see later in the code)
                for line in range(number_line):
                    f.write("Subject number{1}") #The subject number should change for each row containing 19 is added to the text file.
                    f.write('Text\n')
                    f.write('Newtext'.ljust(1))
                    for col in 3,:
                        val = book.sheets()[0].cell(row, col).value
                        s1 = str(val).ljust(1)
                        f.write(s1)
                    f.write('Moretext'.ljust(1))
                    for col 9, 10:
                        val = book.sheets()[0].cell(row, col).value
                        s2 = str(val).ljust(1)
                        f.write(s2)
        else:
            pass

Any help is greatly appreciated! I am working in Python3.4.1

Upvotes: 1

Views: 2870

Answers (1)

Edd
Edd

Reputation: 1370

To write all the rows that include the string "19" in column 1 into the same text file, you can do:

import xlrd
xlsfilename = 'Myexcelfile.xls'
book = xlrd.open_workbook(xlsfilename)
book.sheet_by_index(0)
number_rows = book.sheets()[0].nrows
number_lines = 1
column_target = 1
txtfilename = 'Mytextfile.txt'

with open(txtfilename, "w") as f:
    for row in range(number_rows):
        value = book.sheets()[0].cell(row, column_target).value
        if value == "19":   #Rows where 19 is the cell value in column 1 is to be focused on.
                d={} #Creating a dictionary to for Subject number (see later in the code)
                for line in range(number_line):
                    f.write("Subject number{1}") #The subject number should change for each row containing 19 is added to the text file.
                    f.write('Text\n')
                    f.write('Newtext'.ljust(1))
                    for col in 3,:
                        val = book.sheets()[0].cell(row, col).value
                        s1 = str(val).ljust(1)
                        f.write(s1)
                    f.write('Moretext'.ljust(1))
                    for col 9, 10:
                        val = book.sheets()[0].cell(row, col).value
                        s2 = str(val).ljust(1)
                        f.write(s2)
        else:
            pass

Basically, just move the file scope outside of the loop so that the same file can be reused for multiple rows.

Upvotes: 2

Related Questions