Automate Folder File Listings to Excel with Python and pandas

Introduction

The author shares a Python automation task that extracts file names from a directory and exports them to an Excel workbook using pandas.

Implementation

The following script demonstrates the process:

import os
import datetime
import pandas as pd

# Folder path
folder_path = r"<your_folder_path>"

# Get list of file names in the folder
file_names = os.listdir(folder_path)
print(file_names)

# Create a DataFrame containing the file names
df = pd.DataFrame(file_names, columns=["File Name"])

# Save as an Excel file
os.chdir(r"<your_save_path>")
route = os.getcwd()
print(route)

nowTime = datetime.datetime.now()
Update_Time = nowTime.strftime('%Y-%m-%d-%H-%M-%S')
title = "DATA-" + Update_Time + ".xlsx"
print(title)

# Write data to the Excel file
writer = pd.ExcelWriter(title)
df.to_excel(writer, encoding="utf_8_sig", sheet_name="DATA_Total", index=False)
writer.save()

The code, originally generated by ChatGPT, efficiently solves the problem. An additional refinement was later suggested to improve the workflow.

Conclusion

The article demonstrates a practical Python automation solution for handling file listings, highlighting pandas' convenience for data conversion without the need for complex styling offered by libraries like openpyxl.