Openpyxl with Python - How to delete spaces and replace with commas

Question

I am still new to python and have been asked to reformat a large spreadsheet. Trying to follow this system: https://i.stack.imgur.com/24Trp.png

I want to remove all the spaces in each cell from columns L & M. Then, I need commas to separate the values. I'm unsure how to do the latter since I need to keep the colons (TL:Accepted,AL:Accepted) as one value and then separate them from the next value with a colon. I have some experience with pandas, which could work in conjunction with openpyxl.

Here is my code so far:

# imports
import openpyxl as py
from openpyxl import Workbook, load_workbook

# create a workbook and active worksheet
wb = load_workbook(r'MERGEDSHEETS.xlsx')
print(wb.sheetnames)
ws = wb.active

for sheet[L2:, M2:]:
    py.strip()

wb.save(r'MERGEDSHEETSv2.xlsx')

That code won't run as it stands. You need to change to value of each cell in the range "L2:M2". This is covered in the openpyxl documentation. — Charlie Clark

Francisco Valadares Francisco Valadares · Accepted Answer · 2022-07-28T13:56:42

"...I want to remove all the spaces in each cell from columns L & M. Then, I need commas to separate the values. ..."

One suggestion (if your file is not many large) your would open in tools like microsoft(Excel) or LibreOffice and then save as like CSV file.

or with code:

import pandas as pd
data_xls = pd.read_excel(r'MERGEDSHEETS.xlsx', 'Sheet2', dtype=str, index_col=None)
data_xls.to_csv('MERGEDSHEETSv2.csv', encoding='utf-8', index=False, sep =';')

Openpyxl with Python - How to delete spaces and replace with commas

1 Answers