How to Convert Multiple Sheets in an Excel Workbook to CSV Files in Python

Learn how to easily convert multiple sheets from an Excel workbook to separate `CSV` files using Python and the Pandas library.
---
This video is based on the question https://stackoverflow.com/q/71605777/ asked by the user 'Rehan' ( https://stackoverflow.com/u/4866548/ ) and on the answer https://stackoverflow.com/a/71614824/ provided by the user 'Martin Evans' ( https://stackoverflow.com/u/4985733/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to convert multiple sheets in an excel workbook to csv files in python

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Convert Multiple Sheets in an Excel Workbook to CSV Files in Python

If you've ever worked with Excel files, you know that they can contain multiple sheets, each with its own unique set of data. When it comes to data analysis or sharing results, you may want to save each of these sheets as separate CSV files. This guide will guide you through the process of converting multiple sheets in an Excel workbook into individual CSV files using Python's powerful Pandas library.

The Problem

You might find yourself needing to convert all sheets in an Excel file into CSV files, particularly if you're working with larger datasets, or if you need to share data in a more universal format. For instance, you may have an Excel workbook with several sheets named with different alphabetical characters, and you want each of those sheets saved as its own CSV file within a specific folder.

Current Situation

You might already have a script that works for a single sheet, like the example below:

[[See Video to Reveal this Text or Code Snippet]]

While this code effectively converts the specified sheet to a CSV file, it doesn’t address how to handle multiple sheets. But don’t worry—here’s a simple solution!

The Solution: Iterating Over Multiple Sheets

To convert multiple sheets from an Excel workbook to CSV files, you can leverage the Pandas function read_excel() with a slight modification. The key is to set sheet_name=None when calling the read_excel() function. This returns a dictionary, where the keys are the names of the sheets and the values are the corresponding DataFrames.

Step-by-Step Guide

Import Pandas: Ensure you have the Pandas library installed. If you don’t have it installed yet, you can do so by running:

[[See Video to Reveal this Text or Code Snippet]]

Read Excel with All Sheets: Use the following code to read all sheets in your Excel file:

[[See Video to Reveal this Text or Code Snippet]]

Iterate and Save as CSV: You can now loop over the dictionary items, where each key-value pair corresponds to a sheet name and its DataFrame respectively. Each sheet will be saved as a CSV file with an appropriate filename:

[[See Video to Reveal this Text or Code Snippet]]

Complete Example

Here’s the combined code for better understanding:

[[See Video to Reveal this Text or Code Snippet]]

Important Note

Valid Filenames: Make sure your sheet names are suitable for file names—if they contain characters not allowed in filenames, you will need to handle those cases to avoid errors.

Conclusion

With just a few lines of code, you can efficiently convert multiple sheets from an Excel workbook into separate CSV files using Python. This method is not only simple but also scalable, and can be easily modified to fit your specific needs. By automating this process, you save time and reduce potential errors compared to manual conversions.

Happy coding! If you have any questions or encounter any issues, feel free to drop a comment below.

Видео How to Convert Multiple Sheets in an Excel Workbook to CSV Files in Python канала vlogize