Convert a csv file to an excel file using pandas and openpyxl

In this article we show you how to convert a csv file to an excel file

Here’s a simple Python script that converts CSV files to Excel using the pandas library. You can run this script after installing the required libraries.

Prerequisites

Ensure you have pandas and openpyxl installed. You can install them using

pip install pandas openpyxl

Now for the python code

import pandas as pd
import os

def csv_to_excel(csv_file_path, excel_file_path):
    # Read the CSV file
    df = pd.read_csv(csv_file_path)
    
    # Convert CSV to Excel
    df.to_excel(excel_file_path, index=False)
    print(f"CSV file '{csv_file_path}' has been successfully converted to Excel file '{excel_file_path}'")

def main():
    # Specify the directory containing CSV files
    csv_directory = "path/to/csv_directory"
    
    # Specify the directory to save Excel files
    excel_directory = "path/to/excel_directory"
    
    # Ensure the Excel directory exists
    if not os.path.exists(excel_directory):
        os.makedirs(excel_directory)
    
    # Convert all CSV files in the specified directory to Excel
    for csv_file in os.listdir(csv_directory):
        if csv_file.endswith(".csv"):
            csv_file_path = os.path.join(csv_directory, csv_file)
            excel_file_path = os.path.join(excel_directory, csv_file.replace(".csv", ".xlsx"))
            csv_to_excel(csv_file_path, excel_file_path)

if __name__ == "__main__":
    main()

Usage

1. Modify the Script:
   • Update the csv_directory with the path to your directory containing CSV files. I used csv_directory
   • Update the excel_directory with the path to the directory where you want to save the converted Excel files. I used csv_directory
2. Run the Script:
   • Save the script to a .py file, for example, csv_to_excel_converter.py.
   • Run the script from your command line or terminal:
     python csv_to_excel_converter.py


Explanation

• Import Libraries: The script imports the pandas library for data manipulation and os library for directory operations.
• csv_to_excel Function: This function reads a CSV file into a pandas DataFrame and then writes it to an Excel file.
• main Function: This function handles the directory operations, ensuring that the output directory exists and converting all CSV files in the specified directory.
• Directory Paths: The paths are configurable to make the script flexible for different use cases.

This script will take all CSV files from the specified directory, convert them to Excel format, and save them in the designated output directory.

Here is the output I saw when i ran the script in Thonny, I had a test file called test.csv in the csv folder

>>> %Run csv_to_excel_converter.py
CSV file ‘csv_directory\test.csv’ has been successfully converted to Excel file ‘excel_directory\test.xlsx’