Tutorials › Python › Reading CSV and Excel Files with pandas

Reading CSV and Excel Files with pandas

Load real-world files correctly — handle encodings, date columns, and multi-sheet Excel

Reading files is the first step of every real data project. In practice, files are messy — wrong encodings, extra header rows, inconsistent null representations, date strings, mixed types. This tutorial covers the parameters you actually need on the job.

Examples

read_csv with real-world options
import pandas as pd

# Minimal (clean CSV)
df = pd.read_csv("sales.csv")

# Production-grade (handles common issues)
df = pd.read_csv(
    "sales.csv",
    encoding="utf-8",          # try "latin-1" if this errors
    sep=",",                   # "\t" for TSV
    header=0,                  # row number to use as headers (0=first)
    skiprows=3,                # skip first 3 rows
    usecols=["Date","Region","Amount"],  # load only these columns
    parse_dates=["Date"],      # auto-parse to datetime
    dayfirst=True,             # for DD/MM/YYYY format
    na_values=["NA","N/A","--","null","None",""],
    thousands=",",             # "1,000" → 1000
    dtype={"PIN": str},        # keep as string to preserve leading zeros
    nrows=5000,                # load only first 5000 rows
    low_memory=False           # avoid DtypeWarning on mixed columns
)
read_excel with sheet selection
# Single sheet
df = pd.read_excel("report.xlsx", sheet_name="Sales Q1")

# By position (0=first sheet)
df = pd.read_excel("report.xlsx", sheet_name=0)

# Load all sheets as a dictionary
sheets = pd.read_excel("report.xlsx", sheet_name=None)
# sheets.keys() → dict_keys(['Sales Q1', 'Sales Q2', 'Summary'])
df_q1 = sheets["Sales Q1"]

# Combine all sheets that match a pattern
all_dfs = []
for name, df in sheets.items():
    if name.startswith("Sales"):
        df["quarter"] = name
        all_dfs.append(df)
combined = pd.concat(all_dfs, ignore_index=True)

Key Points

  • ✓UnicodeDecodeError → try encoding="latin-1" (common with Hindi/Indian data)
  • ✓dtype={"PinCode": str} keeps leading zeros in postal codes — otherwise read as int
  • ✓parse_dates=["DateCol"] converts string dates to datetime — required for time analysis
  • ✓na_values extends the default null list (NaN, None, empty) with custom nulls like "--"
  • ✓low_memory=False avoids mixed-type column warnings on large files

Practice Question

A PIN code column (e.g. "011001") is being loaded as the integer 11001, losing the leading zero. Which read_csv parameter fixes this?