How to Convert XLSX to CSV File in Python

1. the XLSX and CSV File Formats
2. Use the Pandas Library to Convert XLSX to CSV File in Python
3. Use the xlrd and csv Modules to Convert XLSX to CSV File in Python
4. Use the openpyxl and csv Modules to Convert XLSX to CSV File in Python
5. Conclusion

This tutorial will demonstrate converting an XLSX file to CSV in Python.

the XLSX and CSV File Formats

The default format of an excel file is XLSX. It stores all the workbook data and the formulas, graphs, and other things.

We can also store an Excel workbook as a CSV file.

A CSV is a comma-separated text file. This text file can be accessed using a simple text editor as well.

A CSV file takes less memory and can be accessed more quickly. However, a CSV file only stores the data.

All the formulas, charts, and pivots will be lost if an Excel workbook is stored as CSV.

XLSX is the latest format of excel workbooks. Till Excel 2003, the file format was XLS.

The methods discussed below will work for both file formats.

Use the Pandas Library to Convert XLSX to CSV File in Python

The pandas module allows us to create and work with DataFrame objects. The data is organized into rows and columns in a DataFrame.

We can read XLSX and CSV files into a DataFrame using the Pandas library.

To convert XLSX to CSV using Pandas, we will read an XLSX file into a DataFrame and export this as a CSV file.

To read excel files, we can use the pandas.read_excel() function. This stores the data in a DataFrame.

Then, this is saved as a CSV file using the pandas.to_csv() function.

Example:

import pandas as pd

df = pd.read_excel("sample.xlsx")
df.to_csv("sample.csv")

Use the xlrd and csv Modules to Convert XLSX to CSV File in Python

The xlrd module provides an efficient way to read excel files. The file’s contents can be written to a CSV file using the csv module.

Let us discuss how.

The xlrd.open_workbook() can be used to read an XLSX workbook. We assume that we only want to convert the first sheet of the workbook to CSV.

This sheet is accessed using the sheet_by_index() function. The index of the first sheet, which is zero, is passed to this function.

We will create a CSV file using the open() function, and create a writer object using the csv.writer() constructor. This object will allow us to write data to the CSV file.

We will iterate the total number of rows in the file and write each row using the writer object with the writerow() function. We get the row’s content using the row_values() function.

We will implement this in the following example.

import xlrd
import csv

ob = csv.writer(open("sample.csv", "w", newline=""))
data = xlrd.open_workbook("sample.xlsx").sheet_by_index(0)
for r in range(data.nrows):
    ob.writerow(data.row_values(r))

Use the openpyxl and csv Modules to Convert XLSX to CSV File in Python

The openpyxl module is used in Python to perform reading and writing operations on Excel files. We can use this module with the csv library in a similar approach as we did previously.

The openpyxl module will be used to read the XLSX file using the load_workbook() function. We will only convert the current sheet to CSV.

This sheet is accessed using the active attribute.

We will write the contents of this sheet to the CSV file using the csv.writer object, as done previously. We will iterate through the sheet and read the contents of the row using list comprehension.

These contents will be written to the CSV file.

See the code below.

import openpyxl
import csv

ob = csv.writer(open("sample.csv", "w", newline=""))
data = openpyxl.load_workbook("sample.xlsx").active
for r in data.rows:
    row = [a.value for a in r]
    ob.writerow(row)

Conclusion

This tutorial discussed the methods to convert XLSX files to CSV using Python.

The pandas module provides the simplest way to achieve this in three lines of code. The other methods require reading XLSX files using the xlrd and openpyxl modules and writing them to CSV files using the csv module.