Adding changing dates in a string-CodePudding

I'm using xlwings to pull in a excel file from a shared drive. The files' names change daily based off the data. Eg;

dailysummary_20220429.xlsx
dailysummary_20220428.xlsx
dailysummary_20220427.xlsx
dailysummary_20220426.xlsx

I'm trying to make the code dynamic so that it pulls in today's file each day but struggling with the syntax to make this work. Any help would be much appreciated. So far I have;

from datetime import date
workbook = xw.Book(r'I:\Analytics\dailysummary_{date.today()}.xlsx')
sheet1 = workbook.sheets['OutputTable'].used_range.value
dailydata = pd.DataFrame(sheet1)

Thanks so much!

CodePudding user response：

as suggested by MattR above, you need to format a date the way you want. It will work, but you are using the wrong type of string literal for your purposes.

workbook = xw.Book(f'I:\Analytics\dailysummary_{date.today().strftime("%Y%m%d")}.xlsx')

an f string lets you do the interpolation. A raw string (prefixed with an r) is sort of the opposite -- no interpolation at all

CodePudding user response：

I like breaking things up a little more, might look like overkill though. It allows for easier refactoring. The pathlib module will help you in the future if files start to move, or you get into wanting to use the pathlib.Path.cwd() or .home() to get base paths without needing to change the code all of the time.

The today_str allows you to override the date if you need an old one or something. Just pass '20220425' or whatever.

import datetime as dt
import pathlib

import pandas as pd
import xlwings as xw

def get_dailydata_df(today_str: str = None) -> pd.DataFrame:
    base_path = pathlib.Path('I:/Analytics')

    if today_str is None:
        today_str = dt.datetime.now().strftime('%Y%m%d')

    file_str = 'dailysummary_'
    file_str = file_str   today_str   '.xlsx'
    today_path = pathlib.Path(base_path, file_str)

    wb = xw.Book(today_path)
    sheet1 = wb.sheets['OutputTable'].used_range.value
    dailydata = pd.DataFrame(sheet1)

    return dailydata