Convert Xlsx To Csv In Python

Convert XLSX to CSV in Python: A Complete Guide

Converting XLSX (Excel) files to CSV (Comma‑Separated Values) is a routine task for data analysts, engineers, and anyone who works with spreadsheet data in Python. Whether you need to export large datasets for import into other tools, prepare data for web applications, or simply archive information in a lightweight format, mastering the convert xlsx to csv in python process will save time and ensure consistency across projects. This article walks you through several reliable methods, explains the underlying mechanics, and offers tips to avoid common pitfalls.

Steps to Convert XLSX to CSV in Python

Using pandas (the most popular approach)

pandas provides a high‑level, intuitive API that reads Excel files and writes them directly to CSV. It handles data types, missing values, and formatting automatically, making it ideal for most use cases.

1. Install pandas and openpyxl (required for reading .xlsx files).

   pip install pandas openpyxl
   

2. Import the libraries in your script.

   import pandas as pd
   

3. Load the Excel workbook Easy to understand, harder to ignore. Worth knowing..

   # Replace 'input.xlsx' with your file path
   df = pd.read_excel('input.xlsx')
   

4. Export the DataFrame to CSV No workaround needed..

   df.to_csv('output.csv', index=False)
   

5. Verify the conversion by reading the CSV back That's the part that actually makes a difference..

   df_check = pd.read_csv('output.csv')
   print(df_check.head())
   

Why pandas works well:

• Handles multiple sheets via sheet_name parameter.
• Preserves column headers and data types.
• Supports encoding options (e.g., encoding='utf-8-sig' for Windows compatibility).

Using openpyxl + csv modules (low‑level control)

When you need fine‑grained control over the conversion process—such as skipping certain rows, applying custom transformations, or working with very large files—you can combine openpyxl (to read .xlsx) with Python’s built‑in csv module.

1. Install openpyxl if not already present.

   pip install openpyxl
   

2. Write a conversion script Still holds up..

   import csv
   from openpyxl import load_workbook
   
   # Load the workbook
   wb = load_workbook('input.xlsx')
   ws = wb.active
   
   # Open CSV file for writing
   with open('output.csv', 'w', newline='', encoding='utf-8') as csvfile:
       csv_writer = csv.writer(csvfile)
   
       # Write header (first row)
       for row in ws.iter_rows(min_row=1, max_row=1, values_only=True):
           csv_writer.writerow(row)
   
       # Write data rows
       for row in ws.iter_rows(min_row=2, values_only=True):
           csv_writer.writerow(row)
   

3. Close the workbook (optional, as it will be closed automatically when the script ends) Worth keeping that in mind..

When to prefer this method:

• You need to skip hidden rows or ignore specific columns.
• You want to apply custom formatting before writing.
• Memory constraints make it necessary to stream data row‑by‑row.

Why Choose Python for File Conversion

Python’s ecosystem offers several advantages for convert xlsx to csv in python tasks:

• Cross‑platform compatibility – scripts run on Windows, macOS, and Linux without modification.
• Rich library support – pandas, openpyxl, xlrd, and csv provide flexible options.
• Automation – you can integrate conversion into larger data pipelines, scheduled jobs, or web services.
• Performance – for moderate‑sized files, pandas is fast enough; for huge datasets, streaming with openpyxl avoids loading everything into memory.

Best Practices and Tips

• Preserve encoding: Use encoding='utf-8-sig' when writing CSV on Windows to ensure Excel opens the file correctly.
• Handle missing values: Decide whether to keep empty cells as blanks or replace them with a placeholder (e.g., 'N/A').
• Validate data types: After conversion, check that numeric columns remain numeric; pandas may infer types differently.
• Use context managers: Always open files with with statements to guarantee proper closure, even if errors occur.
• Test with a small sample: Run the conversion on a few rows first to confirm the output matches expectations before processing large files.
• Document the process: Add comments in your script and keep a README describing input and output formats, especially if the script will be shared with teammates.

Common Issues and How to Resolve Them

Scientific Explanation

At a low level, an .That said, xlsx file is a ZIP archive containing XML files that describe worksheets, styles, and shared strings. So when you call pd. read_excel('input.xlsx'), pandas uses openpyxl to parse these XML files, reconstruct the cell grid, and map values to Python objects (e.In practice, g. , int, float, str). The resulting DataFrame is an in‑memory representation of the spreadsheet.

The official docs gloss over this. That's a mistake.

Exporting to CSV involves iterating over the DataFrame’s rows and writing them as comma‑separated strings. CSV is a plain‑text format with no formatting information, so any styling (fonts, colors, formulas) is lost. The conversion therefore is a data transformation from a structured binary‑like format (XLSX) to a simple text delimiter format (CSV), preserving only the raw cell values and column headers That's the part that actually makes a difference..

FAQ

Q: Do I need both pandas and openpyxl installed?
A: Yes, if you use pd.read_excel with .xlsx files. openpyxl is the engine that reads the newer Excel format

Advanced Tips for dependable XLSX → CSV Conversion

1. Preserve Leading Zeros and Custom Formats
   When a column contains codes like 00123 or dates stored as text, pandas may strip the leading zeros or reinterpret the value. To keep the exact string representation, read the column as object (or str) using the dtype argument:

   df = pd.read_excel('input.xlsx', dtype={'ID': str, 'PostalCode': str})
   

   If you need to apply a custom Excel number format after conversion, you can store the format string in a separate metadata file and re‑apply it downstream.

2. Handle Formulas vs. Calculated Values
   read_excel returns the last calculated value stored in the workbook, not the underlying formula. If you need the formula itself (e.g., for auditing), use openpyxl directly:

   from openpyxl import load_workbook
   wb = load_workbook('input.xlsx', data_only=False)  # keep formulas
   ws = wb['Sheet1']
   formulas = [[cell.value for cell in row] for row in ws.iter_rows()]
   

   You can then write two CSV files—one with values, one with formulas—or embed formulas as comments.

3. Streaming Large Workbooks
   For files that exceed available RAM, avoid loading the whole sheet into a DataFrame. pandas.read_excel supports the chunksize parameter when combined with the openpyxl engine in read‑only mode:

   for chunk in pd.read_excel('huge.xlsx', sheet_name='Data', chunksize=50000):
       chunk.to_csv('output_part_{}.csv'.format(i), index=False, mode='a', header=(i==0))
       i += 1
   

   Alternatively, the openpyxl read_only workbook lets you iterate row‑by‑row and write directly to CSV with the built‑in csv module, eliminating the intermediate DataFrame altogether.

4. Dealing with Multiple Sheets
   When a workbook contains several related tables, you can either:

   • Export each sheet to its own CSV using a loop over wb.sheetnames.
   • Concatenate sheets into a single CSV if they share the same schema, adding a source‑sheet column for traceability:

   frames = []
   for sh in pd.ExcelFile('input.xlsx').sheet_names:
       df = pd.read_excel('input.xlsx', sheet_name=sh)
       df['_source_sheet'] = sh
       frames.append(df)
   pd.concat(frames, ignore_index=True).to_csv('combined.csv', index=False)
   

5. Date and Timezone Handling
   Excel stores dates as floating‑point numbers representing days since 1899‑12‑30 (or 1904‑01‑01 for the 1904 system). Pandas usually converts them to datetime64[ns], but if the workbook uses the 1904 calendar you must specify it:

   df = pd.read_excel('input.xlsx', date_converter=pd.to_datetime)  # default works for 1900 system
   # For 1904 system:
   df = pd.read_excel('input.xlsx', engine='openpyxl', date_converter=lambda x: pd.to_datetime(x, origin='1899-12-30') + pd.Timedelta(days=1462))
   

   After conversion, you can enforce a specific output format in CSV via df['Date'].dt.strftime('%Y-%m-%d').

6. Memory‑Efficient Data Types
   After loading, downcast numeric columns to the smallest sufficient dtype to reduce RAM usage before writing:

   df = df.apply(pd.to_numeric, downcast='integer')  # for int columns
   df = df.apply(pd.to_numeric, downcast='float')   # for float columns
   

   This step is especially useful when the resulting CSV will be consumed by memory‑constrained environments (e.g., embedded systems).

7. Validating Output Integrity
   A quick sanity check after conversion can catch silent data loss:

   original_rows = df.shape[0]
   csv
   
   

8. Verifying the Written CSV

After the loop that writes each chunk, a lightweight verification step can confirm that no rows were inadvertently dropped or duplicated:

import pandas as pd
import os

# Re‑read the concatenated CSV (or each part) and compare row counts
csv_path = 'output_part_0.csv'   # assuming the first chunk contains the header
if os.path.exists(csv_path):
    df_check = pd.read_csv(csv_path, low_memory=False)
    written_rows = df_check.shape[0]

    # If you stored per‑chunk files, sum their sizes:
    total_written = sum(os.path.getsize(f) for f in glob.glob('output_part_*.csv'))
    print(f'Rows in CSV : {written_rows}')
    print(f'Cumulative file size (bytes) : {total_written}')
else:
    print('No CSV was generated – check the write loop.

A more rigorous check is to compare a hash of the original Excel data (if it fits in memory) with the hash of the generated CSV:

```python
import hashlib

def _hash_df(df: pd.DataFrame) -> str:
    # Use a stable representation: sorted columns, deterministic dtypes
    return hashlib.sha256(pd.Still, util. Worth adding: hash_pandas_object(df, categorize=False). values).

# Load the whole Excel (only feasible for moderate files) and hash it
df_original = pd.read_excel('huge.xlsx', sheet_name='Data')
original_hash = _hash_df(df_original)

# Hash the concatenated CSV
df_csv = pd.read_csv('combined.csv')
csv_hash = _hash_df(df_csv)

print(f'Original hash : {original_hash}')
print(f'CSV hash      : {csv_hash}')
print('Integrity check passed!' if original_hash == csv_hash else 'Integrity check FAILED!')

These checks are especially handy when the conversion is part of an automated pipeline; they surface silent data loss before the next stage runs.

9. Fine‑Tuning Write Performance

Even with chunksize, the default CSV writer can be a bottleneck. Two practical tweaks often shave seconds to minutes off large jobs:

1. Batch the to_csv calls – instead of opening and closing the file for every chunk, accumulate a few chunks in memory and write them together:

buffer = []
for chunk in pd.read_excel('huge.xlsx', sheet_name='Data', chunksize=50000):
    buffer.append(chunk)
    if len(buffer) == 5:                     # write every 5 chunks (250k rows)
        pd.concat(buffer, ignore_index=True) \
         .to_csv('output_part_{}.csv'.format(i),
                 mode='a', header=(i==0), index=False)
        buffer.clear()
        i += 1
if buffer:                                   # flush remaining chunks
    pd.concat(buffer, ignore_index=True) \
     .to_csv('output_part_{}.csv'.format(i),
             mode='a', header=(i==0), index=False)

2. Use the utf-8-sig encoding – it adds a BOM that many Excel‑on‑Windows users expect, and it also speeds up writes on some platforms:

pd.concat(buffer, ignore_index=True) \
 .to_csv('combined.csv', index=False, encoding='utf-8-sig')

Both

10. Scaling Beyond Pandas – Out‑of‑Core Solutions

When the Excel file truly exceeds available RAM, pandas’ chunksize trick becomes a limiting factor because each chunk still lives entirely in memory while it is being processed. Two popular families of libraries let you keep the data on disk (or even on a distributed cluster) while still benefiting from a pandas‑like API:

No fluff here — just what actually works.

Below is a compact Dask‑based converter that mirrors the chunked pandas pattern but keeps the data out of RAM:

import dask.dataframe as dd
import glob, os

# 1️⃣  Read the Excel in logical chunks (Dask will not load everything at once)
chunksize = 50_000
ddf = dd.read_excel('huge.xlsx', sheet_name='Data', chunksize=chunksize)

# 2️⃣  Write each partition to its own CSV file – Dask’s .to_csv respects the partition size
output_files = []
for i, chunk in enumerate(ddf):
    path = f'output_part_{i:04d}.csv'
    chunk.to_csv(path, index=False, encoding='utf-8-sig')
    output_files.append(path)

# 3️⃣  Concatenate the part files (optional) – Dask can do this lazily
combined = dd.concat([dd.read_csv(p) for p in output_files])
combined.to_csv('combined.csv', index=False, encoding='utf-8-sig')
print(f'Wrote {len(output_files)} part files and a combined CSV.')

Why Dask shines here

• Memory‑aware – each partition is materialised only when it

The snippet above shows how Dask turns a monolithic Excel read into a series of independent, disk‑resident partitions. Even so, because each partition is only materialised when the to_csv call is executed, the peak RAM usage stays roughly proportional to the chunksize you choose rather than the total file size. This property makes Dask especially attractive for environments where RAM is limited but fast local storage (SSD or NVMe) is available.

Tuning the chunk size

• Start modest – 25 000–50 000 rows per partition is a good baseline for a typical 8 GB RAM laptop.
• Monitor – use ddf.npartitions and ddf.map_partitions(len).compute() to verify that each partition stays comfortably below your memory ceiling.
• Scale up – on a machine with 32 GB+ RAM you can safely push chunksize to 200 000–500 000 rows, reducing the number of output files and the overhead of concatenation.

Handling heterogeneous sheets

If your workbook contains multiple sheets with different schemas, you can build a dictionary of Dask DataFrames and process each independently:

sheets = ['Data', 'Metadata', 'Logs']
dfs = {s: dd.read_excel('huge.xlsx', sheet_name=s, chunksize=chunksize) for s in sheets}
for name, ddf in dfs.items():
    ddf.to_csv(f'{name}_part_.csv', index=False, encoding='utf-8-sig')

Error resilience

Excel files occasionally contain malformed cells (e.g., dates stored as strings). Dask propagates pandas‑level parsing errors per partition, so a single bad row won’t abort the whole job. To capture problematic rows:

def safe_read(part):
    try:
        return pd.read_excel(part, sheet_name='Data')
    except Exception as e:
        # log and return an empty frame with the expected columns
        return pd.DataFrame(columns=expected_cols)

ddf = dd.from_delayed([dd.delayed(safe_read)(f'huge.xlsx#sheet={i}') for i in range(n_sheets)])

When to prefer the other libraries

Practical tips for production

1. Compress on the fly – add compression='gzip' (or 'bz2', 'xz') to to_csv to shrink intermediate files without extra I/O steps.
2. use object storage – point output_files to an S3‑compatible endpoint (s3://bucket/prefix/) and use s3fs or fsspec so that the part files never touch local disk.
3. Avoid unnecessary concatenation – if downstream consumers can read partitioned CSVs directly (e.g., Spark, Dask, or Presto), skip the combined.csv step altogether.
4. Profile – use dask.diagnostics.Profiler or modin.utils.get_ray_logs to spot straggler partitions; adjusting chunksize or enabling dask.config.set(scheduler='threads') can balance CPU vs. I/O bound workloads.

Conclusion

Dask’s chunk‑aware, lazy execution model lets you treat a massive Excel workbook as if it were a regular pandas DataFrame while keeping RAM usage predictable and modest. By selecting an appropriate chunksize, writing each partition to its own CSV (optionally compressed), and only re‑assembling when truly needed, you can convert gigabyte‑scale spreadsheets into CSV format on a modest workstation or a small cluster. When your analytical needs shift toward heavy columnar queries, drop‑in pandas replacements like Modin, or memory‑mapped speed‑ups like Vaex, or when you require the fault‑tolerant scalability of a full Spark cluster, the ecosystem offers complementary tools that slot in without friction with the same Excel‑to‑CSV workflow. Choose the library that matches your hardware profile and processing pattern, and you’ll keep both your memory bill and your runtime under control