Creating Folders - From M.S. Excel

Overview

This example shows how to create a list of folders from a list foldernames stored in an Excel file, using the Python library, openpyxl.

As with all Scripts, the particulars shown below can be modified to suit your needs.

Python Script

The following shows the full script

import os, openpyxl
from pathlib import Path

# Specify Input and Output Locations
working_directory = Path.cwd()
excel_file = Path('Excel_File.xlsx')
output_folder = Path('Folders')
output_folder_path = working_directory / output_folder
data = str(excel_file)

workbook = openpyxl.load_workbook(data)
sheet = workbook['Sheet1']

# Create Folders
column_values = [cell.value for col in sheet.iter_cols(
    min_row = 3, max_row = None, min_col = 4, max_col = 4) for cell in col]

for value in column_values:
    folderName = value
    fullfolderName = os.path.join(str(output_folder_path), str(folderName))
    isDir = os.path.isdir(fullfolderName) 

    # Only Create Folder, if it doesn't already exist
    if not(isDir):
        os.makedirs(os.path.join(fullfolderName))
        print("Created folder: ", folderName)
    else:
        print("The folder:", folderName, "already exists")

How to Use it

• The input file is called Excel_File.xlsx. This has a list of folder names in a particular column

• The script can be given any meaningful name but should have a file extension .py to tell the operating system it is a Python Script

• The newly created folders are generated in the output folder called Folders. This folder should sit along side the input Excel file and python script, within the same working directory

• In the Excel file, the folder names are listed in the sheet called Sheet1 - but this can be tailored to your own needs

• The folder names start from row 3 (which is why the min_row variable above has been set to 3)

• The folder names are listed in column D (which is why the min_col, and max_col variables above have been set to 4)

• The script can be run easily from any Python compiler on a Mac or Windows computer (Visual Studio Code is recommended!)

• The script is super fast! It can do 2-3 days of folder creating work in about 5 seconds!

How it Works

The script works by:

• Opening the Excel file (once it knows where it is)
• It then goes to the relevant sheet, row and column to where the list of folder names are
• It then goes through an iteration loop, extracting the raw value of each cell in the list, and creating a new folder for each one
• It then check whether the new folder already exists in the output folder. If it doesn’t exist, it will create a new folder in the output folder, otherwise it will skip onto the next cell value in the Excel list
• When it (the code!) gets to the end of the list, it will create one final folder called “None” - and then finally stop

That’s it! Enjoy!