We often need to convert multiple Excel sheets (tabs) into CSV files for better compatibility with databases and other tools. I usually use VBA for this task, but it’s not working today, so I decided to try Python, which seems easier.
In this guide, we’ll walk through how to convert each sheet in an Excel file into a separate CSV file using Python.
1. Install Required Libraries
Ensure you have the necessary Python libraries installed in your terminal:
openpyxl for handling Excel files
pandas for working with data and saving to CSV
2. Verify File Accessibility
Before working with your Excel file, confirm that your Python program can access it. Run the following script to check:
If the output shows False for readability or writability, follow the steps below to grant Python access to your files.
3. Grant Full Disk Access to Python (For Mac Users)
If you’re using macOS, Python may be restricted from accessing specific files due to System Integrity Protection (SIP). Follow these steps to grant access:
3.1 Open System Settings
Click the Apple menu in the top-left corner
Select System Settings
3.2 Navigate to Privacy & Security
Scroll down and click Privacy & Security
3.3 Grant Full Disk Access to Python
Scroll down and find Full Disk Access
Click the + (Add App) button
Locate and select Python
Toggle Python ON
Grant Full Disk Access to Python 4. Convert Excel Sheets to CSV Using Python
Once access is granted, use the following script to convert all sheets from an Excel file into separate CSV files:
Output Example:
Now, all sheets from the Excel file are converted into separate CSV files, making it easier to process, analyse the data, or import into an SQL database.
Conclusion
By following this guide, you’ve successfully:
✅ Installed the required libraries
✅ Ensured Python can access the Excel file
✅ Converted each sheet in an Excel file into a CSV file
This method helps streamline data conversion for database imports, data analysis, or any scenario requiring CSV format. 🚀
If you found this helpful, share your thoughts in the comments!
Comments