Tutorials › Python › Reading CSV and Excel Files

Reading CSV and Excel Files

Load data from CSV, Excel, and multiple sheets into Pandas DataFrames

Reading data is the first step of every analysis. Pandas has powerful read functions that handle most real-world file formats with a single line. Knowing the right parameters prevents common issues like wrong data types on load, encoding errors, and extra header/footer rows that come from exported reports.

Examples

pd.read_csv — most important parameters
import pandas as pd

# Basic
df = pd.read_csv("sales_data.csv")

# Common parameters
df = pd.read_csv(
    "sales_data.csv",
    encoding="utf-8",          # or "latin-1" for older Indian exports
    sep=",",                   # delimiter: "," "	" "|" ";"
    header=0,                  # row number of header (0 = first row)
    skiprows=2,                # skip first 2 rows (report titles)
    nrows=1000,                # read only first 1000 rows
    usecols=["Date", "Sales"], # read only specific columns
    parse_dates=["Date"],      # auto-parse date columns
    dtype={"PIN": str},        # force PIN codes to string (not int)
)

# Handle Indian date formats (DD-MM-YYYY)
df["Date"] = pd.to_datetime(df["Date"], format="%d-%m-%Y")

# After loading — always check
print(df.shape)
print(df.dtypes)
print(df.isnull().sum())
💡 dtype={"PIN": str} prevents Pandas from converting "011" to 11 — leading zeros in PIN codes, IDs, and phone numbers must stay as strings.
pd.read_excel — working with Excel files
# Single sheet
df = pd.read_excel("report.xlsx")
df = pd.read_excel("report.xlsx", sheet_name="Sales Q1")

# All sheets at once → returns dict of {sheet_name: DataFrame}
all_sheets = pd.read_excel("report.xlsx", sheet_name=None)
sales_df = all_sheets["Sales Q1"]
cost_df  = all_sheets["Costs Q1"]

# Skip header rows (common in formatted Excel reports)
df = pd.read_excel("report.xlsx", skiprows=3, header=0)

# Read specific columns by name
df = pd.read_excel("report.xlsx", usecols=["Date", "Region", "Amount"])

# Combine all sheets into one DataFrame
import pandas as pd
dfs = pd.read_excel("data.xlsx", sheet_name=None)
combined = pd.concat(dfs.values(), ignore_index=True)

Key Points

  • ✓pd.read_csv() and pd.read_excel() are your primary data loading functions
  • ✓Always check df.dtypes after loading — wrong types are the #1 source of calculation errors
  • ✓encoding="latin-1" fixes UnicodeDecodeError on files exported from older Indian systems
  • ✓parse_dates=["Date"] saves you from manually converting date columns after loading
  • ✓dtype={"col": str} forces a column to stay as text — critical for ID and code columns

Practice Question

Your CSV file has a "PinCode" column that Pandas reads as integer (011041 becomes 11041). How do you fix this on load?

Related Topics

Pandas DataFramesThe core Pandas data structure — a 2D table with rows and columnsData Cleaning with PandasRename columns, fix data types, remove duplicates, and standardise messy real-world dataFile Handling in PythonRead and write text files, CSV files, and automate file operations for data pipelines