How to read excel file from a location with some preconditions?

user_14690158 0
Viewed 33

  1. I need to read an excel where there are some folders and each folder contains many files in the location and my desired file will be in one of those folders. How I can read my desired file from that location? My file name is like Daily Report on (18-Nov-2020)_ALL.xlsm and I want to read as df1 = pd.read_excel(r'C:\(many folders here)\Daily Report on (18-Nov-2020)_ALL.xlsm',header = 1, usecols = "A:B,D:N,U,W,Z",skiprows = range(1,3)).

  2. The date part of my file name 18-Nov-2020 is not fixed and it is the current date of system. How can I change this date part in file name automatically everyday while run the program?

Looking forward to your support

pandas dataframe python-3.x excel
1 Answers
  1. Use glob module. '**' means 'all possible directories and subdirectories' Also make sure, recursive=True
from glob import glob
import os
today_file_path = glob(os.path.join(fixed_path, '**', today_filename), recursive=True)[0]
  1. Use date formatting. %d is day of the month, %b% is shortened name of the Month, %Y is year
from datetime import date
today_formatted = date.today().strftime('%d-%b-%Y')
today_filename = 'Daily Report on ({})_ALL.xlsm'.format(today_formatted)
print(today_filename)

output:

'Daily Report on (28-Nov-2020)_ALL.xlsm'
user_76234920
Related