Exporting Multiple Sheets As Csv Using Python
Last Updated :
27 Mar, 2024
In data processing and analysis, spreadsheets are a common format for storing and manipulating data. However, when working with large datasets or conducting complex analyses, it's often necessary to export data from multiple sheets into a more versatile format. CSV (Comma-Separated Values) files are a popular choice due to their simplicity. In this article, we'll explore two different methods for exporting multiple sheets as CSV using Python, along with code examples.
Exporting Multiple Sheets As Csv Using Python
Below are examples of exporting multiple sheets as CSV using Python. Before exporting multiple sheets as CSV files, we need to install the Pandas and Openpyxl libraries, which help us convert sheets into CSV. To install these libraries, use the following command:
pip install pandas
pip install openpyxl
File Structure:

file.xlsx

Example 1: Exporting Multiple Sheets As Csv Using Pandas
In this example, below code reads an Excel file into a Pandas ExcelFile object, iterates through each sheet, converts each sheet into a DataFrame, and exports it to a CSV file with the same name as the original sheet.
Python3
import pandas as pd
# Read Excel file into a Pandas ExcelFile object
xls = pd.ExcelFile('file.xlsx')
# Iterate through each sheet in the Excel file
for sheet_name in xls.sheet_names:
# Read the sheet into a DataFrame
df = pd.read_excel(xls, sheet_name)
# Export the DataFrame to a CSV file
df.to_csv(f'{sheet_name}.csv', index=False)
Output
sheet1.csv
ID ,Name
1A,Apple
1B,Ball
1C,Cat
1D,Dog
After file Structure

Example 2: Exporting Multiple Sheets As Csv Using openpyxl and csv
In this example, below Python code uses the 'openpyxl' and 'csv' modules to convert each sheet from an Excel workbook ('file.xlsx') into separate CSV files. It iterates through each sheet in the workbook, opens it, and writes its contents row by row into a newly created CSV file with the same name as the original sheet. This process allows for efficient extraction of data from multiple sheets in Excel into CSV format using Python.
Python3
import openpyxl
import csv
# Open the Excel workbook
workbook = openpyxl.load_workbook('file.xlsx')
# Iterate through each sheet in the workbook
for sheet_name in workbook.sheetnames:
# Open the sheet
sheet = workbook[sheet_name]
# Create a CSV file
with open(f'{sheet_name}.csv', 'w', newline='') as csvfile:
writer = csv.writer(csvfile)
# Iterate through rows in the sheet and write to CSV
for row in sheet.iter_rows(values_only=True):
writer.writerow(row)
Output
sheet1.csv
ID ,Name
1A,Apple
1B,Ball
1C,Cat
1D,Dog
After file Structure

Conclusion
In conclusion , Exporting multiple sheets as CSV using Python can greatly streamline data processing tasks, allowing for easy manipulation and analysis in various software environments. In this article, we explored two methods for achieving this: using the Pandas library and using openpyxl along with the csv module. Whether you prefer the simplicity of Pandas or the flexibility of openpyxl.
Similar Reads
How To Create A Csv File Using Python CSV stands for comma-separated values, it is a type of text file where information is separated by commas (or any other delimiter), they are commonly used in databases and spreadsheets to store information in an organized manner. In this article, we will see how we can create a CSV file using Python
3 min read
Copy Rows and Columns in Excel Using Python Manipulating data in Excel often involves tasks such as copying specific rows or columns based on certain conditions. Python, with its powerful libraries like Pandas and openpyxl, provides efficient solutions for handling Excel files. In this article, we will explore how to copy rows and columns in
3 min read
How to Append Data in Excel Using Python We are given an Excel file and our task is to append the data into this excel file using Python. In this article, we'll explore different approaches to append data to an Excel file using Python. Append Data in Excel Using PythonBelow, are some examples to understand how to append data in excel using
2 min read
Python Program to Generate Disk Usage Report in CSV File In this article, we will see how we could generate disk usage reports and store them in a CSV file using Python using multiple methods.Required ModulesWe will be using 4 methods here, the modules required would be Shutil, pandas, WMI, OS, and subprocess.WMI - WMI is a set of specifications from Micr
7 min read
Convert Dict of List to CSV - Python To convert a dictionary of lists to a CSV file in Python, we need to transform the dictionary's structure into a tabular format that is suitable for CSV output. A dictionary of lists typically consists of keys that represent column names and corresponding lists that represent column data.For example
3 min read
How to Convert Tab-Delimited File to Csv in Python? We are given a tab-delimited file and we need to convert it into a CSV file in Python. In this article, we will see how we can convert tab-delimited files to CSV files in Python. Convert Tab-Delimited Files to CSV in PythonBelow are some of the ways to Convert Tab-Delimited files to CSV in Python: U
2 min read
Convert Excel To Json With Python In the realm of data manipulation and analysis, the ability to convert data between different formats is a valuable skill. Excel and JSON are two widely used formats, and Python, with its powerful libraries, provides a seamless way to convert Excel files into JSON. In this article, we will see how w
4 min read
Fastest Way to Read Excel File in Python Reading Excel files is a common task in data analysis and processing. Python provides several libraries to handle Excel files, each with its advantages in terms of speed and ease of use. This article explores the fastest methods to read Excel files in Python.Using pandaspandas is a powerful and flex
3 min read
How To Write A Multidimensional Array To A Text File? In this article, we will explain how to write a multidimensional array to a text file with the help of a few examples. Writing a multidimensional array to a text file is a straightforward process, and it allows you to print the array directly to the text file in a simple manner. Write A Multidimensi
3 min read
Export Data From Mysql to Excel Sheet Using Python We are given a MySQL database and our task is to export the data into an excel sheet using Python. In this article, we will see how to export data from MySQL to Excel Sheets using Python. Export Data From Mysql to Excel Sheet Using PythonBelow are some of the ways by which we can export data from My
3 min read