How can I read a file with now time involved in the full path

Question

I want to read the cell V31 in a xls file but failed How can I open a file with function in the full path?

import datetime
import win32com.client as win32


def time():
    lastBusDay = datetime.datetime.today()
    shift = datetime.timedelta(max(1,(lastBusDay.weekday() + 6) % 7 - 3))
    lastBusDay = lastBusDay - shift
    return lastBusDay.strftime("%Y%m%d")
print(time())

def open_file():
    a = r"Z:\Sea\Excess Margin\GS Reports"
    xls = pd.ExcelFile(r"C:\User\" +  time + "\aaa.xls")
 
    sheetX = xls.parse(0) #2 is the sheet number+1 thus if the file has only 1 sheet write 0 in 
    paranthesis

    var1 = sheetX['V31']
    print(var1[1])
open_file()

I have undone the seemingly accidental edit which removed the information which the answers are based on. Please keep your questions answerable, even after you got an answer. — Yunnosch, Sep 28 '22 at 09:08

Daniil Fajnberg · Accepted Answer · 2022-09-14T08:17:26.130

Your problem is that you are escaping the closing quotes with that backslash \ when you do this:

r"C:\User\"

This means the string continues and that last " just becomes part of the string. Meaning all this becomes one string:

r"C:\User\" +  time + "

In addition, if you want the return value of your time function to included in the path, you need to actually call it, meaning you need to do time(), not just time. The latter is just a reference to the callable/function, while the former actually calls it and returns a value.

To avoid most path problems I would suggest using pathlib whenever possible. The Path class takes care of correctly concatenating parts of a file path, automatically taking into account your operating system, and also allows you to do a whole bunch of other useful stuff with paths.

Here is how I would do it in your example:

    ...
    path = Path("C:/User", time(), "SRMR_207228_1200326491_Portfolio_Margin_286165_1.xls")
    xls = pd.ExcelFile(path)
    ...

PS:

Since there seem to be some misconceptions about how raw string literals work in Python, here is the relevant section in the documentation. An this is the relevant part:

The backslash (\) character [...] can also be used to escape characters that otherwise have a special meaning, such as newline, backslash itself, or the quote character. [...] Even in a raw literal, quotes can be escaped with a backslash.

(Abbreviated and highlighted by me.)

Okay, my bad. https://stackoverflow.com/questions/647769/why-cant-pythons-raw-string-literals-end-with-a-single-backslash — mkrieger1, Sep 14 '22 at 08:04
You quoted a part of the documentation that is about regular strings, not raw strings, though. — mkrieger1, Sep 14 '22 at 08:13

How can I read a file with now time involved in the full path

1 Answers1