5 Best Ways to Convert CSV to XLSX in Python – Be on the Right Side of Change

💡 Problem Formulation: This article discusses the conversion of data from the CSV (Comma-Separated Values) format to the more feature-rich XLSX (Excel Spreadsheet) format using the Python programming language. For example, you want to transform customer_data.csv into customer_data.xlsx, preserving the structure and content but benefiting from the advanced functionalities of Excel.

Method 1: Using pandas

The pandas library in Python is a powerhouse for data manipulation and can be used to convert CSV files to XLSX efficiently. The process involves reading the CSV file into a DataFrame and then exporting this DataFrame to an XLSX file using the to_excel() method.

Here’s an example:

import pandas as pd

df = pd.read_csv('input.csv')
df.to_excel('output.xlsx', index=False)

The output will be an XLSX file named “output.xlsx” with the data from “input.csv”.

This code snippet first imports the pandas library and reads the CSV file into a DataFrame called ‘df’. The to_excel() method is then used to write the DataFrame to an Excel file, with index=False ensuring that no extra column is added for the row index.

Method 2: Using openpyxl and csv Libraries

openpyxl is a library dedicated to reading/writing Excel 2010 files. By combining openpyxl for the XLSX format and the built-in csv module for CSV parsing, you can have a more granular control over the conversion process.

Here’s an example:

import csv
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

with open('input.csv', 'r') as f:
    for row in csv.reader(f):
        ws.append(row)

wb.save('output.xlsx')

The output will be “output.xlsx” with the contents of “input.csv”.

In this method, we create a new Excel workbook with openpyxl, read the CSV using the csv module, and then append each row from the CSV to the workbook. Finally, save the workbook to an XLSX file.

Method 3: Using XlsxWriter

XlsxWriter is a comprehensive Python library dedicated to writing files in the XLSX file format. It provides great control over the Excel file creation process, which can be particularly useful for formatting and chart creation.

Here’s an example:

import csv
import xlsxwriter

workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

with open('input.csv', 'r') as csvfile:
    reader = csv.reader(csvfile)
    for r, row in enumerate(reader):
        for c, col in enumerate(row):
            worksheet.write(r, c, col)

workbook.close()

The output is an XLSX file “output.xlsx” based on “input.csv”.

This snippet utilizes the xlsxwriter library to create a workbook and worksheet, then reads the CSV row by row and cell by cell, writing the data to the worksheet. The workbook is then closed, saving the XLSX file.

Method 4: Using xlwt and xlrd

xlwt and xlrd are two older libraries for writing and reading Excel files in the .xls format, respectively. You can combine these to first import CSV data into an xls format and then convert to XLSX using additional libraries if necessary.

Here’s an example:

# Please note this method is more complex and outdated. 
# Skip to Summary/Discussion for alternative methods.

This method has fallen out of favor and isn’t demonstrated here, but it remains an option for legacy systems that may still use the older excel format.

Bonus One-Liner Method 5: Using pandas One-Liner

For a minimal-effort approach, you can convert a CSV to an XLSX file using a single line of code by chaining the read_csv() and to_excel() functions in pandas.

Here’s an example:

pd.read_csv('input.csv').to_excel('output.xlsx', index=False)

The resulting output is a direct conversion of “input.csv” to “output.xlsx”.

This compact form is the epitome of efficiency, reducing the conversion process to a single line by utilizing the power of pandas and its concise syntax.

Summary/Discussion

Method 1: Pandas. Great for simple conversions, provided that you have the library installed. It does require significant memory for large data sets. Supports additional Excel features like formatting.

Method 2: Openpyxl and csv. A good balance for finer control over the conversion process without having to rely on pandas. It is particularly useful when working on complex Excel files and requires the installation of openpyxl.

Method 3: XlsxWriter. Best for scenarios that require extensive formatting and charting capabilities during the conversion process. It adds an extra layer of control but requires you to manage the workbook and worksheet.

Method 4: xlwt and xlrd. These libraries are better suited for legacy systems that require support for older file formats. Not recommended for new projects due to their lack of features and support.

One-Liner Method 5: pandas One-Liner. The most efficient and straightforward method that showcases the power of pandas’ simplicity and ease of use. Ideal for quick scripts where file size is not an issue.