During admission season, libraries often receive student data in multiple Excel files — separated by departments or batches — but with the same column headings. If you're managing student records in Koha Library Management System, merging these files into one is essential for smooth data import.
Doing it manually? That’s a recipe for errors and wasted time. Let’s automate it using Python!
Why Automate Excel Merging?
Manually copying and pasting data across Excel sheets is time-consuming but with just a few lines of code using the pandas library, we can merge all Excel files into one clean file
Steps to Merge Excel Files
1. Organize Your Files
Place all .xlsx files inside a folder (e.g., ug-students-2025). and enter into that folder
cd ug-students-2025
2. Set Up Your Python Environment
sudo apt install python3-pip python3.11-venv
python3 -m venv ~/excelmerge-env
source ~/excelmerge-env/bin/activate
pip install pandas openpyxl
3. Create the Merge Script
sudo vim merge_excels.py
import pandas as pd
import os
# Use current working directory
folder_path = os.getcwd()
# List only .xlsx files in current directory
excel_files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]
merged_df = pd.DataFrame()
for file in excel_files:
full_path = os.path.join(folder_path, file)
df = pd.read_excel(full_path)
merged_df = pd.concat([merged_df, df], ignore_index=True)
# Save merged file
output_file = os.path.join(folder_path, 'merged_output.xlsx')
merged_df.to_excel(output_file, index=False)
print(f'✅ Merged {len(excel_files)} files into {output_file}')
4. Run the Script
python3 merge_excels.py
Result
All your Excel files will be merged into a single file named merged_output.xlsx inside the same folder. now arrange column heading asper koha's borrower table column headings and create csv file and import to koha.
