Selenium and Excel - Import Excel Data and Send Keys

Question

I am working on a project where I need to import an excel sheet into a website form. I'm having trouble with properly reading the data from the sheet and send it using the send_keys.

First, I created entries in the form:

load_rows = 0
while load_rows < 101:
    add_element = driver.find_element_by_css_selector('.form-footer')
    add_element.click()
    load_rows = load_rows + 1
    driver.execute_script("window.scrollTo(0, 1000);")

Then, I get the data from an excel sheet that matches the required entries on the website:

# Loading the Excel Info
filepath = "trusted_ips.xlsx"
wb = load_workbook(filepath)
ws = wb.active
max_row = sheet.max_row
max_column = sheet.max_column
print("Total columns : ", max_column)
print("Total rows : ", max_row)

It seems like it's storing all of column A into name_elements.. So, the send_keys function is sending all of column A before moving to the next field.

I want it to only send each element to a field, and I think that a list would fix this. But I'm not too sure.

for name_elements in driver.find_elements_by_xpath ('//*[contains(@id, 
    "_name")]'):
    for row in ws['A2':'A100']:
        for cell in row:
            name_elements.send_keys(cell.value)
    print(name_elements)

cgclip · Accepted Answer · 2019-04-30 22:33:26Z

1

you probably want to use pandas or else work on formatting your excel file from openpyxl into a more manageable dictionary.

I've done similar kinds of work, where I need to scrape off of the website where information on an excel file is an input.

My set up is somewhat like the following

import pandas as pd

#load an excel file into a pandas dataframe
df = pd.read_excel("myfile.xlsx",
                    sheet_name=1, # pick the first sheet
                    index_col='client') # set the pandas index to be the column labeled 'client'

# .to_dict converts the dataframe to a dictionary with 'client' being the key, and the remaining rows being converted to dictionary with the column label as key and row as value
for client, record in df.to_dict(orient='records'):
    print(client) # use these print statements if what you're looping over is unfamiliar 
    print(record)
    driver.find_element('#client_input).send_keys(client)
    driver.find_element('#address_input').send_keys(record['address'])

answered Apr 30, 2019 at 22:33

cgclip

3221 gold badge3 silver badges15 bronze badges

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

1 Comment

Charlie Clark Over a year ago

No real need for pandas here: the rows in the worksheet match the fields in the form already so a simple auxiliary dictionary based on the first line is sufficient.

nfordhk · Accepted Answer · 2019-05-02 00:58:21Z

0

I was able to solve this with the following -

I created a list cells[] to store the value. Followed by using the variable name_elements to store them. Settings the counter=0

cells=[]
counter=0
name_elements = driver.find_elements_by_xpath ('//*[contains(@id, "_cidr")]')

The first loop iterates over Column A of the excel sheet and stores the values in the list cells[].

for row in ws['C2':'C100']:
    for cell in row:
        cells.append(cell.value)

The second loop takes the list, for each value performs a send key to the next name_elements using the counter to loop through it.

for cell in cells:
    name_elements[counter].send_keys(cell)
    counter+=1

edited May 2, 2019 at 0:58

answered May 2, 2019 at 0:51

nfordhk

311 silver badge7 bronze badges

Collectives™ on Stack Overflow

Selenium and Excel - Import Excel Data and Send Keys

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related