Pandas read_excel(): Import, Inspect and Validate Workbooks
pd.read_excel() reads an Excel worksheet into a DataFrame. Select the worksheet and header layout first, then choose columns and types. Inspect missing values, identifiers and dates before creating reports or database tables.
Create the Practice Workbook
The first sheet contains five student records matching the introductory sample. Other sheets demonstrate title rows, missing values, text dates and order imports. For a larger dataset, use the student sample Excel file. The practice workbook is synthetic and contains no real student data.
import pandas as pd
from pathlib import Path
folder = Path("pandas_read_excel_practice")
folder.mkdir(exist_ok=True)
path = folder / "practice.xlsx"
def student_data():
return pd.DataFrame({"id": [1, 2, 3, 4, 5],
"name": ["John Deo", "Max Ruin", "Arnold", "Krish Star", "John Mike"],
"class": ["Four", "Three", "Three", "Four", "Four"],
"mark": [75, 85, 55, 60, 60],
"gender": ["female", "male", "male", "female", "female"]})
orders_source = pd.DataFrame({"order_id": ["00101", "00102", "00103", "00104"],
"date_raw": ["01/01/2024", "15/01/2024", "31/02/2024", "20/01/2024"],
"amount": [120.5, 80.0, 50.0, None]})
with pd.ExcelWriter(path, engine="openpyxl") as writer:
student_data().to_excel(writer, sheet_name="students", index=False)
student_data().iloc[:2].to_excel(writer, sheet_name="second_group", index=False)
student_data().to_excel(writer, sheet_name="title_rows", startrow=2, index=False)
student_data().to_excel(writer, sheet_name="no_header", header=False, index=False)
student_data().to_excel(writer, sheet_name="footer", index=False)
writer.sheets["footer"].append(["End of report"])
orders_source.to_excel(writer, sheet_name="orders", index=False)
pd.DataFrame({"code": ["NA", "UNKNOWN", "OK"], "mark": [75, "?", 60]}).to_excel(writer, sheet_name="missing", index=False)
pd.DataFrame({"date_text": ["01/01/2024", "15/01/2024"]}).to_excel(writer, sheet_name="dates", index=False)
pd.DataFrame({"amount_text": ["1.234,50", "20,25"]}).to_excel(writer, sheet_name="numbers", index=False)
print("Created:", path.as_posix())Expected output
Created: pandas_read_excel_practice/practice.xlsxRead the First Sheet into a DataFrame
By default sheet_name=0 reads the first worksheet and header=0 uses the first row as column labels. head() previews rows; it does not limit the import. Here openpyxl reads the .xlsx workbook.
df = pd.read_excel(path, engine="openpyxl")
print(df.head())
assert df.equals(student_data())Expected output
id name class mark gender
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 male
2 3 Arnold Three 55 male
3 4 Krish Star Four 60 female
4 5 John Mike Four 60 femaleRead a Named Sheet, Several Sheets or All Sheets
A sheet name or zero-based position returns one DataFrame. A list or None returns a dictionary of DataFrames. Inspect sheet names with ExcelFile before selecting an unfamiliar workbook. A context manager closes the workbook after use.
with pd.ExcelFile(path, engine="openpyxl") as workbook:
print("Sheet names:", workbook.sheet_names)
second = pd.read_excel(workbook, sheet_name="second_group")
selected = pd.read_excel(workbook, sheet_name=["students", "second_group"])
all_sheets = pd.read_excel(workbook, sheet_name=None)
print("Second group records:", len(second))
print("Selected sheet keys:", list(selected))
assert len(second) == 2 and len(all_sheets) == 9Expected output
Sheet names: ['students', 'second_group', 'title_rows', 'no_header', 'footer', 'orders', 'missing', 'dates', 'numbers']
Second group records: 2
Selected sheet keys: ['students', 'second_group']header and names: Match the Workbook Layout
header is a zero-based worksheet row after skipped rows are removed. Use header=2 for this sheet with two leading blank rows. For a genuinely headerless sheet, use header=None and names. To replace existing headers, use header=0 with names; header=None would import the original header as a data record.
df = pd.read_excel(path, sheet_name="title_rows", header=2)
assert df.equals(student_data())
labels = ["student_id", "student_name", "class", "score", "gender"]
no_header = pd.read_excel(path, sheet_name="no_header", header=None, names=labels)
renamed = pd.read_excel(path, sheet_name="students", header=0, names=labels)
print(no_header.head(2))
assert no_header.equals(renamed)
assert len(renamed) == 5Expected output
student_id student_name class score gender
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 maleusecols: Names, Positions, Letters and a Callable
Select only required fields. Excel letters are one-based labels such as A and B; integer positions are zero-based. usecols does not guarantee the requested list order: reorder columns after reading when output order matters.
by_name = pd.read_excel(path, usecols=["id", "name", "mark"])
by_position = pd.read_excel(path, usecols=[0, 1, 3])
by_letter = pd.read_excel(path, usecols="A:B,D")
by_rule = pd.read_excel(path, usecols=lambda column: column in {"id", "name", "mark"})
assert by_name.equals(by_position) and by_name.equals(by_letter) and by_name.equals(by_rule)
print(by_name[["name", "id", "mark"]].head(2))Expected output
name id mark
0 John Deo 1 75
1 Max Ruin 2 85index_col and Label Lookup
index_col="id" uses the ID values as row labels. This differs from an ordinary default row index. Validate uniqueness before label lookup. For merged or missing index cells, reading first and using set_index later avoids automatic forward filling associated with index_col.
df = pd.read_excel(path, index_col="id")
assert df.index.is_unique
print(df.loc[3])
print("Columns:", df.columns.tolist())
assert df.loc[3, "name"] == "Arnold"Expected output
name Arnold
class Three
mark 55
gender male
Name: 3, dtype: object
Columns: ['name', 'class', 'mark', 'gender']dtype: Scores versus Identifiers
Use dtype for an explicit column type. Read textual order IDs as strings to retain their stored leading zeros. If Excel stores 101 numerically and merely displays it as 00101, dtype="string" cannot recover the display format. Decide the identifier policy from the source contract.
df = pd.read_excel(path, dtype={"mark": "float64"})
print(df.dtypes)
orders = pd.read_excel(path, sheet_name="orders", dtype={"order_id": "string"})
print(orders["order_id"].tolist())
assert orders.loc[0, "order_id"] == "00101"
assert str(df["mark"].dtype) == "float64"Expected output
id int64
name str
class str
mark float64
gender str
dtype: object
['00101', '00102', '00103', '00104']converters: Transform Individual Cells
Converters are applied instead of dtype conversion for the same column. Use them for a deliberate transformation, not to hide malformed data. These score increases and name prefixes are demonstration transformations; they change the imported values. Calling info() prints the summary directly.
df = pd.read_excel(path, converters={"mark": lambda value: value + 10})
assert df.loc[0, "mark"] == 85
other = pd.read_excel(path, converters={"mark": lambda value: value + 2,
"name": lambda value: "Mr " + value})
print(other.head(2))
assert other.loc[0, "mark"] == 77
other.info()Expected output
id name class mark gender
0 1 Mr John Deo Four 77 female
1 2 Mr Max Ruin Three 87 male
<class 'pandas.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 id 5 non-null int64
1 name 5 non-null str
2 class 5 non-null str
3 mark 5 non-null int64
4 gender 5 non-null str
dtypes: int64(2), str(3)
memory usage: 332.0 bytesskiprows: Preserve the Header
skiprows uses zero-based source row positions. A list removes particular rows; an integer removes a number of leading rows. A callable must deliberately retain the header if it is still needed. Skipping row zero without adjusting header can turn a student record into column names.
df = pd.read_excel(path, skiprows=[2, 3])
print("After skipping source rows 2 and 3:", df["id"].tolist())
assert df["id"].tolist() == [1, 4, 5]
from_title = pd.read_excel(path, sheet_name="title_rows", skiprows=2)
assert from_title.equals(student_data())
odd_records = pd.read_excel(path, skiprows=lambda row: row > 0 and row % 2 == 0)
print("Callable keeps IDs:", odd_records["id"].tolist())
assert odd_records["id"].tolist() == [1, 3, 5]Expected output
After skipping source rows 2 and 3: [1, 4, 5]
Callable keeps IDs: [1, 3, 5]nrows and skipfooter
nrows limits data records and excludes the header. skipfooter removes trailing worksheet rows, such as a report note. Confirm the footer layout before dropping rows; it is not a substitute for checking imported records.
first_two = pd.read_excel(path, nrows=2)
without_footer = pd.read_excel(path, sheet_name="footer", skipfooter=1)
print(first_two)
assert len(first_two) == 2
assert without_footer.equals(student_data())Expected output
id name class mark gender
0 1 John Deo Four 75 female
1 2 Max Ruin Three 85 malena_values and keep_default_na
Default missing markers include the literal text NA. If NA is a valid code, keep_default_na=False preserves it. Specify additional missing markers per column, including blanks where appropriate. na_filter=False disables missing-marker detection and makes these NA options ineffective.
df = pd.read_excel(path, sheet_name="missing", keep_default_na=False,
na_values={"mark": ["?", ""]})
print(df)
assert df.loc[0, "code"] == "NA"
assert pd.isna(df.loc[1, "mark"])
default = pd.read_excel(path, sheet_name="missing")
assert pd.isna(default.loc[0, "code"])Expected output
code mark
0 NA 75.0
1 UNKNOWN NaN
2 OK 60.0Date Parsing and Reviewable Conversion
For uniformly valid text dates, parse_dates and date_format can parse during import. For mixed or invalid dates, preserve the raw column and use to_datetime() afterward with an explicit format and errors policy. This separates import from validation.
valid = pd.read_excel(path, sheet_name="dates", parse_dates=["date_text"], date_format="%d/%m/%Y")
print(valid)
assert valid.loc[1, "date_text"] == pd.Timestamp("2024-01-15")
orders = pd.read_excel(path, sheet_name="orders", dtype={"order_id": "string", "date_raw": "string"})
orders["order_date"] = pd.to_datetime(orders["date_raw"], format="%d/%m/%Y", errors="coerce")
print(orders[["order_id", "date_raw", "order_date"]])
assert orders["order_date"].isna().sum() == 1Expected output
date_text
0 2024-01-01
1 2024-01-15
order_id date_raw order_date
0 00101 01/01/2024 2024-01-01
1 00102 15/01/2024 2024-01-15
2 00103 31/02/2024 NaT
3 00104 20/01/2024 2024-01-20Text Numbers with Decimal and Thousands Separators
decimal and thousands apply to numbers stored as text. Numeric Excel cells are read as numbers regardless of their display formatting. Do not assume a comma means the same thing in every source file.
df = pd.read_excel(path, sheet_name="numbers", decimal=",", thousands=".")
print(df)
assert df["amount_text"].sum() == 1254.75Expected output
amount_text
0 1234.50
1 20.25Practical Import: Validate before Reporting
Validate the expected schema, ID uniqueness, dates and missing amounts before building a report. Keep rejected records available for review. This synthetic workbook contains one impossible date and one unknown amount, so only two records are accepted. Missing amounts are not zero revenue.
orders = pd.read_excel(path, sheet_name="orders", dtype={"order_id": "string", "date_raw": "string"})
assert {"order_id", "date_raw", "amount"}.issubset(orders.columns)
assert orders["order_id"].notna().all() and orders["order_id"].is_unique
orders["order_date"] = pd.to_datetime(orders["date_raw"], format="%d/%m/%Y", errors="coerce")
orders["needs_review"] = orders["order_date"].isna() | orders["amount"].isna()
accepted = orders.loc[~orders["needs_review"]].copy()
review = orders.loc[orders["needs_review"]].copy()
print("Accepted records:", len(accepted))
print("Review IDs:", review["order_id"].tolist())
print("Accepted revenue:", accepted["amount"].sum(min_count=1))
assert len(accepted) + len(review) == len(orders) == 4
assert accepted["amount"].sum() == 200.5
assert orders["amount"].sum(min_count=1) == 250.5Expected output
Accepted records: 2
Review IDs: ['00103', '00104']
Accepted revenue: 200.5Save Accepted and Review Records Separately
Write a new workbook instead of overwriting the source. Reopen it to check IDs and row counts. The 50.00 on the invalid-date record stays in the review sheet; it is excluded from the accepted revenue. Continue with to_excel().
report_path = folder / "reviewed_orders.xlsx"
with pd.ExcelWriter(report_path, engine="openpyxl") as writer:
accepted.to_excel(writer, sheet_name="accepted", index=False)
review.to_excel(writer, sheet_name="review", index=False)
restored = pd.read_excel(report_path, sheet_name=None, dtype={"order_id": "string"})
assert len(restored["accepted"]) == len(accepted)
assert len(restored["review"]) == len(review)
assert restored["accepted"]["order_id"].tolist() == ["00101", "00102"]
print("Verified output:", report_path.as_posix())Expected output
Verified output: pandas_read_excel_practice/reviewed_orders.xlsxRead Excel and Create a SQLite Table
Use an embedded practice database for an immediate to_sql() example. A fresh in-memory database makes repeated practice runs independent. For a persistent database, decide how repeat imports and duplicate IDs should be handled before appending.
import sqlite3
df = pd.read_excel(path)
with sqlite3.connect(":memory:") as conn:
df.to_sql("student", conn, index=False, if_exists="fail")
restored = pd.read_sql_query("SELECT id, name, mark FROM student ORDER BY id", conn)
print(restored.head(2))
assert len(restored) == len(df)
assert restored["mark"].sum() == df["mark"].sum() == 335Expected output
id name mark
0 1 John Deo 75
1 2 Max Ruin 85Unique Values, Lists and HTML Output
unique() returns an array of distinct values; call tolist for a Python list. A Series can be converted directly with tolist(). Export a complete escaped HTML table with to_html() rather than concatenating raw text into markup.
df = pd.read_excel(path)
print("Unique classes:", df["class"].unique().tolist())
print("Names:", df["name"].tolist())
df[["name", "class", "mark", "gender"]].to_html(folder / "students.html", index=False, escape=True)
assert (folder / "students.html").read_text().count("<tr") == len(df) + 1Expected output
Unique classes: ['Four', 'Three']
Names: ['John Deo', 'Max Ruin', 'Arnold', 'Krish Star', 'John Mike']Create a Two-Sheet Student Demo
Use ExcelWriter to create the original small NAME/ID/MATH/ENGLISH example in two sheets. index=False prevents an extra saved row-index column. Read each sheet back to verify the sheet name and contents.
first = pd.DataFrame({"NAME": ["Ravi", "Raju", "Alex"], "ID": [1, 2, 3],
"MATH": [30, 40, 50], "ENGLISH": [20, 30, 40]})
second = pd.DataFrame({"NAME": ["Ron", "Pick", "Rabin"], "ID": [4, 5, 6],
"MATH": [60, 40, 30], "ENGLISH": [30, 40, 50]})
demo_path = folder / "my_file.xlsx"
with pd.ExcelWriter(demo_path, engine="openpyxl") as writer:
first.to_excel(writer, sheet_name="my_Sheet_1", index=False)
second.to_excel(writer, sheet_name="my_Sheet_2", index=False)
assert pd.read_excel(demo_path, sheet_name="my_Sheet_1").equals(first)
assert pd.read_excel(demo_path, sheet_name=1).equals(second)
print("Verified two sheets:", demo_path.as_posix())Expected output
Verified two sheets: pandas_read_excel_practice/my_file.xlsxOptional: Upload Your Own Workbook in Colab
Use the Files sidebar to upload a workbook, then update the path. This optional cell opens the Colab upload dialog. A Windows path refers to your computer and will not work in a remote Colab runtime. Read more about Colab practice.
RUN_COLAB_UPLOAD = False
if RUN_COLAB_UPLOAD:
from google.colab import files
uploaded = files.upload()
for filename in uploaded:
if Path(filename).suffix.lower() == ".xlsx":
own_data = pd.read_excel(filename, engine="openpyxl")
print(own_data.head())Desktop: Browse for an Excel File
Tkinter opens a local desktop window, so this example is disabled in the notebook. Choose an .xlsx workbook, cancel safely, and display a preview. See file dialogs and Text widgets.
RUN_DESKTOP_PREVIEW = False
if RUN_DESKTOP_PREVIEW:
import tkinter as tk
from tkinter import filedialog, messagebox
window = tk.Tk()
window.title("plus2net.com — Excel preview")
output = tk.Text(window, height=12, width=75)
output.pack(padx=10, pady=10)
def browse_workbook():
selected = filedialog.askopenfilename(filetypes=[("Excel workbook", "*.xlsx")])
if not selected:
return
try:
data = pd.read_excel(selected, engine="openpyxl")
except (OSError, ValueError, ImportError) as error:
messagebox.showerror("Import failed", str(error))
return
output.delete("1.0", tk.END)
output.insert(tk.END, data.head().to_string(index=False))
tk.Button(window, text="Browse Excel file", command=browse_workbook).pack(pady=10)
window.mainloop()Desktop: Select Excel and Create a SQLite Table
This desktop example creates a new practice table, without appending repeated imports or replacing an existing table. The queryable data uses a persistent SQLite file in the practice folder. For MySQL, follow the SQLAlchemy connection guide and define credentials outside the code. Validate a real workbook before importing it.
RUN_DESKTOP_SQL = False
if RUN_DESKTOP_SQL:
import tkinter as tk
from tkinter import filedialog, messagebox
import sqlite3
window = tk.Tk()
window.title("plus2net.com — Excel to SQLite")
status = tk.Label(window, text="Choose a workbook for a new practice table")
status.pack(padx=20, pady=20)
def import_workbook():
selected = filedialog.askopenfilename(filetypes=[("Excel workbook", "*.xlsx")])
if not selected:
return
try:
data = pd.read_excel(selected, engine="openpyxl")
with sqlite3.connect(folder / "desktop_excel.sqlite") as conn:
data.to_sql("excel_import", conn, index=False, if_exists="fail")
except (OSError, ValueError, ImportError, sqlite3.Error) as error:
messagebox.showerror("Import failed", str(error))
return
status.config(text=f"Created excel_import with {len(data)} records")
tk.Button(window, text="Browse and import", command=import_workbook).pack(pady=10)
window.mainloop()Desktop: Look Up Student Details by ID
Read the practice workbook and use loc for ID lookup. Validate the ID and report missing records instead of failing on an empty or unknown entry. Related example: search a DataFrame in Tkinter.
RUN_DESKTOP_LOOKUP = False
if RUN_DESKTOP_LOOKUP:
import tkinter as tk
data = pd.read_excel(path, index_col="id")
assert data.index.is_unique
window = tk.Tk()
window.title("plus2net.com — Student details")
tk.Label(window, text="Student ID").pack()
entry = tk.Entry(window)
entry.pack()
details = tk.Label(window, text="Enter an ID from 1 to 5", justify="left")
details.pack(padx=15, pady=15)
def show_record():
try:
student_id = int(entry.get())
except ValueError:
details.config(text="Enter a whole-number ID.")
return
if student_id not in data.index:
details.config(text="Student ID not found.")
return
details.config(text=data.loc[student_id].to_string())
tk.Button(window, text="Show details", command=show_record).pack()
window.mainloop()
Google Sheets Is a Separate Source
For live Google Sheets, use the Google Sheets API workflow and get_as_df(). read_excel is for workbook files; a normal Google Sheets sharing page is not an Excel file. You can also download a sheet as .xlsx and import that file.
Import Problems and Engine Choices
File not found: inspect the runtime folder and selected filename. Missing openpyxl: install the engine in the environment running the code. Wrong columns: inspect header and skiprows. Unexpected extra index: export with index=False or deliberately select the saved index. Formula results missing: Pandas does not calculate workbook formulas; cached results can be absent or stale. Format support: openpyxl handles .xlsx; xlrd handles older .xls, and other engines serve .xlsb or OpenDocument files. Choose an engine that supports the actual format. Large imports can use usecols and nrows to limit work; read_excel has no chunksize option.
Exercise: Import a Selected Student Report
Read students with only id, name and mark, use id as the index, keep mark numeric and display students scoring at least 60. Predict four selected records and a selected-score total of 280.
Exercise Solution
Check schema and totals before using the imported table. The complete five-record source score total is 335; the filtered total is a different measure.
df = pd.read_excel(path, sheet_name="students", usecols=["id", "name", "mark"],
index_col="id", dtype={"mark": "float64"})
selected = df.loc[df["mark"].ge(60)]
print(selected)
assert selected.index.tolist() == [1, 2, 4, 5]
assert selected["mark"].sum() == 280
assert df["mark"].sum() == 335Expected output
name mark
id
1 John Deo 75.0
2 Max Ruin 85.0
4 Krish Star 60.0
5 John Mike 60.0Check Your Understanding
Try the sheet selection, header, column, dtype and missing-value options on the practice workbook.
- How do you read an Excel file using the
read_excel()function in Pandas? - What is the default value for the
sheet_nameparameter in theread_excel()function? - How can you read a specific sheet from an Excel file using the
read_excel()function? - What is the purpose of the
headerparameter in theread_excel()function? - How can you skip a specific number of rows while reading an Excel file using the
read_excel()function? - How do you specify a subset of columns to read from an Excel file using the
read_excel()function? - How can you handle missing values while reading an Excel file with the
read_excel()function? - What is the purpose of the
dtypeparameter in theread_excel()function? - How do you read multiple sheets from an Excel file into separate DataFrames using the
read_excel()function? - How can you customize the column names of the resulting DataFrame when reading an Excel file with the
read_excel()function?
Practice in Google Colab
Open in Google Colab View on GitHub
Create the sample workbook, run the import examples and inspect the accepted and review sheets. Save a copy in Drive before editing.
Continue with Pandas input and output, read_csv(), to_excel(), read_sql() and data cleaning. Reference: Pandas read_excel documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials