We help IT Professionals succeed at work.

Using Python to Insert Filename into Column

Wm Allen Smith
on

Using Python to Insert Filename into Column


I have  a folder full of two-column  excel files. Column A has  attributes, Column B has values. I want to loop through the folder and insert a row at the top of the worksheet ("Sheet1")
and enter the values "Filename" in column A and  the actual filename in Column B (without the full path, just the file name). I have tried  creating two data frames, using concat, but has not worked.  Please  advise.
Comment
Watch Question

Bhavesh ShahLead Analyst
CERTIFIED EXPERT
Top Expert 2010

Commented:
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:

You may try something like this...


import os
import openpyxl


folder_path = 'C:\Source\Files'     # Change the path of the source folder with excel files here

for file in os.listdir(folder_path):
    filepath = os.path.join(folder_path, file)
    filename = file
    wb = openpyxl.load_workbook(filepath)
    ws = wb['Sheet1']
    ws.insert_rows(1)
    ws['A1'].value = filepath
    ws['B1'].value = filename
    wb.save(filepath)


Author

Commented:
Bingo! exactly what I was looking for! thanks so much!
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! Glad it worked as desired.