Python-Conversion Between Excel and CSV

Excel is one of the standard file formats used by Microsoft Excel, which can store spreadsheets containing multiple worksheets, charts, formulas and data. CSV, on the other hand, is a simple text file format that is widely used for data exchange and storage. It has no complex formatting specifications and usually contains only plain text data. Sometimes conversion between these two formats allows for more flexible handling of data across different platforms and applications. This post covers how to convert Excel to CSV in Python with sample code. And you can also convert CSV back to Excel via Python if necessary.

• Python Library for Excel Conversion
• Convert Excel to CSV in Python
• Convert CSV to Excel in Python
• Get a Free License for the Library to Convert Excel Files
• Conclusion
• See Also

Python Library for Excel Conversion

Spire.XLS for Python is a powerful spreadsheet manipulation API that allows you to create, modify or convert Excel files on Python platforms. With it, you are able to use Python to convert excel to csv and convert csv back to excel effortlessly.  Before that, please install Spire.XLS for Python and plum-dispatch v1.7.4, which can be easily installed in VS Code using the following pip commands.

pip install Spire.XLS

This article covers more details of the installation.This article covers more details of the installation: How to Install Spire.XLS for Python in VS Code

Convert Excel to CSV in Python

Converting Excel to CSV in Python helps you create a lighter, more readable backup of your data. It also provides a way to archive your data over time.

Steps

• Import the spire.xls library.
• Create a Workbook object to represent an Excel file.
• Use the Workbook.LoadFromFile() method to load an Excel file from the specified path.
• Get the first worksheet from this workbook by the Workbook.Worksheets[index] property, and the index here starts from 0.
• Call the Worksheet.SaveToFile() method to save the worksheet in CSV format using UTF-8 encoding and release resources.

Sample Code

from spire.xls import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel file from the specified path
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx")

# Get the first sheet from this workbook
sheet = workbook.Worksheets[0]

# Save the worksheet in CSV format using UTF-8 encoding
sheet.SaveToFile("C:\\Users\\Administrator\\Desktop\\ExcelToCSV.csv ", ",", Encoding.get_UTF8())
workbook.Dispose()

Convert CSV to Excel in Python

Excel provides a wealth of functions such as sorting, filtering, calculating, charting, and so on. By using Python to convert CSV to Excel, you can more conveniently use these functions to process data.

Steps

• Import the spire.xls library.
• Create a Workbook object to represent an Excel workbook.
• Use the Workbook.LoadFromFile() method to load a CSV file from the specified path.
• Get the first worksheet from this file by the Workbook.Worksheets[index] property, and the index here starts from 0.
• Set the Ignore Errors Option to display numeric values as text in the Excel file.
• Automatically adjust the column widths to fit the content.
• Call the Workbook.SaveToFile() method to save the file in Excel 2013 format at the specified path and release resources.

Sample Code

from spire.xls import *

# Create a Workbook object
workbook = Workbook()

# Load a CSV file
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\ExcelToCSV.csv", ",", 1, 1)

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Display numbers as text
sheet.AllocatedRange.IgnoreErrorOptions = IgnoreErrorType.NumberAsText

# Autofit column width
sheet.AllocatedRange.AutoFitColumns()

# Save to an Excel file
workbook.SaveToFile("C:\\Users\\Administrator\\Desktop\\CSVToExcel.xlsx", ExcelVersion.Version2013)

Get a Free License for the Library to Convert Excel Files

You can get a free 30-day temporary license of Spire.XLS for Python to use Python to convert Excel to CSV and CSV to Excel without evaluation limitations.

Conclusion

In this article, you have learned how to convert Excel to CSV and CSV to Excel in Python. Spire.XLS for Python supports a variety of document conversions on Excel, including Excel to PDF, Excel to images, Excel to HTML etc. In a word, this library simplifies the process and allows developers to focus on building powerful applications that involve Excel manipulation tasks.

• Read Excel File with Python
• How to Convert Excel to PDF in Python
• Merge Excel Files into One Using Python