5 Best Ways to Convert CSV to Excel Using openpyxl in Python

💡 Problem Formulation: Converting CSV files to Excel format is a common requirement for data analysis and reporting in Python. Importing data from a CSV file and then exporting it to an Excel file using the openpyxl library simplifies sharing and presenting data within the widely-used Excel platform. An example input would be a CSV file containing a list of products and their prices, and the desired output is an Excel workbook with the same data properly formatted.

Method 1: Basic CSV to Excel Conversion

This method demonstrates the fundamental way to convert a CSV file to an Excel file using openpyxl. The function reads a CSV file line by line, adding corresponding cells to an Excel workbook, and then saves it as an XLSX file.

Here’s an example:

import csv
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

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

wb.save('products.xlsx')

The output is an Excel file named ‘products.xlsx’ containing the data from ‘products.csv’.

This code snippet opens ‘products.csv’, iterates through each row of the CSV file using a CSV reader, and appends each row of data to the active worksheet in the Excel workbook. Finally, it saves the workbook as an XLSX file.

Method 2: Preserving Data Types

While converting CSV data, preserving the original data types can be crucial. This method shows how to parse each cell’s data and keep the data types consistent with their original form, such as dates or numbers.

Here’s an example:

import csv
from openpyxl import Workbook
from openpyxl.utils import get_column_letter

def as_text(value):
    if value is None:
        return ""
    return str(value)

wb = Workbook()
ws = wb.active

with open('financials.csv', 'r') as file:
    reader = csv.reader(file)
    for row_index, row in enumerate(reader):
        for col_index, value in enumerate(row):
            ws[get_column_letter(col_index + 1) + str(row_index + 1)].value = as_text(value)

wb.save('financials.xlsx')

The output is an Excel file named ‘financials.xlsx’ with data types similar to the original CSV structure.

The code reads the CSV data and then converts each value to text before placing it into the Excel worksheet, thus preserving the data types. The get_column_letter function is used to translate column indices to Excel’s column letters.

Method 3: Adding Styles and Formats

Excel files often need styled cells for better readability. This method encloses the technique to format cells, such as applying fonts or setting column widths, while transferring data from CSV to Excel using openpyxl.

Here’s an example:

import csv
from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
bold_font = Font(bold=True)

with open('inventory.csv', 'r') as file:
    reader = csv.reader(file)
    for row_index, row in enumerate(reader):
        if row_index == 0:
            ws.append([cell.upper() for cell in row])
            for cell in ws[row_index + 1]:
                cell.font = bold_font
        else:
            ws.append(row)

ws.column_dimensions['A'].width = 20
wb.save('inventory_formatted.xlsx')

The output is an Excel file named ‘inventory_formatted.xlsx’, with headers in bold and the first column width set to 20.

The code applies a bold font to the header row and sets the width of the first column. It first reads the CSV file and for the first row (the headers), it applies the bold font style. Then, it proceeds to append each row of data to the Excel sheet.

Method 4: Including Multiple CSV Files in One Workbook

It’s often required to compile multiple CSV files into a single Excel workbook with each CSV representing a separate worksheet. This method demonstrates how to loop through a list of CSV files and convert each into a separate sheet in an Excel workbook.

Here’s an example:

import csv
from openpyxl import Workbook

csv_files = ['sales_q1.csv', 'sales_q2.csv', 'sales_q3.csv']
wb = Workbook()

for file in csv_files:
    ws = wb.create_sheet(title=file.replace('.csv', ''))
    with open(file, newline='') as f:
        for row in csv.reader(f):
            ws.append(row)

wb.remove(wb['Sheet'])
wb.save('quarterly_sales.xlsx')

The output is an Excel workbook named ‘quarterly_sales.xlsx’ with three sheets, each containing data from the respective CSV files.

The code snippet uses a list of CSV file names and iterates over the list, creating a new worksheet for each CSV file, adding rows from the CSV to the appropriate sheet, and finally saving the workbook. It also removes the default ‘Sheet’ that is added when a new Workbook is created.

Bonus One-Liner Method 5: Using Pandas for a Quick Conversion

The Pandas library has built-in functions to read CSV and output Excel files. This one-liner shows how to perform the conversion in a single step.

Here’s an example:

import pandas as pd

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

The output is an Excel file named ‘data.xlsx’ containing the data imported from ‘data.csv’.

This one-liner uses Pandas to read a CSV file and immediately write its data to an Excel file. The argument index=False ensures that Pandas does not write row indices to the Excel file.

Summary/Discussion

• Method 1: Basic CSV to Excel. Simple and straightforward. May not handle data types and formatting concerns.
• Method 2: Preserving Data Types. Takes extra steps to maintain data integrity. Slightly more complex.
• Method 3: Adding Styles and Formats. Provides visually appealing results. Requires knowledge of openpyxl styling capabilities.
• Method 4: Including Multiple CSV Files. Ideal for compiling multiple data sets. Increased complexity with managing multiple files.
• Bonus Method 5: Pandas One-Liner. Fast and efficient for simple datasets. Requires Pandas, which could be overkill for small tasks.