How to Automate Excel and File Management with Python: Copy & Move Scripts

Introduction

In a recent Python community discussion, a user asked how to automate office tasks such as copying Excel files. Below is the original code they provided.

import os
import shutil
import glob

# Specify source and target directories
source_dir = r"D:\设计类工作资料"
target_dir = r"D:\xx"

# Get all Excel files in the source directory
excel_files = glob.glob(os.path.join(source_dir, "*.xlsx"))

# Copy each Excel file to the target directory
for file in excel_files:
    shutil.copyfile(file, os.path.join(target_dir, file))

# Copy library files as well
library_files = glob.glob(os.path.join(source_dir, "*.pyd"))
for file in library_files:
    shutil.copyfile(file, os.path.join(target_dir, file))

Solution

The following script walks through a directory tree, finds files ending with .xlsx or .xls, and moves them to a target folder. It can be easily adapted to handle PDFs or other file types.

import shutil
import os

def copy_file(path):
    # (root, dirs, files) are the current directory, sub‑directories, and files
    for root, dirs, files in os.walk(path):
        for file in files:
            if file.endswith('.xlsx') or file.endswith('.xls'):
                shutil.move(root + '\\' + file, target_path + '\\' + file)
                print(root + '\\' + file + ' copied -> ' + target_path)
        for dir_in in dirs:
            copy_file(dir_in)

if __name__ == '__main__':
    source_path = r'C:\Users\Desktop\test\收入确认表\sample'
    target_path = r'C:\Users\Desktop\test\收入确认表\收入确认表pdf文件'
    copy_file(source_path)

This approach is flexible; you can modify the file‑extension check or the destination logic to suit other automation scenarios.

Conclusion

The article demonstrates a practical Python solution for automating the copying and moving of Excel files, providing a reusable pattern for broader office‑automation tasks.