Pandas read_excel vs read_csv: Choose the Right Reader

Formulas & functions · Updated November 20, 2026

Use pandas.read_csv() for delimited text files such as CSV, and pandas.read_excel() for Excel workbooks such as XLSX. The key difference is that CSV is plain text containing rows and fields, while an Excel file can contain multiple named sheets, cell formatting, formulas, and other workbook features; choose the reader that matches the file, not just the data you hope to extract.

The Main Difference: Text File or Workbook

CSV stands for comma-separated values, although real CSV-like files can use tabs, semicolons, or another delimiter. The file stores text arranged in records and fields. It has no built-in concept of worksheets, cell colors, formulas, or column formatting. A CSV file can be opened in a text editor, and its content can be inspected without spreadsheet software.

An Excel workbook is a structured document. An XLSX file can hold multiple worksheets, cell values, formulas, formatting, and workbook-level features. It is not a plain-text table. Consequently, pandas needs an Excel-reading engine to interpret the workbook before it can return tabular data as a DataFrame.

Both functions ultimately help load tabular data into pandas, but they do not have identical options or behavior. CSVs are usually simpler to exchange and process as text. Excel files are useful when the workbook’s organization matters, such as when a report puts different regions on separate sheets.

Basic Syntax and Choosing a Sheet

The simplest calls accept a file path. For example:

import pandas as pd

df_csv = pd.read_csv('sales.csv')
df_excel = pd.read_excel('sales.xlsx', sheet_name='January')

By default, read_csv() reads a single delimited file, and read_excel() reads the first worksheet. If an Excel workbook has several sheets, pass a sheet name or its zero-based position. For example, sheet_name=0 selects the first sheet, while sheet_name='January' selects the sheet with that name.

To read every worksheet, use sheet_name=None. The result is a dictionary mapping sheet names to DataFrames rather than one DataFrame. That distinction matters: code expecting a DataFrame may need to loop over the dictionary or select one entry. CSV has no worksheet option; if information is split across multiple CSV files, read each file separately and combine the resulting DataFrames as appropriate.

How Pandas Interprets Rows and Columns

Both readers normally treat the first row as column headers. If a file has no header row, specify header=None; you can provide names with the names argument. This avoids pandas using the first data record as labels. Both functions also support options such as usecols to select columns and nrows to limit how many rows are read, though accepted values can differ by format and engine.

CSV parsing has text-specific controls. The default delimiter is a comma, but sep=';' handles a semicolon-delimited file, and sep='\t' handles tab-separated data. Quoted fields may contain delimiter characters, so a CSV should be parsed with the reader rather than split manually on commas. For example, a field containing "Smith, Alex" is one value when correctly quoted.

Excel parsing has workbook-specific controls. In addition to choosing a sheet, you can select a range of columns with usecols, skip introductory rows with skiprows, or specify a row for headers. Excel cells may include blank rows, titles, or notes above a table, so inspect the sheet layout before assuming its first row contains the column names.

Data Types, Missing Values, and Dates

Neither format guarantees that pandas will infer every column exactly as intended. A CSV contains text representations, so a column with values such as 00123 can be interpreted as an integer and lose its leading zeros. If those values are identifiers, read the column as text with dtype, for example pd.read_csv('codes.csv', dtype={'code': 'string'}). Excel cells may carry numeric or date values, but identifiers stored as numbers can still lose meaningful display formatting such as leading zeros when read as data.

For a CSV, dates are commonly stored as strings. Use parse_dates for columns pandas should parse as dates, then check the resulting dtype and values. Excel date cells are generally interpreted from the workbook’s stored cell values and date formatting, but mixed or unusual content can still yield unexpected results. For either file, inspect with df.dtypes and a sample such as df.head() before analysis.

Missing-value markers also require care. Pandas recognizes common representations such as empty fields and certain text markers, but a literal value like NA might be a valid code in a particular dataset. The na_values and keep_default_na options help control CSV interpretation; Excel has corresponding missing-value handling options. Decide what counts as missing based on the data dictionary, not just on what looks blank.

Performance, File Size, and Memory

For large tabular data, CSV is often the more practical of these two choices. It is relatively straightforward to parse and can be read in pieces with chunksize, which returns an iterator of DataFrame chunks. This can help process data that would be too large to load all at once, although the code must handle each chunk and the total work still takes time.

read_excel() does not provide the same chunksize workflow. A large workbook may take longer to parse and consume substantial memory, particularly when it contains many populated cells or complex layouts. If you control the data export and need repeated analysis of a large clean table, a text or columnar data format may be more suitable than a workbook. If you must use Excel, select only the needed sheet, columns, and rows where possible.

File size alone is not a reliable speed comparison. Workbook structure, compression, parsing engine, data types, and the number of cells all affect runtime. For a one-off small file, choose based on the actual format and clarity. For a recurring pipeline, benchmark representative files and consider whether the source can provide a more analysis-friendly format.

Excel Engines and Required Packages

Pandas relies on an engine to read Excel formats. For modern XLSX files, openpyxl is a common choice and may need to be installed separately. Other supported engines and format support depend on the pandas version and installed optional packages: for example, calamine can read several spreadsheet formats, xlrd is for older XLS files, and pyxlsb is used for XLSB files. Check the pandas documentation for the current support matrix and installation requirements.

You can specify an engine when needed, as in pd.read_excel('report.xlsx', engine='openpyxl'). If pandas reports that an optional dependency is missing, install the package in the same Python environment that runs the script, then try again. An engine mismatch can also cause an error if the chosen engine does not support the file’s format.

read_csv() does not need an Excel engine. Its main complications are more often delimiter, encoding, quoting, or malformed rows. The encoding argument can help with text files created in different character encodings; UTF-8 is common, but the correct choice depends on the source. Avoid guessing if names or symbols appear corrupted: confirm the export’s encoding where possible.

Common Errors and How to Diagnose Them

A frequent CSV problem is a file that appears to load into one wide column or many nonsensical columns. The delimiter may not be a comma, or the file may use a different quoting convention. Check a few raw lines in a text editor and set options such as sep or quotechar accordingly. A Unicode decoding error points to an encoding mismatch rather than an Excel issue.

For Excel, “file format cannot be determined” or missing-engine errors can indicate an unsupported extension, a missing optional dependency, or a file that is not actually the format its name suggests. Confirm the file is a valid workbook and use an appropriate engine. A workbook password or encryption may also prevent ordinary reading; pandas is not a general tool for bypassing workbook protection.

Unexpected headers often come from title rows or merged cells above the actual table. Inspect the sheet and adjust skiprows or header. When data looks wrong after loading, check df.shape, df.columns, df.dtypes, and a few representative rows. These quick checks reveal many parsing problems before they affect calculations.

Formulas, Formatting, and What Pandas Does Not Preserve

Reading an Excel workbook into a DataFrame extracts tabular cell values; it does not reproduce the workbook as an editable spreadsheet. Formatting such as font color, borders, and column widths is not represented in a normal DataFrame. If the task depends on workbook appearance or layout, pandas may not be the right tool by itself.

Formula cells need particular attention. Pandas does not calculate Excel formulas. Depending on the engine and options, it can read formula text or the cached result last saved by spreadsheet software; a cached result may be missing or stale if the workbook was not recalculated and saved. If correct formula results are essential, recalculate the workbook in a spreadsheet application or use a workflow that explicitly handles formulas before importing values.

CSV has no formulas or formatting to preserve. When an Excel sheet is exported as CSV, the export represents one sheet as delimited text and omits workbook features. Thus, choosing CSV can simplify data exchange, but it is not a lossless substitute for an Excel workbook.

How to Choose the Right Reader

Use the file’s actual format as the starting point. Do not call read_csv() on an XLSX file simply because it contains a table, or call read_excel() on a CSV because Excel can open it. The extensions offer a quick clue, but a misleading extension does not change the file’s underlying structure.

  • Choose read_csv() for CSV, TSV, and similar plain-text tables, especially when you need delimiter and encoding controls or chunked processing.
  • Choose read_excel() when the source is an Excel workbook and you need a particular worksheet or a table stored in the workbook.
  • For a recurring, large-scale pipeline, ask whether the source can export a clean CSV or another suitable data format instead of a manually maintained workbook.
  • When a workbook contains several sheets, determine whether they share the same columns before combining them; similarly named sheets do not guarantee identical structure.

A useful rule is to separate the data from its presentation. If the workbook is mainly a container for a clean table, importing its relevant sheet is reasonable. If the workbook’s formulas, formatting, merged layout, or human-readable report design are central to the task, extracting a DataFrame may discard information you need.

Frequently Asked Questions

Can pandas read an Excel file with read_csv()?
No. read_csv() parses delimited text, not the binary or zipped workbook structure used by Excel formats. Use read_excel() for supported workbook files.

Can read_excel() read a CSV file?
Use read_csv() for CSV. Although spreadsheet software can open CSV files, that does not make them Excel workbooks, and read_excel() expects a supported spreadsheet format.

How do I read all sheets in an Excel workbook?
Pass sheet_name=None to read_excel(). Pandas returns a dictionary of DataFrames keyed by sheet name, which you can then inspect or process one sheet at a time.

Which is faster, read_csv() or read_excel()?
There is no universal speed winner for every file and setup, but CSV is often simpler and more efficient for large plain tables, and it supports chunked reading. Benchmark the real workload if speed matters.

Does pandas keep Excel formatting or calculate formulas?
No. A DataFrame contains values arranged as rows and columns, not the workbook’s visual formatting. Pandas also does not calculate formulas; formula text or saved cached values may be read depending on the engine and settings.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.