Photo by the author
# Entry
Exceed remains vital when working with data, but much of the time spent using it is purely mechanical. Tasks like merging files from multiple sources, tracking duplicate records, reformatting inconsistent exports, and splitting a master sheet into separate files are not complicated, but they are time-consuming and prone to human error.
These five Python scripts assist automate these tasks. Each is self-contained, configurable, and designed to work with unstructured real-world data.
You can find all scripts on GitHub.
# Combining multiple Excel files
// Pain point
When consolidating data from multiple Excel or CSV files, the manual process of opening each file, copying the data, and pasting into a master worksheet is snail-paced and prone to misalignment errors, especially when the column order differs from file to file.
// What the script does
This script scans the folder for .xlsx and .csv files, arranges all the data into one unified sheet, and writes a pristine, merged output file. Optionally, it can add a source column so you always know which row comes from which file, and automatically handles mismatched column orders.
// How it works
The script uses pandas to read every file in the target directory, aligns columns by name rather than position, and combines everything into one DataFrame. Customizable add_source_column flag appends the original filename to each line. Column mismatches are logged so you know if there are extra or missing fields in certain files. The output is written with openpyxl and contains a summary tab showing the line count file by file.
⏩ Download the Excel file merging script
# Find and mark duplicate rows
// Pain point
Duplicate records are common in datasets that have been exported and re-imported between systems. Exact matches are simple to find, but near-duplicates – the same record, slightly different formatting or spacing – are harder to catch manually at scale.
// What the script does
This script scans the Excel file for duplicate rows based on defined columns, marks exact duplicates and near-duplicates using fuzzy matches in string fields, and writes an annotated output file, highlighting each suspected duplicate group with color coding and a confidence indicator.
// How it works
The script uses pandas for true duplicate detection and RapidFuzz for fuzzy string matching on configurable key columns. Each row is assigned a duplicate group ID and a match confidence percentage. The output Excel file uses openpyxl formatting to highlight duplicate clusters. A separate summary sheet shows the total number of duplicates found by match type.
⏩ Download the duplicate finder script
# Cleaning and standardizing messy exported data
// Pain point
Data exported from external systems often arrives in an inconsistent format, with different date formats, inconsistent capitalization, phone numbers with different separators, and trailing whitespace. Cleaning this up manually before any analysis adds up quickly.
// What the script does
This script applies a configurable set of cleanup rules to an Excel or CSV file. These include standardizing dates, trimming white space, correcting letter case, normalizing phone numbers and zip codes, removing blank lines, and marking cells that appear invalid. Generates a cleaned file and a change log showing exactly what was modified.
// How it works
The script reads a configuration file that maps column names to cleanup operations: date_format, title_case, strip_whitespace, phone_normalize, remove_blank_rowsand others. Each operation is performed sequentially. A parallel changelog is written to the second output sheet, showing the original and cleaned values for each modified cell. Nothing is rejected silently. If a value cannot be parsed, it is flagged in file a _clean_errors column.
⏩ Download the data cleansing script
# Split one sheet into separate files by column value
// Pain point
The master dataset often requires distribution as separate files – perhaps one per region, department, or category. Doing this manually involves repeated filtering, copying, and saving, with a high risk of mixing up data between files.
// What the script does
This script reads a single Excel sheet and splits it into separate output files – one per unique value in a specific column. Each output file contains only the lines corresponding to this value, preserving the original formatting. File names are generated automatically based on column values. Optionally, it can send each file as an email attachment using the name-to-email mapping you provide.
// How it works
The script groups DataFrame through the target column using pandasthen saves each group to its own .xlsx file with openpyxl. Naming template, e.g Sales_Report_{value}_{date}.xlsxallows you to control the format of the output file name. Column headings, data types, and basic formatting are preserved in each output file. The optional email mode reads the CSV mapping {value} → {email address} and sends each file via Simple Mail Transfer Protocol (SMTP).
⏩ Download the sheet separator script
# Generate a summary pivot report from raw data
// Pain point
Creating a summary report from your raw data – category totals, monthly trends, or top performers – involves creating pivot tables, formatting them, and copying the results into a pristine layout. When the source data is updated regularly, this process is repeated from scratch each time.
// What the script does
This script reads an Excel file with raw data, creates customizable pivot summaries, and writes a formatted summary report consisting of multiple tabs. Charts are generated and embedded in the output file. You can restart it at any time when the source data changes.
// How it works
The configuration file defines a date field, a value field, grouping columns, and specific aggregations to run. The script uses pandas for all aggregation logic and openpyxl With Matplotlib to generate charts. Each summary type has its own tab. Conditional formatting distinguishes the highest and lowest values. The report is designed to be regenerated on demand, and restarting the script completely overwrites the previous output.
⏩ Download the Pivot Report Generator script
# Summary
These five scripts cover common Excel tasks that are simple to automate but tedious to perform manually. Choose the one that addresses the most common task in your workflow and start there. Here’s a quick overview:
| Script name | Intention | Key Features | Best exploit case |
|---|---|---|---|
| Excel file combination | Combine multiple Excel/CSV files | Column alignment, source tracking, summary sheet | Consolidation of data from multiple sources |
| Duplicate finder | Identify exact and fuzzy duplicates | Fuzzy matching, confidence metrics, color highlighting | Cleaning datasets with duplicate records |
| Data cleansing | Standardize messy exported data | Formatting rules, normalization, change log | Pre-processing of raw external data |
| Sheet divider | Split one sheet into multiple files | Automatic file naming, grouping, optional e-mail sending | Distribution of reports by category/region |
| Pivot report generator | Create summary reports based on raw data | Automated pivots, charts, output across multiple tabs | Periodic reports and dashboards |
Elated automation!
Bala Priya C is a software developer and technical writer from India. He likes working at the intersection of mathematics, programming, data analytics and content creation. Her areas of interest and specialization include DevOps, data analytics and natural language processing. She enjoys reading, writing, coding and coffee! He is currently working on learning and sharing his knowledge with the developer community by writing tutorials, guides, reviews, and more. Bala also creates intriguing resource overviews and coding tutorials.