Convert CSV to Excel using openpyxl in Python

CSV stands for comma-separated value files that store content in plaintext format separated using delimiters like commas in a series of rows. Excel on the other hand stores content in cells via horizontal and vertical rows and columns. When we have complex data for analysis we often find it necessary to convert CSV files to EXCEL. Thus, in this tutorial, we will learn how to convert CSV files to EXCEL using openpyxl in Python.

Follow the steps given below to convert a CSV file to Excel using the openpyxl library in Python:

Step 1: Install openpyxl package

Openpyxl is a library in Python that is used to manipulate EXCEL workbooks through Python. Using this library we can read and write to excel sheets easily using simple python based commands. Use the below-mentioned command to install this library on your local computer.

pip install openpyxl

You can check: How to install openpyxl in Python

Step 2: Import the openpyxl and CSV library

Import the openpyxl library to manipulate the excel workbook in Python using the following command. We use to CSV package to deal with CSV-style data in Python.

import csv
import openpyxl

Suppose we have a products CSV file which stores the products, their cost price, selling price, and profit as shown below.

Step 3: Create a workbook using openpyxl

Next, we shall create a workbook using the workbook() function containing at least one excel worksheet and we shall activate this workbook to use it using the activate function()

wb = openpyxl.Workbook()
ws = wb.active

Step 3: Open CSV and read it using the CSV library

We will use the open() function in Python which will open the CSV file by taking the file as an input. It will return the file object as the output. Next, we use the reader() which will read the file object, and use the delimiter as a comma. It gives an iterable reader object as an output.

with open('items.csv') as f:
    reader = csv.reader(f, delimiter=',')

Step 4: Add rows to Worksheet

The contents of each row can be obtained by using a for loop on the iterable reader object. Each of these rows is appended to our excel worksheet using the append() method of openpyxl.

for row in reader:
    ws.append(row)

Step 4: Save the workbook in EXCEL format

Finally, we save the workbook on our local computer as an excel file at the desired location using the save() function. This function takes the desired path where the workbook is to be saved as an input.

wb.save('output.xlsx')

The output.xlsx file generated is:

Thus we reach the end of this tutorial on how to Convert CSV to Excel using openpyxl in Python. To read more about openpyxl click on the following link: Get values of all rows in a particular column in openpyxl in Python