Python – Getting an Excel List of all Folders and Files in them

Getting a list of all folders within a given folder is actually a single line command. Writing it to Excel is usually another line. Getting all files, within these folders is not a lot as well.

However, having a tool that actually does all these is a bit of coding. Hence, I have decided to do it and even to make a YouTube video about it.

So, this is what the code does:

1. Collects Folder Paths and Names: The code identifies all folders within a given directory (path) and creates lists of their paths and names. If include_root is set to True, the root directory itself is also included in these lists.
2. Builds a File Dictionary: It then creates a dictionary (file_dict) where each folder name is a key, and the values are lists of file names in each corresponding folder. If full_name is False, only the file names (without extensions) are stored.
3. Pads File Lists for Uniform Length: To ensure all folders have the same number of entries in the dictionary, the code finds the maximum number of files in any folder and pads shorter lists with empty strings.
4. Creates a DataFrame: The code converts the dictionary into a Pandas DataFrame, with each folder name as a column and each row representing files (or empty entries) from each folder.
5. Saves to Excel: Finally, the DataFrame is saved as an Excel file named files_in_all_folders.xlsx, and the path to this Excel file is returned.

As simple as that. It is even present in YouTube here:

The code is below:

import os
import pandas as pd
import platform
import subprocess

def list_files_in_all_folders(path='.', full_name=True, include_root=False):    

    # Get all folder names from path
    folder_paths = [os.path.join(path, folder) for folder in os.listdir(path) if os.path.isdir(os.path.join(path, folder))]
    folder_names = [folder for folder in os.listdir(path) if os.path.isdir(os.path.join(path, folder))]
    
    if include_root:
        folder_paths.insert(0, path)
        folder_names.insert(0, "root")
    
    # Dictionary to store file lists for each folder
    file_dict = {}
    
    # Populate the dictionary with file names from each folder
    for folder_path, folder_name in zip(folder_paths, folder_names):
        file_list = os.listdir(folder_path) if os.path.exists(folder_path) else []
        # Modify list based on `full_name` parameter
        if not full_name:
            file_list = [os.path.splitext(file)[0] for file in file_list]
        file_dict[folder_name] = file_list

    # Determine the maximum number of files in any folder to pad columns
    max_len = max(len(files) for files in file_dict.values()) if file_dict else 0
    for key in file_dict.keys():
        file_dict[key].extend([""] * (max_len - len(file_dict[key])))
    
    # Create DataFrame from the dictionary
    df = pd.DataFrame(file_dict)
    
    # Save to Excel
    output_path = 'files_in_all_folders.xlsx'
    df.to_excel(output_path, index=False)

    return output_path

def open_file(output_path):    
    if platform.system() == 'Windows':
        os.startfile(output_path)
        print(f'A file is opened -> {output_path}')
    else:
        print("Not Windows!")
        
output_path = list_files_in_all_folders(full_name=False, include_root=True)
open_file(output_path)

Enjoy it! 🙂