Convert Excel to CSV or CSV to Excel with Python (Simple Example)
Excel and CSV (Comma Separated Values) are two popular file formats used for storing and managing data. Excel is a spreadsheet program developed by Microsoft, while CSV is a simple text format that stores data in tabular form. These file formats are widely used across different industries and sectors, as they provide a simple and efficient way to organize and manage data.
Converting between Excel and CSV files is a common task that many people need to perform. There are several reasons why you might want to convert between these file formats. For example, you may need to convert an Excel file to CSV format in order to import it into a database system more efficiently. Alternatively, you may need to convert a CSV file to Excel format to perform advanced analysis or create charts and graphs.
In this article, we will explore how to convert Excel to CSV or convert CSV to Excel with Python.
Python Library to Convert Between Excel and CSV File Formats
To convert between Excel and CSV files, this article uses a Python Excel library: Spire.XLS for Python.
Spire.XLS for Python is a multifunctional and easy-to-use library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can easily work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.
You can easily install Spire.XLS for Python from pypi by running the following commands in your terminal:
pip install Spire.XlsFor more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.
Convert Excel XLS or XLSX to CSV in Python
The following code sample shows how to convert a specific worksheet of an Excel XLS or XLSX file to CSV format using Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load an Excel XLSX file
workbook.LoadFromFile("Input.xlsx")
#Or load an Excel XLS file
#workbook.LoadFromFile("Input.xls")
#Get the first sheet of the file
sheet = workbook.Worksheets[0]
#Save the worksheet to a CSV file with a specific separator
sheet.SaveToFile("ExcelToCsv.csv", ",", Encoding.get_UTF8())
workbook.Dispose()If you want to convert all worksheets in an Excel file to separate CSV files, refer to the following code sample:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load an Excel XLSX file
workbook.LoadFromFile("Input.xlsx")
#Or load an Excel XLS file
#workbook.LoadFromFile("Input.xls")
#Iterate through the worksheets in the file
for sheet in workbook.Worksheets:
fileName = sheet.Name + ".csv"
#Save each worksheet to a separate CSV file with a specific separator
sheet.SaveToFile(fileName, ",", Encoding.get_UTF8())
workbook.Dispose()Convert CSV to Excel XLS or XLSX in Python
The following code sample shows how to convert a CSV file to an Excel XLS or XLSX file using Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load a CSV file
workbook.LoadFromFile("Input.csv", ",", 1, 1)
#Get the first worksheet
sheet = workbook.Worksheets[0]
#Set ignore error option
sheet.Range["D2:E19"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText
# Auto-fit column widths
sheet.AllocatedRange.AutoFitColumns()
#Save the CSV file to an XLSX file
workbook.SaveToFile("CsvToExcel.xlsx", ExcelVersion.Version2016)
#Or save the CSV file to an XLS file
#workbook.SaveToFile("CsvToExcel.xlsx", ExcelVersion.Version97to2003)
workbook.Dispose()Conclusion
In this article, we have discussed how to convert Excel to CSV or CSV to Excel with Python. We hope it’s helpful to you.
Related Topics
Easily Create or Edit Excel Files with Python
Python — How to Convert Excel XLS or XLSX to PDF
Read Data from Excel Files in Python — A Comprehensive Guide
Python — How to Export Data from Database to Excel (Step by Step Guide)
