Import Excel CSV Files in Python: A Step-by-Step Guide

How-to basics · Updated November 21, 2026

To import a CSV file saved from Excel into Python, install pandas, then use import pandas as pd and df = pd.read_csv('sales.csv'). This loads the rows into a DataFrame so you can inspect, clean, and analyze them. A CSV is a plain-text table, not an Excel workbook, so it does not retain formatting, formulas, or multiple sheets.

What You’ll Need

  • Python installed on your computer, or access to a Python environment such as Jupyter Notebook, VS Code, or Google Colab.
  • The CSV file saved from Excel. If you still have the workbook, in Excel for Microsoft 365 on Windows choose File > Save As and select CSV UTF-8 (Comma delimited) (*.csv) when available. On Mac, use File > Save As and choose a CSV format. The precise choices can differ by Excel version.
  • Pandas for the main method below. Install it with python -m pip install pandas in a terminal. If you use a notebook, run %pip install pandas in a notebook cell, then restart the kernel if the import is not recognized.

Step-by-Step

  1. Confirm that the file is a CSV. Look for a filename ending in .csv, such as sales.csv. If the file ends in .xlsx or .xls, it is an Excel workbook rather than a CSV; use pandas’ read_excel method instead. A CSV normally represents one table in one sheet. Excel may warn that some workbook features are not compatible when you save as CSV; that is expected because CSV cannot preserve charts, cell formatting, formulas, or other sheets. If you need those features, keep the original workbook as well.
  2. Put the file somewhere Python can reach. For a beginner, keep the CSV in the same folder as your Python script or notebook. In a notebook, the current folder is often not the folder where the notebook file appears, so check it with from pathlib import Path and Path.cwd(). A clear folder structure, such as a project folder with a data subfolder, makes it easier to find the file later. Avoid relying on a vague filename if several copies exist, such as sales.csv and sales-final.csv.
  3. Import pandas and set the file path. In a script, use from pathlib import Path, then define the location with file_path = Path('data') / 'sales.csv'. This means Python looks in a folder named data beside the script’s working directory. If the CSV is beside the script, use file_path = Path('sales.csv'). For an absolute Windows path, use a raw string such as Path(r'C:\Users\Ava\Documents\sales.csv'); the r prevents backslashes from being treated as escape characters. On Mac or Linux, paths use forward slashes, for example Path('/Users/ava/Documents/sales.csv').
  4. Read the CSV into a DataFrame. Start with the defaults if the file is a standard comma-separated CSV. Use import pandas as pd, then df = pd.read_csv(file_path). Pandas reads the first row as column headings by default and assigns the result to df. If you prefer, the same basic import works with a filename string, as in df = pd.read_csv('sales.csv'). Run these lines in order in a script or notebook. If Python reports that pandas is missing, install it in the same environment that runs your code, not just in a different terminal or Python installation.
  5. Check that the data arrived correctly. Run print(df.head()) to view the first five rows and print(df.columns) to see the headings. Use print(df.shape) to get the number of rows and columns, and df.info() to review data types and missing values. Check a few values against the original Excel sheet, especially identifiers, dates, amounts, and columns with leading zeroes. If the headings or values look shifted, stop and correct the import settings before analyzing or saving results.
  6. Adjust settings only when the file needs them. If Excel or another program used semicolons between fields, specify df = pd.read_csv(file_path, sep=';'). For a UTF-8 CSV that includes a byte-order mark, try df = pd.read_csv(file_path, encoding='utf-8-sig'). If an ID such as 00127 must keep its leading zeroes, tell pandas to treat that column as text: df = pd.read_csv(file_path, dtype={'Customer ID': 'string'}). Replace the example heading with the exact heading in your file. Add one setting at a time and inspect the result so you know which change fixed the issue.
  7. Use the built-in csv module for simple row-by-row work. Pandas is convenient for analysis, but Python also includes a csv module and needs no extra installation for it. For example, use import csv, open the file with with open('sales.csv', newline='', encoding='utf-8-sig') as f:, then create a reader with reader = csv.DictReader(f) and loop with for row in reader:. Each row is a dictionary keyed by the CSV headings. Use row['Customer'] to access a field, changing the heading to match your file. This approach is useful for processing records one at a time, but it does not automatically provide pandas’ table operations or infer numeric data types.

Common Mistakes

A common mix-up is passing an Excel workbook to read_csv. It expects delimited text, so a file ending in .xlsx needs pd.read_excel('sales.xlsx') instead; reading Excel workbooks may also require the openpyxl package. Renaming an .xlsx file to .csv does not convert it.

Another frequent issue is assuming Excel’s display is exactly what the CSV contains. CSV stores text values separated by a delimiter, not the cell’s number format. Excel might display a long number with commas or a date in a friendly format, while the saved text may look different. It can also remove leading zeroes from values it interprets as numbers. Check important columns in the CSV or import them as text when those characters matter.

Do not assume every file uses commas, UTF-8, or a header row. Regional settings and the program that created a file can change the delimiter or encoding. Also, avoid silently ignoring malformed rows just to make an import run: skipped records can make totals incomplete. First identify why rows do not match the expected structure, then choose an intentional fix.

Tips

  • Use Path from Python’s standard pathlib module to build file paths. It handles separators across Windows, Mac, and Linux more safely than manually joining path fragments.
  • Preview a large file before loading all of it with pd.read_csv(file_path, nrows=10). This reads only the first ten data rows, which helps check headings and delimiters quickly.
  • Keep identifiers such as postal codes, account numbers, and product codes as text if leading zeroes or exact characters matter. A value can look numeric without being a quantity you should calculate.
  • Use clear variable names, such as orders or customers, instead of df when your script handles several tables. Keep the original CSV unchanged and write cleaned data to a separate file if you need to preserve a source copy.
  • When importing dates, inspect the result before doing date calculations. Date formats can vary, and Excel’s display format does not guarantee how the date is represented in the CSV. Pandas can parse dates with options such as parse_dates=['Order Date'], provided that the heading and values are suitable.

Troubleshooting

FileNotFoundError: Python cannot find the path you supplied. Print Path.cwd() to see the current working directory, check spelling and capitalization, and confirm the file really has a .csv extension. Then move the file to the expected folder or provide the correct full path. In notebooks, the working directory may differ from the notebook’s visible location.

UnicodeDecodeError: The file’s text encoding does not match the one pandas tried. If the CSV came from Excel, try encoding='utf-8-sig' in read_csv. If that fails, identify the encoding used when the file was created or save a fresh UTF-8 CSV from Excel. Do not choose a random encoding without checking the output; incorrect decoding can turn names and symbols into unreadable characters.

One very wide column or strange column names: The separator may be wrong. Open the CSV in a plain-text editor to see whether fields are separated by commas, semicolons, or tabs, then set sep=',', sep=';', or sep='\t' as appropriate. If the first row is data rather than headings, use header=None; if headings begin on a later line, check pandas’ skiprows option and verify the result.

ParserError or rows with shifted values: Inspect the reported line and compare it with neighboring lines. A field containing a delimiter may need to be quoted correctly, or a row may have an extra separator. Re-exporting the file from Excel can fix damaged CSV structure. Avoid using an option that skips bad lines unless you have reviewed which records will be lost and confirmed that skipping them is acceptable.

Numbers or dates look wrong: Check whether the file uses a decimal comma, a different date convention, or text values mixed into a numeric column. Pandas may treat mixed values as text, and Excel may have changed identifiers when the CSV was created. Inspect the original text and choose explicit parsing or data types only after confirming the intended meaning.

Frequently Asked Questions

Can Python read a CSV file saved by Excel?
Yes. A CSV saved from Excel is plain-text data that pandas can read with pd.read_csv(). The file contains one table, not the full workbook, and does not preserve formatting or formulas.

Do I need Excel installed to import a CSV in Python?
No. Python reads the CSV as a text file, so Excel does not need to be installed. You only need access to the CSV itself and either pandas or Python’s built-in csv module.

Should I use pandas or the csv module?
Use pandas when you want a table you can filter, summarize, or clean with DataFrame tools. Use csv when you want a lightweight built-in option and plan to process one row at a time. Both can read CSV files; choose based on what you will do with the data.

How do I import a CSV without losing leading zeroes?
Specify the relevant column as text when loading it, for example pd.read_csv(file_path, dtype={'Postal Code': 'string'}). Use the exact column heading. If Excel already removed the zeroes before saving the CSV, Python cannot infer the missing digits; correct the source data first.

Why does the imported file show everything in one column?
The separator in the file may not be a comma. Check the raw file and set the sep option to the actual delimiter, such as a semicolon or tab. Regional settings can cause Excel or another program to save a different delimiter than expected.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.