Automate data entry in Excel using Python

Handling data entry tasks manually in Excel can be tedious and prone to errors.

With Python, you can automate the process using tools like PySimpleGUI and pandas to create an easy-to-use form that inputs data directly into an Excel file.

In this guide, we’ll walk through how to build a simple data entry form that saves the data automatically into an Excel sheet.

GUI Result

What You’ll Need

Before diving into the code, ensure you have the necessary tools:

• Python installed on your system
• PySimpleGui for building the user interface
• pandas to interact with Excel files
• An Excel file where the data will be stored

You can install PySimpleGUI and pandas by running these commands in your terminal:

pip install pysimplegui pandas

Setting Up the Environment

Once you’ve installed the required libraries, ensure you have an Excel file ready for data entry. In this example, we’ll use a file called Excel_data.xlsx

Creating the Data Entry Form

Next, we need to design the data entry form using PySimpleGUI. The form will collect details like the user’s name, favorite color, languages they speak, and the number of children they have.

Here’s how the layout will be structured:

layout = [
    [sg.Text('Please fill out the following fields:')],
    [sg.Text('Name', size=(15, 1)), sg.InputText(key='-NAME-')],
    [sg.Text('Favourite Colour', size=(15, 1)), sg.Combo(['Red', 'Green', 'Blue'], key='-COLOUR-')],
    [sg.Text('I speak', size=(15, 1)), sg.Checkbox('English', key='-ENGLISH-'), sg.Checkbox('French', key='-FRENCH-')],
    [sg.Text('No. of Children', size=(15, 1)), sg.Spin([i for i in range(0, 16)], initial_value=1, key='-CHILDREN-')],

Writing the Code

With the layout ready, let’s write the full code to make the form functional. This code will handle data entry and save the data directly into the Excel file.

import PySimpleGUI as sg
import pandas as pd

# Add some color to the window
sg.theme('DarkTeal')

# Load the Excel file
EXCEL_FILE = 'Excel_data.xlsx'
df = pd.read_excel(EXCEL_FILE)

# Layout of the form
layout = [
    [sg.Text('Please fill out the following fields:')],
    [sg.Text('Name', size=(15, 1)), sg.InputText(key='-NAME-')],
    [sg.Text('Favourite Colour', size=(15, 1)), sg.Combo(['Red', 'Green', 'Blue'], key='-COLOUR-')],
    [sg.Text('I speak', size=(15, 1)),
     sg.Checkbox('English', key='-ENGLISH-'),
     sg.Checkbox('French', key='-FRENCH-')],
    [sg.Text('No. of Children', size=(15, 1)), sg.Spin([i for i in range(0, 16)], initial_value=1, key='-CHILDREN-')],
    [sg.Submit(), sg.Button('Clear'), sg.Exit()]
]

window = sg.Window('Data Entry Form', layout)

def clear_input():
    for key in values:
        window[key]('')
    return None

# Event loop to capture form data
while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Exit':
        break
    if event == 'Clear':
        clear_input()
    if event == 'Submit':
        df = df._append(values, ignore_index=True)
        try:
            df.to_excel(EXCEL_FILE, index=False)
            sg.popup('Data saved successfully.')
            clear_input()
        except PermissionError:
            sg.popup_error('Error: The Excel file might be open. Please close it and try again.')
            clear_input()

window.close()

Testing and Running the Script

Save the code as data_entry.py, and run it in your terminal:

python data_entry.py

Fill out the form, click “Submit,” and check your Excel file. The data should be saved in the next available row.

Get access to the script

To make it even easier for you to get started, we’ve uploaded the Excel Data Cleaner and Copier script to our GitHub account. Here’s how you can find and use it:

Visit Our GitHub Repository

1. Go to GitHub: Open your web browser and visit our GitHub repository at https://github.com/Automate-Coders/excel_data_entry_form.git
2. Explore the Repository: You’ll see a list of files and folders in the repository. Look for the file named Index.py

And here is a flow chart of how the code works

Results

After running the script and entering data, the Excel file will be updated with your input. Each new entry will appear in a new row.

Excel Data Before Running the script

After Running The Script

Troubleshooting Common Issues

1. Excel file locked: Ensure the Excel file is closed before running the script.
2. Permission errors: Double-check file permissions, or try running the script with administrator privileges.
3. Form fields not clearing: If fields don’t reset after submission, ensure the clear_input() function is correctly called.

Conclusion

With this guide, you’ve learned how to automate Excel data entry using PySimpleGUI and pandas. This approach not only saves time but also reduces the risk of manual entry errors. You can now explore more advanced features or adapt the script for other use cases!

If you like blog post like this then you would love our other blog post like How to Remove Duplicates and Move Data in Excel Using Python