CODEWITHSUNDEEP
pythonexcelrenameopenpyxlshutil
JasonDL
JasonDL

Reputation: 127

Python - read excel file into a list and rename files in a folder

We have an excel file with 2094 rows and 3 columns structured like so:

Employee Old ID | Employee Name | Employee New ID

007219 | John Doe | 001234

The end result being: John Doe 001234.jpg

We have a folder with employee photos labels by their Old IDs and we want to read the excel file and then copy and rename the photos with the new ID.

Issue with the code - it stops after it copies and renames the first photo. I presume I need to adjust the last for loop but I am drawing a blank on how to get it to iterate.

Notes: I tried to make the code flexible by including a file dialog folderSource. Additionally, I am new to Python so if you see ways to clean up the code by all means let me know, I added some questions in the comments in the code:

import openpyxl
import os
import shutil
from tkinter import *
from tkinter import filedialog

root = Tk()
root.withdraw()

# File with file name data
# Add the file name
file_names = openpyxl.load_workbook(filedialog.askopenfilename())
# Add the sheet name - can you make this more flexible? 
file_names_sheet = file_names['piclist2']  

# Select the source folder with files in it
folderSource = filedialog.askdirectory()

# New Folder Name - is there a filedialog way to flexibly create this?
folderDestination = 'SSL Photos Renamed'

# Takes: start cell, end cell, and sheet you want to copy from.
def copyRange(startCol, startRow, endCol, endRow, sheet):
    rangeSelected = []
    # Loops through selected Rows
    for i in range(startRow, endRow + 1, 1):
    # Appends the row to a RowSelected list
        rowSelected = []
        for j in range(startCol, endCol + 1, 1):
            rowSelected.append(sheet.cell(row=i, column=j).value)
    # Adds the RowSelected List and nests inside the rangeSelected
    rangeSelected.append(rowSelected)

return rangeSelected


def renameFiles():
    print('Processing...')

    # Make a folder for the files
    current_directory = os.getcwd()
    folder_n_path = os.path.join(current_directory, folderDestination)
    print("Files saved to: " + folder_n_path)
    try:
    newFolder = os.makedirs(folder_n_path)

except:
    print("Folder already exists")
    return

# Get the Data to make the file names
selectedRange = copyRange(1, 1, 2, 2, file_names_sheet)
print(selectedRange)

for i, filename in zip(selectedRange, os.listdir(folderSource)):
    print(filename)
    file_name = str(i[0]) + " " + i[1] + ".jpg"
    filename = os.path.join(folderSource, filename)
    file_name = os.path.join(folderDestination, file_name)
    shutil.copy(filename, file_name)
    print("Done")

go = renameFiles()

I believe the issue is in the last bit of code but I cannot figure out how to do the loop. Thoughts?

Upvotes: 0

Views: 3545

Answers (1)

d_kennetz
d_kennetz

Reputation: 5359

Try this for your last loop and let me know how it turns out, some modifications may need to be made since I can't see your data. It appears you want to run the for loop on the lists together, so try this:

for i, filename in zip(selectedRange, os.listdir(folderSource)):
    file_name = str(i[1]) + " " + i[2] + ".jpg"
    filename = os.path.join(folderSource, filename)
    file_name = os.path.join(folderDestination, file_name)
    shutil.copy(filename, file_name)
print(done)
go = renameFiles()

For nested for loop structures, consider the following:

loop1 = ['a','b','c','d','e']
loop2 = ['f','g','h','i','j']
for i in loop1: # iterates through a,b,c,d,e
    print(i) #prints a,b,c,d,e
    for j in loop2: # compares all j objects of loop2 to i in loop 1:
        ij = i + j
        print(ij) # this will add all j's to each i

snippet output will add all j's to each iteration of i, before moving to the next iteration of i:

'af','ag','ah','ai','aj','bf','bg','bh','bi',bj'... etc

zipping 2 lists together, (which is what I did in the answer) compares each element in loop1 to loop2 at the same index in both lists:

for i,j in zip(loop1,loop2):
    ij = i + j
    print(ij)

output:

'af','bg','ch','di','ej'

The only thing you need to consider when you use the zip function for 2 lists, is that the iteration will only occur until the end of the shortest list. So if loop1 and loop2 were not equal lengths, then i+j would stop after the shorter list was complete. I hope this clarifies some of what I have done.

Upvotes: 1

Related Questions