Copy Excel file and make multiple daily versions

Question

Every month, my department has to generate blank upload files (.xlsx) for each day of the month.

The following code helps this process by copying a template, iterating through the number of days of the current month and generating a file for each of those days from the template.

I found the copy_rename function which helped a ton.

I'm looking for any feedback pertaining to:

1. Can I avoid the format_Num function somehow?
2. Do I need to .close() the file since I didn't open it?
3. Layout of the code: can I have my functions at the very bottom so that the code comes up first?

# checks whether a number is single digit or not
def format_Num(num):
    num = str(num)

    if len(num) == 1:
        return(str(0) + str(num))
    else:
        return str(num)

#function copies a file from one dir, to another

def copy_rename(old_file_name, new_file_name):
    src_dir= "C:\\a\\dir\\Desktop"
    dst_dir= "C:\\a\\different\\dir\\TESTPYTHONFILES"
    src_file = os.path.join(src_dir, old_file_name)
    shutil.copy(src_file,dst_dir)
    
    dst_file = os.path.join(dst_dir, old_file_name)
    new_dst_file_name = os.path.join(dst_dir, new_file_name)
    os.rename(dst_file, new_dst_file_name)

from calendar import monthrange
import shutil
import os
import datetime

now = datetime.datetime.now()
curr_year = now.year #2018
curr_month  = now.month #04

total_month_days = monthrange(curr_year, curr_month)[1] + 1
filepath = "C:\\a\\dir\\Desktop"
template = "Template.xlsx"

for day in range(1, total_month_days):

    wb_Name = "workFeed" + str(curr_year) + format_Num(curr_month) + format_Num(day) + ".xlsx"
    path_n_fileName = os.path.join(filepath + wb_Name)

    # verifies that the file does not exist    
    if os.path.isfile(path_n_fileName) != True:
        copy_rename(template, wb_Name)

Answer 1

Review

def format_Num(num):
    num = str(num) // is 'num' a binary value, or is 'num' a string?

    if len(num) == 1:
        return(str(0) + str(num))
    else:
        return str(num)

could be, perhaps:

def two_digits( num ):
    if num <= 9:
        return(str(0) + str(num))
    else:
        return str(num)

(Note the absence of upper_Case being mixed with snake_case.)

---

There are a number of hardwired directory paths and filenames, alternately referenced with "dir" or "path" and "file" or just a named variable. Lacks consistency!
Perhaps it would be better to gather these together hardwired at the top of the program for ease of access. (PS: A single forward slash can (likely) be used with your version of Windows.)

---

While it's good to try to prevent replacing an existing file with a "blank" version of the same name, this code is not 'atomic' (ie. there is a gap in time between the check and the performance of the action.) Suggest finding a more robust solution.

---

Design issues

This "generation" must be conducted once-and-only once each month. (Are 'weekend' dates necessary?) While a procrastinator (eg. "me") could put-it-off for quite a few days, if put-off for an entire month, this code cannot generate the files for the previous (missed) month to catch-up.

Humans being what they are, having ~30 files present before one finishes one's morning coffee may lead to filling in the wrong spreadsheet as the result of a poorly placed double-click. Recommend changing this "utility" to only create "today's" blank copy, to be executed once each day by the party responsible. (In this way, the program could also launch MS-Excel for the user.)

---

In light of a number of deficiencies and recommendations, my own suggestion is to find another Python project to develop Python skills, but to investigate, instead, the powers of using a "shell script/batch file".

• "String manipulation" for the 'date'/'filename' issue can be done with utilties, and
• most system "file copy" commands have options to prevent overwriting an existing file.

The OS and shell are anxious to serve. Why wake up the Python unnecessarily? KISS!

---

Speaking from a lot of business office experience (but none with the OP's actual data), it seems likely that a database solution would be better than a bevy of isolated Excel workbooks. Jus' saying...

---

OR...

Investigate MS-Excel's Template facilities for storing the original and creating instances. Consider using a directory hierarchy (eg: "{path}/YYYY/MM/DD.xlsx") for the local copy. Renaming the daily version(s) to be "uploaded" becomes a separate downstream issue, if still considered necessary.

---

OR...

... blank upload files ...

If the "data needs" are quite simple (ie. a single sheet/table of one day's transactions),
"Google Sheets®" may be worth investigating. A "shared workbook" would mean head office could watch the information being posted in near real time! Also, 'Sheets' offers a scripting language that might be used to automate some operations or entries.

Answer 2

leading zero

def format_Num(num):

PEP 8 is begging and pleading with you to spell it format_num.

An if statement can accomplish the business need, as the OP code shows. But this comes up quite often, and there's a standard answer for it:

def format_num(num: int) -> str:
    return f"{num:02d}"

pathlib

The double double back back whacks are distracting. Consider using the / other kind of slash.

    src_dir= "C:\\a\\dir\\Desktop"
    ...
    src_file = os.path.join(src_dir, old_file_name)

If you feel you need \ then please use a raw-string.

    src_dir= r"C:\a\dir\Desktop"

Better, take advantage of Path.

from pathlib import Path ...

    src_dir= Path(r"C:\a\dir\Desktop") ...
    src_file = src_dir / old_file_name

import details

import shutil
import os

It is helpful to the reader when you use isort to organize your imports. A setting of "float_to_top = true" would have lifted these imports to the top of the file, where they belong.

__main__ guard

As written, a maintenance engineer cannot tack on an automated test of a routine like format_num(). The top-level code should be buried within def main():, invoked with the customary

if __name__ == "__main__":
    main()

Then a unit test can safely import this code without unwanted side effects.

Answer 3

I think it bears pointing out that rolling your own date formatting code is not necessary here - the datetime you're working with is perfectly able to display itself in a format of your choosing using its strftime method, letting you go from

wb_Name = "workFeed" + str(curr_year) + format_Num(curr_month) + format_Num(day) + ".xlsx"

to the simpler

wb_Name = "workFeed" + now.strftime("%Y%m%d") + ".xslx"

or perhaps even to

wb_Name = now.strftime("workFeed%Y%m%d.xslx")

Either way format_Num is unnecessary.

Answer 4

Trying not to overlap too much with previous answers...

Layout

All the import lines should be at the top of the code

The functions should be after the import lines before the executable code.

Comments

The comments for the functions are helpful.. For example:

# checks whether a number is single digit or not
def format_Num(num):

But, the PEP 8 style guide recommends using docstrings instead:

def format_Num(num):
    """ Checks whether a number is single digit or not """

These comments should be deleted because they obviously become incorrect:

curr_year = now.year #2018
curr_month  = now.month #04

Naming

The PEP-8 guide recommends snake_case for function and variable names. For example:

format_Num
path_n_fileName

would be:

format_num
path_n_file_name

Format

Can I avoid the format_Num function somehow?

Yes, you can use f-strings with the :02d format specifier to format an integer with 2 digits and a leading 0 for integers less than 10. Change:

wb_Name = "workFeed" + str(curr_year) + format_Num(curr_month) + format_Num(day) + ".xlsx"

to:

wb_Name = f"workFeed{str(curr_year)}{curr_month:02d}{day:02d}.xlsx"

Lint check

ruff reports:

E712 Avoid inequality comparisons to `True`; use `if not os.path.isfile(path_n_fileName):` for false checks
 |     if os.path.isfile(path_n_fileName) != True:
 |        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ E712
   = help: Replace with `not os.path.isfile(path_n_fileName)`

Close

Do I need to .close() the file, since I didn't open it?

No, it is not needed.