Convert CSV to XLS PYQT5

Prerequisites

Before we start, make sure you have the following installed on your system:

• Python 3.x
• PyQt5: Install it using pip install pyqt5
• Pandas: Install it using pip install pandas
• PyInstaller: Install it using pip install pyinstaller

Step 1: Creating the Python Script

Create a new Python file named csv_to_xls_file_n_folder.py and paste the following code:

import sys
import pandas as pd
import re
import os
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QGridLayout, QPushButton, QFileDialog, QLabel, QDesktopWidget
from PyQt5.QtGui import QIcon, QFont
from PyQt5.QtCore import Qt

class CSVtoXLSXConverter(QWidget):
def __init__(self):
super().__init__()
self.initUI()

def initUI(self):
self.setWindowTitle('CSV to XLSX Converter')
self.resize(700, 300)  # Set the window size

# Set the icon for the application
icon_path = os.path.join(os.path.abspath(os.path.dirname(__file__)), 'icon.ico')
self.setWindowIcon(QIcon(icon_path))

mainLayout = QVBoxLayout()

self.statusLabel = QLabel('', self)
self.statusLabel.setAlignment(Qt.AlignCenter)
self.statusLabel.setFont(QFont('Arial', 12))
self.statusLabel.setStyleSheet("color: green;")
mainLayout.addWidget(self.statusLabel)

self.label = QLabel('Select a CSV file or folder to convert', self)
mainLayout.addWidget(self.label)

gridLayout = QGridLayout()

self.browseFileButton = QPushButton('Browse File', self)
self.browseFileButton.clicked.connect(self.browseFile)
gridLayout.addWidget(self.browseFileButton, 0, 0)

self.browseFolderButton = QPushButton('Browse Folder', self)
self.browseFolderButton.clicked.connect(self.browseFolder)
gridLayout.addWidget(self.browseFolderButton, 1, 0)

self.convertButton = QPushButton('Convert', self)
self.convertButton.clicked.connect(self.convert)
gridLayout.addWidget(self.convertButton, 0, 1, 2, 1)  # Make the Convert button larger with rowspan 2

mainLayout.addLayout(gridLayout)
self.setLayout(mainLayout)

self.center()  # Center the window on the screen

def center(self):
qr = self.frameGeometry()
cp = QDesktopWidget().availableGeometry().center()
qr.moveCenter(cp)
self.move(qr.topLeft())

def browseFile(self):
options = QFileDialog.Options()
options |= QFileDialog.ReadOnly
fileName, _ = QFileDialog.getOpenFileName(self, "Select CSV File", "", "CSV Files (*.csv)", options=options)
if fileName:
self.csvFilePath = fileName
self.csvFolderPath = None
self.statusLabel.setText('')  # Reset status label
self.label.setText(f"Selected file: {fileName}")

def browseFolder(self):
folderName = QFileDialog.getExistingDirectory(self, "Select Folder")
if folderName:
self.csvFolderPath = folderName
self.csvFilePath = None
self.statusLabel.setText('')  # Reset status label
self.label.setText(f"Selected folder: {folderName}")

def convert(self):
if self.csvFilePath:
self.convertFile(self.csvFilePath)
elif self.csvFolderPath:
self.convertFolder(self.csvFolderPath)
else:
self.label.setText("Please select a CSV file or folder first.")

def convertFile(self, filePath):
try:
df = pd.read_csv(filePath)

# Modify or remove unwanted characters
df = df.apply(lambda col: col.str.replace('unwanted_character', '') if col.dtype == 'object' else col)

# Remove all URLs in parentheses along with the preceding space
df = df.apply(lambda col: col.str.replace(r'\s*\(https?://[^\s]+?\)', '', regex=True) if col.dtype == 'object' else col)

# Replace "PT " with "PT. " and "CV " with "CV. "
df = df.apply(lambda col: col.str.replace(r'\bPT\s', 'PT. ', regex=True).str.replace(r'\bCV\s', 'CV. ', regex=True) if col.dtype == 'object' else col)

# Strip leading spaces from each column value
df = df.apply(lambda col: col.str.lstrip() if col.dtype == 'object' else col)

xlsxFilePath = filePath.replace('.csv', '.xlsx')
df.to_excel(xlsxFilePath, index=False)
self.statusLabel.setText(f"File successfully converted to: {xlsxFilePath}")
except Exception as e:
self.statusLabel.setText(f"Error: {str(e)}")
self.statusLabel.setStyleSheet("color: red;")

def convertFolder(self, folderPath):
try:
for fileName in os.listdir(folderPath):
if fileName.endswith('.csv'):
filePath = os.path.join(folderPath, fileName)
self.convertFile(filePath)
self.statusLabel.setText(f"All CSV files in folder {folderPath} successfully converted.")
except Exception as e:
self.statusLabel.setText(f"Error: {str(e)}")
self.statusLabel.setStyleSheet("color: red;")

if __name__ == '__main__':
app = QApplication(sys.argv)
ex = CSVtoXLSXConverter()
ex.show()
sys.exit(app.exec_())

Step 2: Adding the Icon File

Ensure that you have an icon file named icon.ico in the same directory as your Python script.

Step 3: Creating the Executable with PyInstaller

Use PyInstaller to create a single executable file. Open your terminal or command prompt and navigate to the directory containing your Python script and icon file. Run the following command:

pyinstaller --onefile --windowed --icon=icon.ico csv_to_xls_file_n_folder.py

This command will generate an executable file in the dist directory.

Conclusion

You have now created a CSV to XLSX converter application with Python and PyQt5 and packaged it into an executable file using PyInstaller. This application allows you to select a single CSV file or a folder containing multiple CSV files and convert them to XLSX format.