A Comprehensive Guide to Converting CSV to XLSX Files in Python

Introduction

CSV (Comma-Separated Values) and XLSX (Excel Open XML Spreadsheet) are widely used file formats for data storage. However, the older XLS format has a row limit of 65,536, which can be insufficient for large datasets. This article explores how to convert CSV files to XLSX using Python, focusing on the xlsxwriter library as the primary method, with alternatives provided for flexibility.

Primary Method: Using xlsxwriter

The xlsxwriter library is a powerful tool for creating Excel files in the XLSX format. It supports large files and offers extensive formatting options. To use it, first install the library via pip:

Here is a rewritten example based on the core concepts from the provided answer, which converts all CSV files in the current directory to XLSX:

import os
import glob
import csv
from xlsxwriter.workbook import Workbook

# Iterate over all CSV files in the current directory
for csvfile in glob.glob(os.path.join('.', '*.csv')):
    # Create a new XLSX workbook with the same base name
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet()
    
    # Open the CSV file and read its contents
    with open(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        # Enumerate rows and columns to write data
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    
    # Close the workbook to save the file
    workbook.close()

This code loops through each CSV file, creates a corresponding XLSX file, and writes all data row by row. The use of enumerate ensures proper indexing, and the write method handles cell population.

Alternative Methods

Other libraries can also achieve this conversion:

• Pandas: A data manipulation library that simplifies the process. Example code:

import pandas as pd
read_file = pd.read_csv('input.csv')
read_file.to_excel('output.xlsx', index=None, header=True)

• pyexcel: Allows batch conversion with minimal code. Example:

from pyexcel.cookbook import merge_all_to_a_book
import glob
merge_all_to_a_book(glob.glob("*.csv"), "output.xlsx")

• openpyxl: Another option for reading and writing XLSX files. Example:

from openpyxl import Workbook
import csv
wb = Workbook()
ws = wb.active
with open('test.csv', 'r') as f:
    for row in csv.reader(f):
        ws.append(row)
wb.save('output.xlsx')

Comparison and Recommendations

xlsxwriter is ideal for large files and custom formatting, while pandas is user-friendly for data analysis. pyexcel and openpyxl offer simplicity for specific use cases. Choose based on your project requirements.

Conclusion

Converting CSV to XLSX in Python is straightforward with libraries like xlsxwriter. This guide provides a comprehensive approach, ensuring compatibility with large datasets. Experiment with the code examples to find the best fit for your needs.