How to Convert between Excel and CSV in Python

#csv #excel #tutorial #python

Excel files are a widely used document format with rich tools and features, allowing users to easily create, organize, analyze, and visualize data in tabular form. CSV file, on the other hand, is a simpler file format primarily used for storing tabular data, such as spreadsheets or databases. It consists of plain text where each line represents a row of data, with values separated by commas. Due to its high compatibility, CSV can be easily used and processed in various software applications and operating systems. The conversion between these two document formats is also very practical. Here is some related code sharing.

Tool

Visual Studio Code
Spire.XLS for Python This is an Excel library supporting processing Excel document easily on Python platforms, such as creating Excel, reading Excel, converting Excel to image.

Installation

You can install Spire.XLS for Python in Visual Studio Code by performing the following steps:

First, make sure that you have download and install Python.
Open VS Code, click “Extensions”, search for “Python” and then install it.
Click “Explorer” > “NO FOLRDER OPENED” > “Open Folder”.
Choose a desired folder.
Add a “.py” file to this folder and name it whatever you like.
Click “Terminal” > “New Terminal”.
Last, input the following commands

pip install Spire.XLS-for-Python
pip install plum-dispatch==1.7.4

Sample Codes:

Excel to CSV

from spire.xls import *
from spire.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("sample.xlsx")

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

# Convert Excel to CSV 
sheet.SaveToFile("ToCSV.csv", ",", Encoding.get_UTF8())
workbook.Dispose()

In this code, you can convert Excel sheet to CSV format using SaveToFile() method. The "," parameter specifies the delimiter between values, and “Encoding.get_UTF8()” sets the encoding to UTF-8.

CSV to Excel

from spire.xls import *
from spire.common import *

# Create a Workbook object
workbook = Workbook()

# Load a CSV file
workbook.LoadFromFile("sample.csv", ",", 1, 1)

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

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

# Autofit column width
sheet.AllocatedRange.AutoFitColumns()

# Convert CSV to Excel
workbook.SaveToFile("ToExcel.xlsx", ExcelVersion.Version2013)

In this code, the CSV sheet was saved as "ToExcel.xlsx" by calling SaveToFile(). The “ExcelVersion.Version2013” parameter indicates the desired Excel version for compatibility.

DEV Community