How to Read CSV and Excel Files With Pandas

1How Do You Read CSV and Excel Files in Pandas?

Read a CSV with pd.read_csv('file.csv') and an Excel workbook with pd.read_excel('file.xlsx', sheet_name='Sheet1'). Both functions parse the file and return a DataFrame you can immediately explore, filter, and analyse. For anything beyond a clean file, a handful of parameters handle delimiters, encodings, headers, and data types.

The functions are forgiving with tidy files and highly configurable with messy ones. Learning the key parameters up front saves hours of cleanup, because it is far easier to parse a file correctly than to repair a badly parsed DataFrame afterwards.

2Reading CSV Files

read_csv is one of the most parameter-rich functions in Pandas, but the defaults handle a standard comma-separated file with a header row perfectly. You reach for parameters when the file deviates from that norm.

• pd.read_csv('data.csv') # standard file
• pd.read_csv('data.tsv', sep='\t') # tab-separated
• pd.read_csv('data.csv', header=None, names=['a', 'b', 'c']) # no header row
• pd.read_csv('data.csv', usecols=['id', 'price']) # only these columns
• pd.read_csv('data.csv', nrows=1000) # first 1000 rows for a quick look

3Handling Encodings and Delimiters

Two problems dominate real-world CSV loading: the wrong text encoding and the wrong delimiter. A UnicodeDecodeError almost always means the file is not UTF-8 — many exports from spreadsheets use latin-1 or cp1252. A file that loads into a single column usually has a delimiter other than a comma.

• pd.read_csv('data.csv', encoding='latin-1') # non-UTF-8 files
• pd.read_csv('data.csv', encoding='utf-8-sig') # strips a BOM from Excel exports
• pd.read_csv('data.csv', sep=';') # European-style semicolon separator
• pd.read_csv('data.csv', sep=None, engine='python') # let Pandas sniff the delimiter

4Setting Data Types and Parsing Dates

By default Pandas infers each column's type, which can be slow and occasionally wrong — a ZIP code or product code with leading zeros may be read as an integer and lose them. Specifying dtypes and date columns at read time is faster and safer than converting afterwards.

• pd.read_csv('data.csv', dtype={'zip': str, 'qty': 'int32'})
• pd.read_csv('data.csv', parse_dates=['order_date'])
• pd.read_csv('data.csv', parse_dates={'ts': ['date', 'time']}) # combine columns
• pd.read_csv('data.csv', na_values=['NA', 'null', '-', '']) # custom missing markers

5Reading Excel Files

read_excel loads .xlsx and .xls workbooks and shares many parameters with read_csv. Because a workbook can hold many sheets, sheet_name is the parameter you will use most. Modern .xlsx files require the openpyxl engine, which you install with pip.

• pd.read_excel('book.xlsx', sheet_name='Sales') # one sheet by name
• pd.read_excel('book.xlsx', sheet_name=0) # first sheet by position
• pd.read_excel('book.xlsx', sheet_name=['Sales', 'Costs']) # dict of frames
• pd.read_excel('book.xlsx', sheet_name=None) # every sheet as a dict
• pd.read_excel('book.xlsx', skiprows=3, usecols='B:F') # skip title rows

6Taming Messy Real-World Files

Exported files rarely start with a clean header on row one. Reports often include title rows, blank lines, footers, or thousands separators inside numbers. A few parameters clear most of this up during the read.

• skiprows=2 to jump over title banners before the real header.
• skipfooter=1 with engine='python' to drop a totals row at the bottom.
• thousands=',' so 1,200 parses as the number 1200 rather than text.
• na_values=['N/A', '--'] to treat placeholder text as missing.
• index_col='id' to promote a column straight to the DataFrame index.

7Best Practices

A little discipline at load time prevents a lot of downstream cleanup.

• Inspect the raw file first, then choose parameters — don't guess and repair.
• Set dtypes explicitly for ID-like columns to preserve leading zeros.
• Parse dates during the read, not with a separate conversion step.
• Load only the columns you need with usecols to save memory on wide files.
• For very large CSVs, read in chunks with chunksize and process each piece.

8Key Takeaways

Reading files well is mostly about the right parameters.

• read_csv and read_excel both return DataFrames and share many options.
• Fix encoding with encoding= and delimiter problems with sep=.
• Set dtype and parse_dates at read time for speed and correctness.
• read_excel needs openpyxl for .xlsx and uses sheet_name to pick sheets.
• usecols, nrows, na_values, and skiprows tame large or messy files.

9Frequently Asked Questions

Q: How do I fix a UnicodeDecodeError when reading a CSV? A: The file is not UTF-8. Try encoding='latin-1' or encoding='cp1252', which cover most spreadsheet exports, or encoding='utf-8-sig' if the file has a byte-order mark. If you are unsure, a library like charset-normalizer can detect the encoding for you.

Q: How do I read a specific sheet from an Excel file? A: Pass sheet_name with the sheet's name or its zero-based position, for example pd.read_excel('book.xlsx', sheet_name='Sales') or sheet_name=1. Use sheet_name=None to load every sheet at once as a dictionary of DataFrames keyed by sheet name.

Q: Why did Pandas read my whole CSV into one column? A: The delimiter is wrong. Your file probably uses semicolons, tabs, or pipes instead of commas. Set sep=';' (or the correct character), or use sep=None with engine='python' to let Pandas sniff it automatically.

Q: How do I read a very large CSV that does not fit in memory? A: Use the chunksize parameter to iterate over the file in blocks, for example for chunk in pd.read_csv('big.csv', chunksize=100000). You can also limit columns with usecols and set compact dtypes to shrink the footprint.