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 xlrd openpyxl
3. Create the Merge Script
sudo vim merge_excels.py
import pandas as pd
import os
folder_path = os.getcwd()
# Collect Excel-like files
excel_files = [
f for f in os.listdir(folder_path)
if f.endswith(('.xlt', '.xls', '.xlsx'))
]
all_data = []
failed_files = []
for file in excel_files:
full_path = os.path.join(folder_path, file)
try:
# Choose engine based on extension
if file.endswith(('.xls', '.xlt')):
df = pd.read_excel(full_path, engine='xlrd')
else:
df = pd.read_excel(full_path, engine='openpyxl')
df['source_file'] = file # optional tracking column
all_data.append(df)
except Exception as e:
print(f"❌ Failed to read {file}: {e}")
failed_files.append(file)
# Merge all at once (faster than repeated concat)
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
else:
merged_df = pd.DataFrame()
# Save output
output_file = os.path.join(folder_path, 'merged_output.xlsx')
merged_df.to_excel(output_file, index=False)
print("\n✅ Merge Completed")
print(f"📁 Files processed: {len(excel_files)}")
print(f"✔️ Successfully merged: {len(all_data)}")
print(f"❌ Failed files: {len(failed_files)}")
if failed_files:
print("\nFailed list:")
for f in failed_files:
print(" -", f)
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.
