-1

I have excel file shown bellow from that i need to extract data in different format using python. so please help i tried the bellow code:

import xlrd
book = xlrd.open_workbook("excel.xlsx")

sheet = book.sheet_by_index(0)
year=[]

# for print the year seperately in list. 
for dat in sheet.row_values(0):
     if dat is not '':
        year.append(dat)
print(year)


rowdata = []
for i in range(2, sheet.nrows):
    sheetinlist = sheet.row_values(i)
    for cell in sheetinlist:
             if cell is not '':
                rowdata.append(cell)

print(rowdata)

I print all data without null with the above code Click to see but couldn't get desire output
the desire output should be in list like:[student age, 20, 1990], [student age, 21,1991], [student age, 22,1992],...... for whole sheet. using for loop. The excel sheet is: click to see
So Please help ....Awaiting for response...

user7917919
  • 33
  • 7
  • *should be in list like:[student age, 20, 1990]*: Your code logic are faulty. `student age == A3`, `20 == B3` and `1990 == B1`. You want `rowdata[cell(A3), cell(B3), cell(B1)]`, `rowdata[cell(A3), cell(C3), cell(C1)]` ... **Why** do you loop rows if your data stays in the same row on different columns? – stovfl Oct 14 '18 at 09:59
  • @stvofl, yes code is not working. till now only i could code. but need the desired output for entire my sheet so it should be in for loop means thats logic is apply for whole sheet – user7917919 Oct 14 '18 at 12:08
  • You need to [edit] your Question and change looping, from **row** to **column**. – stovfl Oct 14 '18 at 15:09

1 Answers1

2

Your requirement is contradicting the data in your Excel sheet. One problem I can find is that you are now clearing the list after each row ..

    for i in range(2, sheet.nrows):
        sheetinlist = sheet.row_values(i)
        rowdata[:] = []
        for cell in sheetinlist:
                    if cell is not '':
                            rowdata.append(cell)
        print rowData
Husam Ibrahim
  • 6,999
  • 3
  • 16
  • 28
Subhroneel Ganguly
  • 21
  • 4
  • thanks for your time sir. but i want [student age, 20, 1990], [student age, 21,1991], [student age, 22,1992],......like this output not want to clear the data i could code till there so which one i should add next so i get my output – user7917919 Oct 14 '18 at 12:11