Create Dynamic Excel path to read in Python

Question

Happy NYE! Hopefully this is the last question of the year from me. I am trying to create a dynamic excel path for python to extract various excel files based on the date, type parameters, etc.

I have created below parameters which I can change base on the file I am trying to retrieve:

date = input() [assign '201912']
type = input() [assign 'abc']

I am trying to figure out a way to incorporate these into the file directory I am asking python to read such that it returns the same result as below:

import pandas as pd
sheet=pd.ExcelFile(r'C:\Information\Management\201912\abc\abc Template 201912.xlsm')

Have tried few different ways but cant seem to get it to work. Any suggestions on this?

Thank you very much!

I tried sheet=pd.ExcelFile(r'C:\Information\Management\%s\abc\abc Template 201912.xlsm'%date) which works but how would you go about this if you have more than one value to replace — AMY
– AMY, Commented Dec 31, 2019 at 16:23

Code Different · Accepted Answer · 2019-12-31 16:54:20Z

1

You can format it with the % sign like in Python 2 (not recommended):

path = r'C:\Information\Management\%s\%s\%s Template %s.xlsm' % (date, type, type, date)

The Pythonic way in Python 3 is to use str.format:

path = r'C:\Information\Management\{}\{}\{} Template {}.xlsm'.format(date, type, type, date)

Or with named parameters:

path = r'C:\Information\Management\{date}\{type}\{type} Template {date}.xlsm'.format(date=date, type=type)

Starting with Python 3.6, you can also use f-strings:

path = f'C:\\Information\\Management\\{date}\\{type}\\{type} Template {date}.xlsm'
sheet = pd.read_excel(path)

edited Dec 31, 2019 at 16:54

answered Dec 31, 2019 at 16:35

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AMY Over a year ago

thanks so much for answering! I am using python 3 unfortunately. What would be the corresponding code to make it work ?

Code Different Over a year ago

You get find out the exact version of Python you are using by typing python --version in the command line. BTW, 3.6 is Python 3

Collectives™ on Stack Overflow

Create Dynamic Excel path to read in Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related