how to fill excel file from selenium scraping in loop with python

Question

i am trying to scrape a website than contain many pages, with selenium i open each time a page in second 'TAB' and launch my function to get the data. after that i close the tab and open the next tab and continue extraction until the last page. my problem is when i save my data in the excel file, i found that it save just the last information extract from the last page(tab). can you help me to find my error ?

def scrape_client_infos(linksss):

tds=[] # tds is the list that contain the data

reader=pd.read_excel(r'C:\python projects\mada\db.xlsx')
writer= pd.ExcelWriter(r'C:\python projects\mada\db.xlsx',engine='openpyxl')
html = urlopen(linksss)
soup=BeautifulSoup.BeautifulSoup(html,'html.parser')

table=soup.find('table',attrs={'class':'r2'})

#scrab all the tr that contain text    
for tr in table.find_all('tr'):
    elem = tr.find('td').get_text()
    elem=elem.replace('\t','')
    elem=elem.replace('\n','')
    elem=elem.replace('\r','')
    tds.append(elem)
    
print(tds)   

#selecting the data that i need to save in excel
raw_data={'sub_num':[tds[1]],'id':[tds[0]],'nationality':[tds[2]],'country':[tds[3]],'city':[tds[3]],'age':[tds[7]],'marital_status':[tds[6]],'wayy':[tds[5]]}    
df=pd.DataFrame(raw_data,columns=['sub_num','id','nationality','country','city','age','marital_status','wayy'])

#save the data in excel file
df.to_excel(writer, sheet_name='Sheet1',startrow=len(reader), header=False)
writer.save()
return soup

P.S: i always want to fill the excel file from the last line

Mike67 · Accepted Answer · 2020-09-17 15:16:12Z

2

To append excel data using Pandas, you need to set the worksheets in the writer object.

Update the last section in your code:

#save the data in excel file
from openpyxl import load_workbook
book = load_workbook(path)
startrw = book['Sheet1'].max_row+1
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)  # prevent overwrite
df.to_excel(writer, sheet_name='Sheet1',startrow=startrw, header=False)
writer.save()
return soup

edited Sep 17, 2020 at 15:16

answered Sep 16, 2020 at 20:53

Mike67

11.3k2 gold badges9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

yok Over a year ago

still always the same result, just the last record in saved in the excel file

Justin Lambert Over a year ago

You can read ,write into excel file by reader java in selenium (you have utils class for excel reader in your project )

Mike67 Over a year ago

There may be an issue with the startrow. Answer updated. Please retry.

yok Over a year ago

thank you,it work well . just one thing, inside the excel file i got after each insert row there is an empty row (blank row), is there any thing to do to avoid it ?

Collectives™ on Stack Overflow

how to fill excel file from selenium scraping in loop with python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related