Read_Excel for Excel Workbooks with rolling date

Question

I'm trying to write some code that begins with pulling data from an Excel workbook that is dropped daily into a folder OR pulled from an email attachment.

The workbook has a naming function like this: Workbook 20190821 (tomorrow the workbook name will be Workbook 20190822 I would like to make this process as touch-free as possible so is there a way to channel pandas.read_excel() (or some other function) that can handle rolling dates?

Barring some built-in method already available in Python, I wonder if a For Loop that increases by business day and then saves as Path name would work?

So you want to write a script that is reading the newest workbook from the folder? Are you aiming to run the script daily with scheduler? — Heikura, Aug 21 '19 at 07:33
Yes I would like to write a script (fgos in python) that can read the newest workbook in the folder. Would aim to run daily, but not sure what you mean by 'scheduler'? — Tony, Aug 21 '19 at 08:25
With scheduler I mean for example windows task scheduler to make the process automatic. :) — Heikura, Aug 21 '19 at 08:29
Check this: https://www.youtube.com/watch?v=n2Cr_YRQk7o . :) — Heikura, Aug 21 '19 at 08:42

score 0 · Accepted Answer · answered Aug 21 '19 at 08:34

This script would work for searching today's worksheet from the folder path. Is this what you are looking for?

import datetime
import pandas as pd
import os

# File folder
file_path = 'your file path for the file'

# Get current date
num = datetime.datetime.today().strftime("%Y%m%d")

# Current date's workbook name
workbook_name = 'Workbook ' + num

# Complete path
path = os.path.join(file_path, workbook_name)

# Read workbook to Pandas dataframe
df = pd.read_excel(workbook_name)

If you want to find the newest file (if the daily file has not arrived), check this: How to get the latest file in a folder using python

Read_Excel for Excel Workbooks with rolling date

1 Answers1