To convert an Excel workbook to CSV with pandas, read a worksheet into a DataFrame with pd.read_excel(), then write it with DataFrame.to_csv(). For example, df = pd.read_excel('workbook.xlsx', sheet_name='Sales'); df.to_csv('sales.csv', index=False) exports the Sales sheet without adding pandas’ row index as a column.
Basic Excel-to-CSV conversion
A minimal script needs pandas and the workbook’s path. For a typical .xlsx file, the code is:
import pandas as pd
df = pd.read_excel('workbook.xlsx', sheet_name='Sales')
df.to_csv('sales.csv', index=False)
Run the script from a folder where the workbook can be found, or provide full paths. The first line imports pandas. The next reads the named worksheet into a DataFrame, pandas’ table-like data structure. The last line writes that table as a comma-separated text file.
If you omit sheet_name, pandas reads the first worksheet by default. If you omit index=False, the DataFrame’s index is written as an extra leading column. That index is often just a sequence such as 0, 1, 2, so excluding it is usually right when the goal is a clean data export. Keep it only if it represents meaningful data you need in the CSV.
Install pandas and the Excel reader
Pandas does not handle every Excel file format with the same built-in reader. For .xlsx workbooks, install pandas and openpyxl in the Python environment that will run the script:
python -m pip install pandas openpyxl
In a notebook, installation may use a different command depending on how the notebook environment is configured. The key point is that installing a package in one Python environment does not necessarily install it in another. If Python reports that it cannot find an Excel-reading engine, install the required package in the environment shown by the error.
Modern .xlsx workbooks are commonly read with openpyxl. Older .xls files and other formats may require a different supported engine. Check the file extension and pandas’ current documentation if you are working with .xls, .xlsb, or an unusual workbook type; renaming a file extension does not convert its format. A password-protected or damaged workbook may also need to be opened or repaired in a spreadsheet application before pandas can read it.
Choose a file, worksheet, and columns
The read_excel function accepts a path and options that determine what pandas imports. A worksheet can be selected by its visible tab name or by a zero-based position: position 0 means the first worksheet, position 1 the second. For instance:
df = pd.read_excel('workbook.xlsx', sheet_name=0)
To read only certain columns, use usecols. This can make an export smaller and avoid including notes or unrelated calculations elsewhere in the workbook:
df = pd.read_excel('workbook.xlsx', sheet_name='Sales', usecols='A:D')
By default, pandas treats the first row as column headers. If the workbook has a title or explanatory rows above the actual table, use skiprows to skip them. If the table has no header row, set header=None; pandas will assign numeric column labels unless you provide names. Choose these settings based on the actual layout, because an incorrect header choice can turn data into column names or vice versa.
For repeatable scripts, use a clear, explicit input and output path. The current working directory is not always the directory containing the script, especially when running code from an editor, notebook, or scheduled task.
Control the CSV export
to_csv has options for the parts of a CSV that can vary between systems and applications. A practical export often specifies the encoding as well as whether to include the index:
df.to_csv('sales.csv', index=False, encoding='utf-8-sig')
UTF-8 is a widely used text encoding. The utf-8-sig option writes a UTF-8 byte-order mark, which can help some versions or configurations of Excel recognize UTF-8 text when opening a CSV directly. It is not required for every workflow: when another system specifically expects plain UTF-8, use encoding='utf-8' instead. If accents or other non-English characters look corrupted, check the encoding used when writing and importing the file.
sep=';'changes the delimiter from a comma to a semicolon, which can suit systems that expect semicolon-delimited files.columns=['Date', 'Region', 'Sales']writes only the named DataFrame columns, in that order.na_rep=''writes missing values as empty fields; a different marker can be supplied if the receiving system requires one.decimal=','changes the decimal character used when formatting numeric values. Coordinate this with the delimiter so the output can be parsed unambiguously.
These options change the text representation, not the underlying Excel workbook. Before sharing an export with another application, confirm its expected delimiter, encoding, decimal convention, and representation for missing values.
Export every worksheet separately
A CSV file holds one flat table, not a collection of named worksheets. To export a workbook with multiple tabs, create one CSV per worksheet. ExcelFile lets you inspect the worksheet names and read them from the same workbook:
from pathlib import Path
import pandas as pd
source = Path('workbook.xlsx')
output_dir = Path('csv_exports')
output_dir.mkdir(exist_ok=True)
with pd.ExcelFile(source) as workbook:
for number, sheet in enumerate(workbook.sheet_names, start=1):
df = pd.read_excel(workbook, sheet_name=sheet)
df.to_csv(output_dir / f'sheet_{number}.csv', index=False, encoding='utf-8-sig')
This example writes files such as sheet_1.csv and sheet_2.csv into a new folder, so worksheet names containing characters unsuitable for filenames do not become part of the output path. The numbering follows the order of tabs in the workbook. If retaining the worksheet name is important, create a filename-safe version of each name and check for duplicates before writing; two different names can become the same filename after cleanup.
Not every worksheet is necessarily a data table. A workbook may contain a cover sheet, charts, or notes. Review the sheet names and decide whether all tabs should be exported before running a batch conversion.
What CSV keeps—and what it loses
CSV is plain text organized into rows and fields. It does not preserve Excel features such as multiple worksheets, cell formatting, formulas as editable formulas, charts, merged cells, column widths, comments, or data validation. It is useful for transferring tabular values between programs, but it is not a substitute for an .xlsx workbook when those features matter.
Pandas reads cell values into columns with inferred data types, then writes a textual representation of those values. As a result, a CSV does not retain Excel’s number formats. A date may be written as a date string, and a value displayed in Excel as currency may be written as a number without its currency symbol. Decide how the receiving system should interpret these values rather than expecting the CSV to preserve their visual appearance.
Leading zeros are a frequent source of confusion. If a cell contains the text 001234, pandas can preserve it as text. If the cell contains the number 1234 and Excel merely displays it with a custom format such as six digits, pandas generally reads the numeric value, not the display format; the resulting CSV may contain 1234. For identifier columns that are genuinely stored as text, you can request a string type when reading:
df = pd.read_excel('workbook.xlsx', dtype={'PostalCode': 'string'})
That option cannot reconstruct zeros that exist only in Excel formatting. If exact identifiers matter, store them as text in the source workbook or deliberately format the values during data preparation.
Formula cells need attention too. Pandas does not calculate Excel formulas. Depending on what the workbook has saved, the reader may see a cached result or a missing value; it will not refresh a formula as Excel recalculates the workbook. If the export must contain current formula results, recalculate and save the workbook in a spreadsheet application before reading it, then verify a few representative values.
Common errors and practical fixes
- File not found: Check the spelling, extension, and working directory. Print or inspect the full path, and use an absolute path if the script may run from different folders.
- Missing optional dependency: Install the reader needed for the file type, such as openpyxl for common .xlsx files, in the active Python environment.
- Worksheet not found: Confirm the exact tab name, including spaces. Inspect
pd.ExcelFile('workbook.xlsx').sheet_namesto see the names pandas detects. - Wrong columns or headers: Check whether the worksheet has title rows, blank rows, or a header somewhere other than the first row. Adjust
skiprows,header, orusecolsto match its layout. - CSV opens in one column: The receiving application may expect a different delimiter, or it may use regional import settings. Import the file and specify its delimiter rather than assuming every program uses commas.
- Unexpected extra first column: If that column contains row numbers, export again with
index=False.
For a large workbook, reading every sheet or every column can consume substantial memory. Select only the needed sheet and columns where possible. If a worksheet is too large for available memory, consider processing the data in a suitable database or a chunk-oriented format rather than assuming that a single full-workbook read will be lightweight.
CSV safety and how Excel opens the result
A CSV is plain text, but spreadsheet programs may interpret some text fields as formulas when a person opens the file. Values beginning with characters such as = can be treated as formulas by spreadsheet software. This is a concern when exporting untrusted user-supplied content for people to open in Excel, sometimes called CSV formula injection. Pandas does not automatically make every such value safe for every spreadsheet application.
For a data exchange intended for another program, preserve the original values and follow that program’s import rules. For a file intended to be opened by people in a spreadsheet, assess untrusted fields and apply an agreed sanitization approach before export; prefixing a value can change the data, so it should not be done blindly. Keep an unmodified source when exact data retention matters.
Opening a CSV in Excel is also different from importing a native workbook. Excel may infer dates, numbers, and leading zeros from text, and those guesses can affect what is displayed or later saved. For more control in Microsoft Excel for Microsoft 365 on Windows, use Data > From Text/CSV, inspect the delimiter and preview, and set column types where needed before loading. On Mac, the import experience can differ by Excel build, but importing through its text/CSV import controls similarly gives more control than simply double-clicking the file.
When pandas is the right choice
Pandas is a good fit when conversion is part of a repeatable Python workflow: for example, exporting selected columns, renaming headers, cleaning values, or converting many workbooks on a schedule. The code can make the same choices each time and can be expanded to validate row counts or report missing sheets. It also makes sense when the data is already being analyzed in Python.
For a one-time conversion with no transformation, Excel’s Save As or export options may be quicker and avoid setting up Python. In Excel for Microsoft 365 on Windows, open the workbook, select the worksheet to export, and choose File > Save As or File > Save a Copy, then select a CSV format. Excel saves the active sheet, not all workbook sheets into one CSV. Menu labels and available CSV encoding choices can vary by platform and build. Whichever tool you use, keep the original .xlsx file if you may need formatting, formulas, or other sheets later.
Frequently Asked Questions
Can pandas convert an Excel file to CSV in one line?
Yes. For a workbook named input.xlsx whose first worksheet should be exported, use pd.read_excel('input.xlsx').to_csv('output.csv', index=False) after importing pandas and installing an appropriate Excel reader. Add a sheet_name argument when you need a specific worksheet.
How do I convert all sheets in an Excel workbook to CSV?
Write a loop over pd.ExcelFile(path).sheet_names, read each worksheet by name, and save each DataFrame to its own CSV. A single CSV cannot contain multiple worksheet tabs, so choose a clear filename for each export.
Why does my CSV have an extra column?
Pandas writes the DataFrame index by default. Use index=False in to_csv to omit it, unless the index contains information you intentionally want in the file.
Can pandas preserve Excel formatting or formulas in CSV?
No. CSV stores text fields, not workbook formatting or editable formulas. Pandas does not calculate formulas; make sure the workbook has current saved results if those values are needed in the export.
How can I keep leading zeros in a CSV?
Store identifiers as text in Excel and read the relevant column as a string where appropriate. If zeros are only a number format applied to a numeric cell, that visual formatting is not the underlying value, so pandas cannot infer the intended identifier width automatically.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Conditional Formatting on All SheetsExcel does not have a single built-in command that applies one conditional-formatting rule to every worksheet at once.…
- Excel Power Query Merge vs Append: Which Should You Use?In Excel Power Query, Merge joins two queries side by side using one or more matching columns; Append…
- Set Out of Office in Outlook Mail: A Step-by-Step GuideTo set an out-of-office reply in Outlook, open Automatic Replies, turn on automatic replies, write your message, and—if…
- Clear Excel Cell Formatting: What Changes and What StaysTo clear formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Clear…
- Excel Table Styles: How to Apply and Customize ThemAn Excel table style is a coordinated set of formatting applied to an Excel table, such as a…
- How to Set an Out of Office Reply in OutlookTo set an out-of-office reply in Outlook, open Automatic Replies, turn them on, write your message, and—if available—choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.