Python loop problem

[Kristenl2784]

Hello,

I'm trying to get this script to loop through 27 excel files (source), and paste them one by one to my template file, and then save to an output folder once finished I will have 27 new files inside my output folder.

Examples Source Files:
NNB-6a_v1_T01-Report.xlsx
NNB-6a_v1_T02-Report.xlsx
NNB-6a_v1_T03-Report.xlsx
NNB-6a_v1_P01-Report.xlsx
NNB-6a_v1_P02-Report.xlsx
NNB-6a_v1_P03-Report.xlsx

When I run this script it for some reason only grabs file 26, and paste it to my template, and saves it to my output folder. I don't know why id decides to start at file 26 and not a the first excel file.

import openpyxl as xl; 
import os

files = [file for file in os.listdir('C:/data') if os.path.isfile(file) and file.endswith('.xlsx')]


output = ('C:/data/output')



for file in files:
    wb=xl.load_workbook(file)
    ws=wb.worksheets[1]
    
# Open template
Template ="C:/data/Template.xlsx"
wb2 = xl.load_workbook(Template) 
ws2 = wb2.worksheets[2] 
  
# calculate total number of rows and  
# columns in source excel file 
mr = ws.max_row 
mc = ws.max_column 
  
# copying the cell values from source  
# excel file to destination excel file 
for i in range (1, mr + 1): 
    for j in range (1, mc + 1): 
        
# reading cell value from source excel file 
        c = ws.cell(row = i, column = j) 
# Cells for source data to pasted inside Template
        ws2.cell(row = i+12, column = j+1).value = c.value 
  
# saving the destination excel file 
wb2.save('./output/'+file+'.xlsx')

[buran]

lines 15 and next are outside the for loop. So it goes over all files in the loop and then only after it exit the loop you write to template and save the resulting file.

[Kristenl2784]

lines 15 and next are outside the for loop. So it goes over all files in the loop and then only after it exit the loop you write to template and save the resulting file.

Hello,

Can you show me what you mean? I'm not following how it should be set up.

---

lines 15 and next are outside the for loop. So it goes over all files in the loop and then only after it exit the loop you write to template and save the resulting file.

I figured out what you meant, and it is running correctly now. Thank you!

Do you know what I would need to add to this script if I only wanted to look at files 1-5, and then 7-10? Would I need to add some type of range loop? Sometimes instead of looking at all 27 files, I might want to only look at a couple of them.

[buran]

with some small changes

import openpyxl as xl; 
import os

input_dir = 'C:/data'
output_dir = os.path.join(input_dir, 'output') # make sure it exists
template = os.path.join(input_dir, 'Template.xlsx")

 
files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith('.xlsx')]
 
for file in files:
    input_file =  os.path.join(input_dir, file) # make the full path, so that it does not depend on input_dir and CWD being the same
    wb=xl.load_workbook(input_file)
    ws=wb.worksheets[1]
     
    # Open template
    wb2 = xl.load_workbook(template) 
    ws2 = wb2.worksheets[2] 
    
    # calculate total number of rows and  
    # columns in source excel file 
    mr = ws.max_row 
    mc = ws.max_column 
    
    # copying the cell values from source  
    # excel file to destination excel file 
    for i in range (1, mr + 1): 
        for j in range (1, mc + 1): 
            
    # reading cell value from source excel file 
            c = ws.cell(row = i, column = j) 
    # Cells for source data to pasted inside Template
            ws2.cell(row = i+12, column = j+1).value = c.value 
    
    # saving the destination excel file 
    output_file = os.path.join(output_dir, file)
    wb2.save(output_file)

[Kristenl2784]

with some small changes

import openpyxl as xl; 
import os

input_dir = 'C:/data'
output_dir = os.path.join(input_dir, 'output') # make sure it exists
template = os.path.join(input_dir, 'Template.xlsx")

 
files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith('.xlsx')]
 
for file in files:
    input_file =  os.path.join(input_dir, file) # make the full path, so that it does not depend on input_dir and CWD being the same
    wb=xl.load_workbook(input_file)
    ws=wb.worksheets[1]
     
    # Open template
    wb2 = xl.load_workbook(template) 
    ws2 = wb2.worksheets[2] 
    
    # calculate total number of rows and  
    # columns in source excel file 
    mr = ws.max_row 
    mc = ws.max_column 
    
    # copying the cell values from source  
    # excel file to destination excel file 
    for i in range (1, mr + 1): 
        for j in range (1, mc + 1): 
            
    # reading cell value from source excel file 
            c = ws.cell(row = i, column = j) 
    # Cells for source data to pasted inside Template
            ws2.cell(row = i+12, column = j+1).value = c.value 
    
    # saving the destination excel file 
    output_file = os.path.join(output_dir, file)
    wb2.save(output_file)

I'm trying to figure out a way to save the files, as something different.

Example Save as:

Report_6a_v1_Regular.xlsx
Report_6a_v1_T01.xlsx
Report_6a_v1_T02.xlsx
Report_6a_v1_T03.xlsx
Report_6a_v1_P01.xlsx
Report_6a_v1_P02.xlsx
Report_6a_v1_P03.xlsx

[buran]

so what have you tried? It looks you just replace NNB part of the name with Report

[Kristenl2784]

so what have you tried? It looks you just replace NNB part of the name with Report

This is the full name of the source file:

NNB-6a_v1_Normal-Complete Report.xlsx
NNB-6a_v1_T01-Complete Report.xlsx

This is what I want it to save as:


Report-6a_v1_Normal.xlsx
Report-6a_v1_T01.xlsx

[buran]

>>> name = 'NNB-6a_v1_T01-Report.xlsx'
>>> name.replace('-Report', '').replace('NNB', 'Report')
'Report-6a_v1_T01.xlsx'

>>> name = 'NNB-6a_v1_Normal-Complete Report.xlsx'
>>> name = name.split('-')[1]
>>> f'Report-{name}.xlsx'
'Report-6a_v1_Normal.xlsx'

[Kristenl2784]

>>> name = 'NNB-6a_v1_T01-Report.xlsx'
>>> name.replace('-Report', '').replace('NNB', 'Report')
'Report-6a_v1_T01.xlsx'

>>> name = 'NNB-6a_v1_Normal-Complete Report.xlsx'
>>> name = name.split('-')[1]
>>> f'Report-{name}.xlsx'
'Report-6a_v1_Normal.xlsx'

Will I need to change the name = NNB-6a_v1_T01 Report manually so that it goes through all of the files?

[buran]

Will I need to change the name = NNB-6a_v1_T01 Report manually so that it goes through all of the files?

it's in the loop - file variable is exactly that. you need to make transformations and get what you want the new name to be, then use it to create output_file variable used in save()