Chapter 8 - Excel & Database Automation

8.1 Chapter Overview

Business reporting often involves repetitive spreadsheet and database work. Staff may download Excel files, clean data, copy records into another system, prepare summaries, create reports and email results. These tasks are highly suitable for automation.

Excel and database automation connects spreadsheets, structured data storage and reporting. A bot can read Excel data, validate records, insert data into a database, run SQL queries and generate business reports automatically.

Learning Outcome: By the end of this chapter, learners should be able to automate business reporting processes using Excel files, SQLite databases and Python automation scripts.

Excel Input

→

Python Bot

→

SQLite Database

→

Summary Query

→

Excel Report

8.2 Learning Objectives

Understand spreadsheet and database automation use cases.
Use Python pandas to read and clean Excel data.
Use openpyxl to create formatted Excel reports.
Use SQLite to store and query business records.
Build a simple ETL workflow: Extract, Transform and Load.
Generate automated business summary reports.
Create a complete working reporting bot.
Apply error handling and validation in reporting automation.

8.3 Business Reporting Automation Use Cases

Department	Manual Reporting Task	Automation Opportunity
Finance	Combine invoice spreadsheets and calculate totals.	Bot consolidates Excel files and generates finance summary.
HR	Track attendance and payroll data manually.	Bot reads attendance Excel and creates department summary.
Training Centre	Prepare student completion and certificate reports.	Bot stores records in database and creates completion report.
Sales	Calculate sales by product and region.	Bot generates sales dashboard data.
Inventory	Check stock levels and reorder requirements.	Bot highlights low stock items automatically.

8.4 Excel Automation with pandas

pandas is used to read, clean, analyze and export spreadsheet data. It is very useful for business reporting automation.

Install Required Libraries

pip install pandas openpyxl

Read Excel File

import pandas as pd

df = pd.read_excel("business_transactions.xlsx")

print(df.head())

Group and Summarize Data

import pandas as pd

df = pd.read_excel("business_transactions.xlsx")

summary = df.groupby("Department")["Amount"].sum().reset_index()

print(summary)

summary.to_excel("department_summary.xlsx", index=False)

Expected Result:
An Excel file named department_summary.xlsx is created.

8.5 Excel Report Formatting with openpyxl

openpyxl helps format Excel reports by applying colors, bold text, column width and number formats.

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

workbook = load_workbook("department_summary.xlsx")
sheet = workbook.active

for cell in sheet[1]:
    cell.font = Font(bold=True)
    cell.fill = PatternFill(start_color="FFD400", end_color="FFD400", fill_type="solid")

for column in sheet.columns:
    max_length = 0
    column_letter = column[0].column_letter

    for cell in column:
        value = str(cell.value)
        if len(value) > max_length:
            max_length = len(value)

    sheet.column_dimensions[column_letter].width = max_length + 4

workbook.save("formatted_department_summary.xlsx")

print("Formatted Excel report created.")

8.6 Introduction to Database Automation

A database stores structured business data in tables. SQLite is a lightweight database included with Python. It is excellent for learning and small automation projects.

Database Term	Meaning	Example
Database	Collection of organized data.	pdtc_reports.db
Table	Structured storage with rows and columns.	transactions
Row	One record.	One invoice transaction
Column	One data field.	Department, Amount, Date
SQL	Language used to query databases.	SELECT * FROM transactions

8.7 Create SQLite Database with Python

import sqlite3

connection = sqlite3.connect("pdtc_business.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT,
    department TEXT,
    process TEXT,
    amount REAL,
    status TEXT
)
""")

connection.commit()
connection.close()

print("Database and table created successfully.")

8.8 Insert Excel Data into Database

import pandas as pd
import sqlite3

df = pd.read_excel("business_transactions.xlsx")

connection = sqlite3.connect("pdtc_business.db")

df.to_sql("transactions", connection, if_exists="replace", index=False)

connection.close()

print("Excel data inserted into database successfully.")

Explanation: This script reads Excel data and stores it into a database table named transactions.

8.9 Query Database with SQL

import sqlite3
import pandas as pd

connection = sqlite3.connect("pdtc_business.db")

query = """
SELECT Department, SUM(Amount) AS Total_Amount
FROM transactions
GROUP BY Department
ORDER BY Total_Amount DESC
"""

summary = pd.read_sql_query(query, connection)

connection.close()

print(summary)

8.10 ETL Automation Workflow

ETL stands for Extract, Transform and Load. It is a common automation pattern used in business reporting.

1Extract Data from Excel

2Transform / Clean Data

3Load to Database

4Run SQL Queries

5Generate Report

Data Cleaning Example

import pandas as pd

df = pd.read_excel("business_transactions.xlsx")

df["Department"] = df["Department"].str.strip()
df["Status"] = df["Status"].str.title()
df["Amount"] = df["Amount"].fillna(0)

df = df[df["Amount"] > 0]

print(df)

8.11 Automated Business Reporting

An automated business reporting bot can generate reports by department, process, date or status.

Report	Purpose
Department Summary	Shows total amount by department.
Status Summary	Shows completed, pending or rejected transactions.
Process Summary	Shows total value by business process.
Exception Report	Shows missing data, negative values or pending items.

Exception Report Example

import pandas as pd

df = pd.read_excel("business_transactions.xlsx")

exceptions = df[
    (df["Amount"].isna()) |
    (df["Amount"] <= 0) |
    (df["Status"].isna())
]

exceptions.to_excel("exception_report.xlsx", index=False)

print("Exception report created.")

8.12 Real Working Bot Model

The complete working bot follows this process:

Create Sample Excel

→

Read Excel

→

Clean Data

→

Load SQLite

→

Generate Reports

Main Bot Code Preview

import pandas as pd
import sqlite3
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

df = pd.read_excel("business_transactions.xlsx")

df["Department"] = df["Department"].str.strip()
df["Status"] = df["Status"].str.title()
df["Amount"] = df["Amount"].fillna(0)

connection = sqlite3.connect("pdtc_business_reports.db")
df.to_sql("transactions", connection, if_exists="replace", index=False)

department_summary = pd.read_sql_query("""
SELECT Department, SUM(Amount) AS Total_Amount
FROM transactions
GROUP BY Department
ORDER BY Total_Amount DESC
""", connection)

status_summary = pd.read_sql_query("""
SELECT Status, COUNT(*) AS Total_Transactions, SUM(Amount) AS Total_Amount
FROM transactions
GROUP BY Status
""", connection)

connection.close()

with pd.ExcelWriter("PDTC_Automated_Business_Report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Cleaned Data", index=False)
    department_summary.to_excel(writer, sheet_name="Department Summary", index=False)
    status_summary.to_excel(writer, sheet_name="Status Summary", index=False)

print("Automated business report created successfully.")

8.13 Downloadable Working Example

A complete working example package is provided. It contains:

create_sample_excel.py - creates sample Excel transaction file
excel_database_reporting_bot.py - full automation bot
README.txt - step-by-step instructions

Testing Steps: Download the ZIP, extract it, run pip install pandas openpyxl, then run python create_sample_excel.py and python excel_database_reporting_bot.py.

⬇ Download Working Example ZIP

8.14 Interactive Report Simulator

This small calculator simulates how business reporting automation summarizes department amounts.

Finance Amount HR Amount Training Amount

Click Generate Summary.

8.15 Best Practices

Best Practice	Reason
Validate Excel columns	Prevents bot failure from missing headers.
Clean data before database loading	Improves report accuracy.
Use SQL queries for summaries	Efficient and repeatable reporting.
Keep raw data and summary reports separate	Improves audit and troubleshooting.
Use exception reports	Highlights data problems requiring human review.
Back up database files	Protects business records.

8.16 Practical Activities

Activity 1: Create Excel Data

Create an Excel file with Department, Process, Amount and Status columns.

Activity 2: Load Data to SQLite

Write a Python script to insert Excel data into a SQLite database.

Activity 3: SQL Summary

Use SQL to calculate total amount by department.

Activity 4: Automated Report

Generate an Excel workbook with raw data, department summary and status summary sheets.

Mini Project

Build a business reporting bot that reads Excel, stores data in SQLite and generates a formatted Excel report.

8.17 Interactive Final Assessment Quiz

Each correct answer gives +1 mark.
Each wrong answer gives -0.5 mark.

1. Excel automation can help reduce repetitive reporting work.

True False

2. Which Python library is commonly used for data analysis and Excel reading?

pandas paint music vlc

3. SQLite is a lightweight database that can be used with Python.

True False

4. SQL is used to query database records.

True False

5. ETL stands for:

Extract, Transform, Load Enter, Type, Leave Excel, Text, Logo Error, Test, Link

6. openpyxl can be used to format Excel reports.

True False

7. Exception reports help identify data problems.

True False

8. A database table stores rows and columns.

True False

9. It is good practice to validate Excel columns before running a reporting bot.

True False

10. Automated reporting should never keep logs or summaries.

True False

Your Score: 0

8.18 Chapter Summary

In this chapter, learners studied Excel and database automation for business reporting. They learned how to read Excel data, clean records, store data in SQLite, run SQL queries and generate automated Excel reports using real Python scripts.

Remember: A good reporting bot extracts accurate data, cleans it, stores it properly, generates useful summaries and highlights exceptions for review.