0

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]04102016H16if 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])).

Gustavo Silva
  • 159
  • 1
  • 10

1 Answers1

0

Based on comments below I am updating my answer. Just do:

xls.sheet_names()[0]

However, if you want to loop through the sheets, then you may want all sheet names instead of just the first one.

aberger
  • 2,144
  • 3
  • 12
  • 28
  • It now says `AttributeError: 'Book' object has no attribute 'parse_' `. – Gustavo Silva Oct 04 '16 at 15:38
  • typo on my part. But you can learn a lot by debugging errors on your own. – aberger Oct 04 '16 at 15:40
  • Still gives the same error as if parse has no attribute. This command should give all sheets in this workbook but that is not happening for some reason. – Gustavo Silva Oct 04 '16 at 15:59
  • Found in an answer [here](http://stackoverflow.com/questions/17977540/pandas-looking-up-the-list-of-sheets-in-an-excel-file) this is the code to get all sheets. However, also found [this](http://stackoverflow.com/questions/24179378/how-to-get-excel-sheet-name-in-python-using-xlrd), which might be worthy to try. – Gustavo Silva Oct 04 '16 at 16:18
  • What do you expect .parse() to do? I can't find it in documentation. – aberger Oct 04 '16 at 16:19