Build a salary bank upload file with IFSC checks and control totals

What the bank will and will not catch

Most corporate banking portals reject a file whose columns do not match their template, and some reject a malformed IFSC. Very few will tell you that two employees share an account number, that a salary of ₹0 has crept in, or that the file total is ₹39,400 short of the payroll register because one row was deleted while fixing it. Those are business errors, and the checks for them have to happen before the upload.

The IFSC format, from the source

The RBI's NEFT FAQ describes the Indian Financial System Code as an 11-character code: the first four alphabetic characters identify the bank, the fifth is 0, and the last six identify the branch. Two practical consequences follow. The fifth character is the digit zero, and a letter O typed in its place is a common error. And the branch part is not guaranteed to be digits, so a check that insists on six digits will reject genuine codes.

Account numbers have no single national length. The script below uses 9 to 18 digits purely as a loose sanity range to catch obvious truncation; replace it with the rule your bank gives you.

Checks, holds and the upload in one pass

Every employee, account and IFSC below is synthetic.

import csv, io, re
from decimal import Decimal
import pandas as pd

# Synthetic payroll output for September 2026. Names, accounts and IFSCs are invented.
pay = pd.DataFrame({
    "emp_id": ["E201", "E202", "E203", "E204", "E205", "E206", "E207"],
    "name": ["Anita Verma", "Rohit Singh", "Meena Iyer", "Farhan Ali", "Kavya Rao",
             "Deepak Jain", "Suresh Pal"],
    "account": ["50100123456789", "000401234567", "50100123456789", "9876543210",
                "31234567890", "12345678901234", "7766554433"],
    "ifsc": ["HDFC0001234", "icic0000401", "HDFC0001234", "SBIN012345",
             "SBIN0012345", "UTIB0002233", "KKBK0004455"],
    "net_pay": ["48,250.00", "61,900", "52,000.00", "39,400.00", "0", "57,125.50", "44,800.00"],
    "status": ["active", "active", "active", "active", "active", "active", "on hold"],
})
PAYROLL_REGISTER_TOTAL = Decimal("258675.50")   # from the approved payroll summary, active staff

pay["ifsc"] = pay["ifsc"].str.strip().str.upper()
pay["account"] = pay["account"].str.strip()
pay["amount"] = pay["net_pay"].str.replace(",", "", regex=False).map(Decimal)

def problems(r, dup_accounts):
    out = []
    if not re.fullmatch(r"[A-Z]{4}0[A-Z0-9]{6}", r.ifsc):
        out.append("IFSC not in 4 letters + 0 + 6 characters form")
    if not re.fullmatch(r"\d{9,18}", r.account):
        out.append("account number not 9-18 digits")
    if r.account in dup_accounts:
        out.append("account number shared with another employee")
    if r.amount <= 0:
        out.append("net pay zero or negative")
    return "; ".join(out)

active = pay[pay["status"] == "active"].copy()
dups = set(active.loc[active["account"].duplicated(keep=False), "account"])
active["problem"] = [problems(r, dups) for r in active.itertuples()]
held = active[active["problem"] != ""]
ok = active[active["problem"] == ""]

print("HOLD FOR CORRECTION")
print(held[["emp_id", "name", "ifsc", "amount", "problem"]].to_string(index=False))

# Control totals: the file plus held rows must reconcile to the approved register.
file_total, held_total = ok["amount"].sum(), held["amount"].sum()
print(f"\nfile: {len(ok)} rows, {file_total:.2f} | held: {len(held)} rows, {held_total:.2f}")
assert file_total + held_total == PAYROLL_REGISTER_TOTAL, "does not reconcile to payroll"

# Write the upload in the column order your bank's template specifies (illustrative here).
buf = io.StringIO()
w = csv.writer(buf, lineterminator="\n")
w.writerow(["Beneficiary Name", "Account Number", "IFSC", "Amount", "Narration"])
for r in ok.itertuples():
    w.writerow([r.name.upper(), r.account, r.ifsc, f"{r.amount:.2f}", f"SALARY SEP26 {r.emp_id}"])
w.writerow(["TOTAL", len(ok), "", f"{file_total:.2f}", ""])
print(buf.getvalue())

Six active employees go in. Two rows reach the file and four are held:

E207 is on hold in payroll and never enters the check, which is why the approved register total excludes them. The reconciliation line then reads: two rows worth ₹1,19,025.50 in the file plus four rows worth ₹1,39,650.00 held equals ₹2,58,675.50, the approved total. If it did not, the script stops before writing anything.

Why each check is there

Normalise before checking. icic0000401 becomes ICIC0000401. Lower case and stray spaces are formatting noise, not wrong data, and holding a salary over them creates work for nobody's benefit.

Duplicates hold both rows. Paying the first and holding the second assumes the first is right. A shared account number means one of two records is wrong, and only HR knows which.

Amounts as `Decimal`, not float. Salaries are summed to the paisa and compared for equality with the register. Floating-point sums can differ in the last place and turn an exact reconciliation into a tolerance argument.

Account numbers stay text. 000401234567 keeps its leading zeros only because it is never converted to a number. In Excel, format the column as Text before pasting, or a leading-zero account silently loses digits and the credit bounces or, worse, lands in another account.

Helper columns and a control box

With IFSC in C2, account number in B2 (as text) and net pay in D2, three helper columns give the same flags:

IFSC ok     =AND(LEN(C2)=11, MID(C2,5,1)="0", ISERROR(-LEFT(C2,1)), ISERROR(-MID(C2,2,1)), ISERROR(-MID(C2,3,1)), ISERROR(-MID(C2,4,1)))
Dup account =COUNTIF($B$2:$B$500, B2)>1
Pay ok      =D2>0

The first formula tests length, the zero in position five, and that none of the first four characters can be read as a number. It is looser than the regular expression but catches the common mistakes. Then build a small control box at the top of the sheet: count and sum of rows passing all three, count and sum of rows failing, and a cell that shows the difference from the approved total. The upload is sent only when that cell is zero.

After the file is sent

Keep three things with the payroll records for the month: the upload file exactly as sent, the held-rows sheet with the resolution of each, and the portal's acknowledgement showing the count and total it accepted. When an employee says their salary did not arrive, those three answer the question in a minute. Credits that bounce back appear on the bank statement a few days later; matching them to employees is the same work as matching bank entries to the cash book by UTR and cheque number.

Held rows usually come from upstream: attendance and loss-of-pay figures are the other half of the same run, covered in converting an attendance sheet into LOP days for payroll. Writing checks that stop a bad file before it leaves the building, and proving the numbers reconcile, is the kind of routine control work our Data Analyst programme practises on synthetic payroll and finance data.