Reading & writing Excel, CSV and SQL

Most business data in India still moves around as Excel workbooks and CSV exports from Tally, billing software or ERP systems. Knowing the options of pandas' readers and writers saves hours of manual fixing.

CSV: the options that matter

import pandas as pd

sales = pd.read_csv(
    "billing_export.csv",
    usecols=["bill_no", "bill_date", "branch", "amount"],   # load only what you need
    dtype={"bill_no": "string", "branch": "category"},
    parse_dates=["bill_date"],
    dayfirst=True,              # 05/04/2025 means 5 April, not 4 May
    thousands=",",              # "1,25,000" becomes 125000
    encoding="utf-8",
)

Problem	Option
File from Excel on Windows shows UnicodeDecodeError	encoding="cp1252" or "utf-8-sig"
Semicolon or pipe separated	sep=";" or sep="|"
Junk lines above the header	skiprows=3
"NA", "-", "nil" meaning missing	na_values=["-", "nil"]
File too big for memory	chunksize=100_000 and process in a loop

For large files, read in chunks and aggregate as you go:

totals = []
for chunk in pd.read_csv("big.csv", chunksize=200_000):
    totals.append(chunk.groupby("branch")["amount"].sum())
branch_total = pd.concat(totals).groupby(level=0).sum()

When writing a CSV that colleagues will open in Excel, use df.to_csv("out.csv", index=False, encoding="utf-8-sig") so the ₹ symbol and Hindi text display correctly.

Excel: sheets, headers and multiple outputs

Reading and writing .xlsx needs the openpyxl package (pip install openpyxl).

# One sheet
jan = pd.read_excel("sales_2025.xlsx", sheet_name="Jan", skiprows=2)

# Every sheet at once -> dict of {sheet_name: DataFrame}
sheets = pd.read_excel("sales_2025.xlsx", sheet_name=None)
year = pd.concat(sheets, names=["month", "row"]).reset_index(level="month")

# Several DataFrames into one workbook
with pd.ExcelWriter("report.xlsx") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    year.to_excel(writer, sheet_name="Detail", index=False)

sheet_name=None is the quickest way to combine twelve monthly tabs into a single table, with a column telling you which month each row came from.

SQL databases

pandas talks to databases through SQLAlchemy (or a plain sqlite3 connection for SQLite):

from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///shop.db")

# Write: create or append to a table
sales.to_sql("sales", engine, if_exists="append", index=False, chunksize=5_000)

# Read with a safe parameter
q = text("SELECT branch, SUM(amount) AS total FROM sales WHERE branch = :b GROUP BY branch")
pune = pd.read_sql(q, engine, params={"b": "Pune"})

if_exists can be "fail" (default), "replace" (drops the table) or "append". Be careful with "replace": it deletes existing data and indexes.

A faster format for yourself: Parquet

For intermediate files that only your code reads, df.to_parquet("sales.parquet") (needs pyarrow) is much smaller and faster than CSV and keeps dtypes, including dates and categories.

Common mistakes

• Forgetting dayfirst=True, so Indian dates are parsed as month-first.
• Writing the index to CSV and getting a mystery Unnamed: 0 column when reading back; use index=False.
• Loading every column of a 2 GB file when you need four.
• Building SQL with f-strings instead of parameters.