CODEWITHSUNDEEP
pythonexcelseleniumloopsurl
Andrés Ruiz
Andrés Ruiz

Reputation: 21

How to use selenium to open multiple urls from a column in an xlsx file

I am an absolute noob and I have the following scenario: I have an Excel file with a column filled with +4000 URLs each one in a different cell. The url links to a facebook-like page where a user would be asked to set a password. I need to use Python to retrieve each url from the column, open it with Chrome, input the same specified password for all users, and then validate it lands on the home page.

Step by step:

1 Openpyxl opens excel spreadsheet.

2 Find column with urls.

3 Make a list of urls?

4 Get chrome to open the first url.

6 Find password field.

7 Input a password that will be the same one for all users.

8 Confirm it lands on homepage.

9 Loop with all other urls in the column until the end.

10 Preferably get a report to confirm the number of fails if any.

So far this is my code:

# I can open the file
import openpyxl
wb=openpyxl.load_workbook('Test Sheet.xlsx')
type(wb)

# get the name of the sheet I need to work with
print (wb.sheetnames)

<Worksheet "Users">

# this line brings the current urls in my file 
sheet=wb['Users']
for x in range (2,4):
print(x,sheet.cell(row=x,column=3).value)

# output
2 https://firstfacebookpage.com
3 https://secondfacebookpage.com


# I found this other way to retrieve the urls from the excel spreadsheet.
ws = wb['Users']
column = ws['c']  
column_list = [column[x].value for x in range(len(column))]
print (column_list)

# output while having only 2 urls in the test sheet.
['Claim Link', 'https://somefacebookurl.com', 'https://someotherfacebookurl.com', None, None, None, 
None, None, None, None, None, None, None, None, None, None, None, None, None, None]

# This login, enter password, verify, close browser, works perfectly if I manually enter the url.
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
driver = webdriver.Chrome()
driver.get("https://firstfacebookpage.com")

password_box = driver.find_element_by_class_name('inputpassword') 
password_box.send_keys("theonepassword") 
print ("Password entered") 

login_box = driver.find_element_by_id('u_0_9') 
login_box.click() 

print ("Done") 
driver.close() 
print("Finished")

Now I can't figure out a way to make the "driver.get" the urls from the spreadsheet and loop over these login steps. Since my file will have +4000 urls in the column I'd rather have the script do that for me. Any help will be much appreciated.

Upvotes: 0

Views: 2521

Answers (1)

Sureshmani Kalirajan
Sureshmani Kalirajan

Reputation: 1938

You can try with pandas & xlrd,

import pandas as pd
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException



df = pd.read_excel('myurls.xlsm') # Get all the urls from the excel
mylist = df['urls'].tolist() #urls is the column name

print(mylist) # will print all the urls

# now loop through each url & perform actions.
for url in mylist:
    driver = webdriver.Chrome()
    driver.get(url)

    try:
       WebDriverWait(driver, 3).until(EC.alert_is_present(),'Timed out waiting for alert.')

       alert = driver.switch_to.alert
       alert.accept()
       print("alert accepted")
    except TimeoutException:
       print("no alert")
    password_box = driver.find_element_by_class_name('inputpassword') 
    password_box.send_keys("theonepassword") 
    print ("Password entered")
    login_box = driver.find_element_by_id('u_0_9') 
    login_box.click() 
    driver.close()


print ("Done")

Upvotes: 1

Related Questions