Create a GUI to convert CSV file to Excel file using Python

As businesses grow and expand, they need to manage a lot of information. This information may come in different formats, and being able to convert them to the right format is important for the smooth running of business operations. One of the ways to handle this is by converting CSV files to excel format. In this tutorial, we will build a Graphical User Interface (GUI) in Python to convert CSV files to excel files.

What is a CSV file and what are importance of using CSV files in python

A CSV (Comma Separated Values) file is a plain text file that stores tabular data in a structured format. Each line in a CSV file represents a record, and each record contains one or more fields separated by commas. CSV files are widely used in data science, machine learning, and other fields where tabular data needs to be processed or analyzed.

In Python, CSV files are commonly used for storing and processing data. The key importance of using CSV files in Python are −

Easy to Read and Write − Python provides a built-in module called csv that makes it easy to read and write CSV files. This module includes functions that handle the low-level details of parsing CSV files, such as handling different field separators and handling quoted fields.

Lightweight and Efficient − CSV files are lightweight and take up minimal disk space, making them a good choice for storing and transferring large amounts of data. Python's built-in csv module is also efficient and can handle large CSV files without consuming too much memory.

Compatible with Other Programs − CSV files are widely used and supported by many different programs and tools. This makes them a good choice for exchanging data between different systems or applications.

Flexibility − CSV files can be easily customized to fit a variety of data formats and structures. For example, you can use different delimiters, such as tabs or semicolons, to separate fields in a CSV file.

Integration with Other Python Libraries − Python's pandas library, which is widely used in data science and analysis, includes a powerful CSV reading and writing engine that allows you to easily read and manipulate CSV files. This makes it easy to integrate CSV files into larger data processing workflows.

In summary, CSV files provide a simple and flexible way to store and exchange tabular data, and Python provides a powerful and efficient set of tools for working with CSV files.

What are the advantages of using Excel?

Excel is a popular spreadsheet software application developed by Microsoft. It is widely used in businesses, schools, and personal settings for a variety of purposes. Here are some advantages of using Excel −

Data organization − Excel allows you to organize data in a tabular format, making it easy to view and manipulate large amounts of data. You can sort, filter, and group data to quickly identify patterns and trends.

Calculation − Excel has powerful calculation capabilities, allowing you to perform complex calculations and analysis on your data. You can use formulas, functions, and pivot tables to analyze your data and generate insights.

Visualization − Excel allows you to create charts and graphs to visualize your data. This makes it easy to communicate insights and trends to others.

Collaboration − Excel allows multiple users to work on the same spreadsheet simultaneously, making it easy to collaborate on projects with others. You can also track changes and comments, making it easy to review and approve changes.

Integration − Excel can be integrated with other software applications, such as databases and other data sources, making it easy to import and export data. This allows you to work with data from different sources in a single spreadsheet.

Automation − Excel has powerful automation capabilities, allowing you to automate repetitive tasks and save time. You can use macros and VBA (Visual Basic for Applications) to automate tasks such as formatting, data entry, and reporting.

In summary, Excel is a powerful tool for organizing, analyzing, and visualizing data. It allows you to collaborate with others and integrate data from different sources. It also provides automation capabilities to save time and increase efficiency.

Prerequisites

Before we dive into the details of creating a GUI, you should have a basic understanding of Python programming, object-oriented programming (OOP) concepts, and how to work with the Tkinter module.

List of recommended settings −

• pip install tkinter, pandas

• It is expected that the user will have access to any standalone IDE such as VS-Code, PyCharm, Atom or Sublime text.

• Even online Python compilers can also be used such as Kaggle.com, Google Cloud platform or any other will do.

• Updated version of Python. At the time of writing the article I have used 3.10.9 version.

• Knowledge of the use of Jupyter notebook.

• Knowledge and application of virtual environment would be beneficial but not required.

Steps required to accomplish the task

Step 1: Import the necessary modules

import tkinter as tk
import pandas as pd
from tkinter import filedialog

In this section, we will import the pandas library and the tkinter library as well. Pandas will be used to read the input CSV file, and tkinter will be used for building the GUI.

Step 2: Building the GUI

We will create a function named "CSV_to_Excel" that will be executed when the "Convert-Button" is clicked. This function reads the input CSV file, converts it to excel format and saves it with a ".xlsx" extension.

# Building the GUI
# Function to Convert CSV to Excel and save it
def CSV_to_Excel():
   # Get Input File Path
   input_file_path = filedialog.askopenfilename()
   # Read CSV
   df = pd.read_csv(input_file_path)
   # Get Output File Path and Name
   output_file_name = input_file_path.split("/")[-1][:-4]
   output_file_path = filedialog.asksaveasfilename(defaultextension=".xlsx", initialfile=output_file_name)
   # Convert CSV to Excel and Save
   df.to_excel(output_file_path, index=False)

Next, we need to create the GUI window. We can do this by creating an instance of the Tk() class from the Tkinter module. We can also set the title of the window using the title() method.

Step 3: Create a Main Window

The main window is the window that will contain our GUI. We will start the GUI window by creating an instance of the tkinter class “Tk()”. We will then set various attributes of the window, such as its title, background color, width and height.

# Creating Main Window
root = tk.Tk()
root.title("CSV to Excel Converter")
root.geometry("400x200")
root.config(bg="#f0f0f0")

Step 4: Create the Function to Check Domain Availability:

In this step, we will add a button to the window. This button will have the text "Convert", and will execute the CSV_to_Excel() function when clicked.

# Adding a Button to the Window
   convert_button = tk.Button(root, text="Convert", command=CSV_to_Excel, font=("Helvetica", 14), bg="#4caf50", fg="#ffffff")
   convert_button.pack(pady=20)

Step 5: Adding a File Dialog

In this step, we will add a file dialog to let the user select the input CSV file and to provide the user with an option to select the output file's name and directory.

# Adding File Dialog
filedialog = tk.filedialog 

Step 6: Running the GUI

Finally, we will run the GUI by calling the root.mainloop() function, which will display our GUI window.

# Running the GUI
root.mainloop()

Final code, program

import tkinter as tk
import pandas as pd
from tkinter import filedialog

# Building the GUI
# Function to Convert CSV to Excel and save it
def CSV_to_Excel():
   # Get Input File Path
   input_file_path = filedialog.askopenfilename()
   # Read CSV
   df = pd.read_csv(input_file_path)
   # Get Output File Path and Name
   output_file_name = input_file_path.split("/")[-1][:-4]
   output_file_path = filedialog.asksaveasfilename(defaultextension=".xlsx", initialfile=output_file_name)
   # Convert CSV to Excel and Save
   df.to_excel(output_file_path, index=False)

# Creating Main Window
root = tk.Tk()
root.title("CSV to Excel Converter")
root.geometry("400x200")
root.config(bg="#f0f0f0")

# Adding a Button to the Window
convert_button = tk.Button(root, text="Convert", command=CSV_to_Excel, font=("Helvetica", 14), bg="#4caf50", fg="#ffffff")
convert_button.pack(pady=20)

# Adding File Dialog
filedialog = tk.filedialog 

# Running the GUI
root.mainloop()

Output

The user needs to set the path of the file to be converted. The output will be stored in the desired folder.

Real-world Example

Assume a company collects data on their daily production in CSV format. They need to analyze and visualize the data in Excel for their management report. By using our CSV to Excel converter, the company can easily convert the daily production data from CSV format to Excel format with just a few clicks.

Limitations

• The code only works for converting CSV files to Excel files.

• Several CSV files cannot be converted at once

Conclusion

In this tutorial, we have learned how to build a GUI in Python to convert CSV files to Excel. We have also demonstrated how to use the tkinter library for building a GUI, and how the pandas library can be used to read and convert CSV files to Excel format. We have also provided an example of a real-world scenario where this tool could be useful. This tool can help businesses manage their data and save time in converting them into different formats.