I have an extra sized excel file and I need to automate a task I do everyday: Add rows to the bottom with the day's date, save a new workbook, crop the old ones and save as a new file with the day's date.
An example is today only having rows with date 04-10-2016 and the filename would be [sheetname]04102016H12
or [sheetname]04102016H16
if it has passed 12 pm.
I've tried xldr, doing this in VBA and so on but I can't get along with VBA and it is slow. So I'd rather use Python here - lightweight, does the job and so on.
Anyway, so far, I have done the follwing:
import xlsxwriter, datetime, xlrd
import pandas as pd
# Parsing main excel sheet to save the correct
with xlrd.open_workbook(r'D:/path/to/file/file.xlsx', on_demand=True) as xls:
for sheet in xls.parse(xls.sheet_names([0])):
dfs = pd.read_excel(xls, sheet, header = 1)
now = datetime.date.today()
df[df['Data'] != now]
if datetime.time()<datetime.time(11,0,0,0):
df.to_excel(r'W:\path\I\need'+str(sheet)+now+'H12.xlsx', index=False)
else:
df.to_excel(r'W:\path\I\need'+str(sheet)+now+'H16.xlsx', index=False)
Unfortunately, this does not separate the main file into as many files as worksheets the workbook contains. It outputs TypeError: 'list' object is not callable
, regarding this in xls.parse(xls.sheet_names([0]))
.