Python XLS to CSV: A Practical Guide for Data Analysts
Learn to convert Excel files (.xls/.xlsx) to CSV with Python using pandas. This practical guide covers single and multi-sheet conversions, data types, dates, and best practices for reliable CSV output.
To convert an Excel file (XLS/XLSX) to CSV in Python, load the workbook with pandas read_excel and then export with to_csv. For a single sheet: df = pd.read_excel('data.xlsx', sheet_name='Sheet1'); df.to_csv('data.csv', index=False). For multiple sheets, loop over sheet names and save separate CSVs. This approach is reliable and widely used.
Overview: Why Python is a natural choice for XLS to CSV transformations
Python, with libraries like pandas, provides a reliable and readable path to convert Excel files (XLS/XLSX) to CSV. This approach is especially popular among data analysts and developers who want repeatable, auditable pipelines. According to MyDataTables, Python-based CSV workflows are a common, robust choice for data transformation tasks.
import pandas as pd
# Read the first sheet by default and export to CSV
df = pd.read_excel("data.xlsx", sheet_name="Sheet1") # reads first sheet
df.to_csv("data.csv", index=False) # export to CSV without an index column# Read all sheets and export each to its own CSV file
xls = pd.ExcelFile("data.xlsx")
for sheet in xls.sheet_names:
df = pd.read_excel(xls, sheet_name=sheet)
df.to_csv(f"{sheet}.csv", index=False)Notes:
- Use header=0 to ensure the first row is treated as headers unless your file uses a different row.
- For xlsx files, openpyxl is the recommended engine; for xls, you may rely on xlrd (legacy support) if necessary.
languageDefinedCodeBlocksInContentOnlyOnThisSectionOfAnswerOnlyShouldBeCodeBlocksWithinBlockContentAllowedToUseMarkdownCodeFencesWithLanguageIdentifiersIfPresentInThisBlock
Steps
Estimated time: 15-30 minutes
- 1
Install prerequisites
Ensure Python 3.8+ is installed and that pip is available. Install pandas and OpenPyXL to enable Excel reading and CSV exporting. If you need legacy Xls support, also install xlrd. Verify installations with python -V and pip --version.
Tip: Use a virtual environment to isolate dependencies. - 2
Prepare your Excel file
Place the Excel file in your working directory. Ensure there is a clear header row in the sheet(s) you will export, and decide whether you want a single output or multiple CSVs.
Tip: Rename sheets to friendly names if you plan to export multiple CSVs. - 3
Write the conversion script
Create a Python script or run the provided one-liners. Start with a simple single-sheet conversion, then expand to handle multiple sheets or restricted columns as needed.
Tip: Comment each step to keep the pipeline maintainable. - 4
Run the script and verify output
Execute the script and inspect the produced CSVs. Reload the CSVs to confirm headers, data types, and values look correct.
Tip: Check a few random rows to ensure numeric and date columns are preserved. - 5
Handle sheets and columns variations
Adapt the script to export multiple sheets or to limit columns using usecols. Consider parsing dates for consistent ISO formatting.
Tip: Sanitize sheet names before using them as file names. - 6
Troubleshooting and validation
If something goes wrong, check file paths, confirm you installed the right engines, and review error messages. Add try/except blocks to surface useful errors during production runs.
Tip: Enable logging for long-running conversions.
Prerequisites
Required
- Required
- pipRequired
- Required
- Required
- Basic command line knowledgeRequired
Optional
- Optional
- Code editor or IDE (optional)Optional
Commands
| Action | Command |
|---|---|
| Install required Python packagesxlrd used for legacy .xls support; omit if not needed. | python -m pip install pandas openpyxl xlrd |
| Run conversion for a single sheetConverts Sheet1 to CSV. | python -c "import pandas as pd; df = pd.read_excel('data.xlsx', sheet_name='Sheet1'); df.to_csv('data.csv', index=False)" |
| Convert all sheets to separate CSVsExports every sheet as its own CSV file. | python -c "import pandas as pd; xls = pd.ExcelFile('data.xlsx');\nfor s in xls.sheet_names:\n df = pd.read_excel(xls, sheet_name=s)\n df.to_csv(f'{s}.csv', index=False)" |
People Also Ask
Do I need Excel installed to convert XLS to CSV with Python?
No. Python libraries read the Excel file directly from disk and do not require Excel to be installed. You just need the appropriate Python packages (pandas and a suitable engine like openpyxl or xlrd).
No—Python reads the file itself without needing Excel installed.
Can I convert multiple sheets at once?
Yes. Use an ExcelFile object to list sheets and loop through them, exporting each sheet to its own CSV file. You can also concatenate sheets with an extra column identifying the origin if needed.
Yes, you can export every sheet in one workbook by looping through the sheets.
How do I handle date columns when exporting to CSV?
Parse dates with parse_dates when reading, then export. If needed, convert to ISO strings or pandas datetime objects before to_csv to ensure consistent serialization.
Parse dates on read and normalize to ISO format before saving.
What about very large Excel files?
Large workbooks may require processing one sheet at a time and limiting memory usage with usecols. Pandas reads the data into memory, so per-sheet processing is safer for big files.
Process one sheet at a time to manage memory when files are large.
How can I preserve headers and avoid an extra index in CSV?
Make sure to use header=0 if needed and always pass index=False to to_csv to avoid writing an extra index column.
Keep headers by setting header correctly and drop the index with index=False.
Main Points
- Install pandas and OpenPyXL for Excel support
- Read Excel sheets with read_excel and specify sheet_name
- Export to CSV with to_csv, using index=False
- Validate results by re-reading the CSV and checking headers