I Am Trying To Fill Out A Web Form With Python Using Data From Excel

I have an excel file with thousands of rows with data. I am trying to find materials that could help me automate the process of pulling data from excel and filling it up on the web

Solution 1:

Your question is pretty broad, but in general lines what I think you're looking for is essentially Python, Pandas and Selenium.

You could essentially install Selenium, use Pandas to evaluate your csv file and loop it so it'll input the data for you. It might take a while as it will imitate human procedure, so to speak, but it should help you.

Edit - Handling Selenium and Pseudo-Code

1. Building blocks

For this pseudo-walkthrough, here's what we are going to use:

• BeautifulSoup: Python library used to obtain HTML and XML information
• Pandas: Python library for reading and manipulating data. This is what we're using to handle the Excel file.
• Selenium: a web browser automation tool that will handle the grunt work for us.
• Google Chrome: dismisses presentation

2. Installations

2.1. Python Libraries

Run the following code to install the necessary libraries:

!pip install bs4!pip install pandas!pip install selenium

2.2. Selenium Driver

Now that you have the libraries installed, let's handle the last part of the setup. We will download a Google Chrome driver, which will be the basis of operation for the Selenium library.

Follow these steps:

1. Open Google Chrome and click the three dots on the upper right corner
2. Go to Help > About Google Chrome
3. Check what your Chrome version is
4. Access http://chromedriver.chromium.org/downloads and download the driver corresponding to your version
5. Make sure you save the driver to a folder you'll use in the future

3. Getting stuff done

So, I haven't exactly run this code just yet, so sorry for any syntax mistakes I might've overlooked. However, this is what it's going to look like:

from bs4 import BeautifulSoup
from requests import get
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
import time
import pandas as pd

# using Pandas to read the csv file
source_information = pd.read_csv('my_file.csv',header=None,skiprows=[0])

# setting the URL for BeautifulSoup to operate in
url = "yourwebform.com"
my_web_form = get(url).content
soup = BeautifulSoup(my_web_form, 'html.parser')

# Setting parameters for selenium to work
path = r'C:/Users/itsme/Desktop/Util/chromedriver.exe'#make sure you insert the path to your driver here!
options = webdriver.ChromeOptions()
options.add_argument("--start-maximized")
driver = webdriver.Chrome(path, chrome_options=options)
driver.get(url)

# creating a procedure to fill the formdeffulfill_form(username, user_email):
    # use Chrome Dev Tools to find the names or IDs for the fields in the form
    input_customer_name = driver.find_element_by_name('username')
    input_customer_email = driver.find_element_by_name('email')
    submit = driver.find_element_by_name('Submit')

    #input the values and hold a bit for the next action
    input_customer_name.send_keys(username)
    time.sleep(1)
    input_customer_email.send_keys(user_email)
    time.sleep(1)
    submit.click()
    time.sleep(7)


# creating a list to hold any entries should them result in error
failed_attempts = []

# creating a loop to do the procedure and append failed cases to the listfor customer in source_information:
    try:
        fulfill_form(source_information[customer_name], source_information[customer_email])
    except:
        failed_attempts.append(source_information[customer_name])
        passiflen(failed_attemps) > 0:
    print("{} cases have failed").format(len(failed_attempts))

print("Procedure concluded")

Let me know if you run into any trouble and we'll try and fix it together!

Solution 2:

I used Rodrigo Castilho's solution but had to place all the code creating and opening the driver inside the formula. With the driver outside the formula it would iterate through the first row of data and then fail at the second because there was no connection to the driver. By placing the block of code to connect in the formula it closed and reopened for each row. I'm happy to explain to anyone else having similar problems any other steps.

deffulfill_form(first, last, email, phone, zip):

    # Setting parameters for selenium to work
    path = r'chromedriver'# make sure you insert the path to your driver here!
    options = webdriver.ChromeOptions()
    options.add_argument("--start-maximized")
    driver = webdriver.Chrome(path, options=options)
    driver.get(url)

    # use Chrome Dev Tools to find the names or IDs for the fields in the form
    input_first = driver.find_element_by_id('form-first_name')
    input_last = driver.find_element_by_id('form-last_name')
    input_email = driver.find_element_by_id('form-email')
    input_phone = driver.find_element_by_id('form-phone')
    input_zip = driver.find_element_by_id('form-zip_code')

    submit = driver.find_element_by_name('commit')

    #input the values and hold a bit for the next action

    driver.delete_all_cookies()
    input_first.send_keys(first)
    time.sleep(1)
    input_last.send_keys(last)
    time.sleep(1)
    input_email.send_keys(email)
    time.sleep(1)
    input_phone.send_keys(phone)
    time.sleep(1)
    input_zip.send_keys(zip)
    time.sleep(1)
    submit.click()
    time.sleep(1)

    driver.close()