I am writing a cleaning function to clean my excel files to analyse them. I get a excel sheet everyday which is named for example:
"tourniquets_27.07.2022_raw.xls"
I get such a sheet everyday and so the date changes to that day. I now change the dates manually but I would love to make this automatic. I also save the file as follow: "tourniquets_28.07.2022_cleaned.xls". The dates need to bne changed here as well.
I tried the following code but it gives an error, I tried other things as well but this seems to be the most close to what I want.
import pandas as pd
import os
from pathlib import Path
from datetime import date
#assuming the files are in the directory:
folder = Path("C:/Users/JHA4/Desktop/Code/Tourniquets/Cleaned")
date_string = f"tourniquets_{date.today().month}.{date.today().day}.{date.today().year}_raw.xlsx"
xlsx_file = folder.glob(date_string)
#read in data
df = pd.read_excel(io=next(xlsx_file)
#Save it back to excel with a new name=
df.to_excel(io=next(xlsx_file))
Hope that I asked everything clearly! Thank you in advance.