Saving Files

[Kristenl2784]

Hello,

Right now when this script saves a file it uses the summaryFile variable 'Report_v1_' and then adds the first schedule name 'Nm' to the end so it saves as Report_v1_Nm, and then it loops around and saves the next as Report_v1_S01.

How would I make it so that if I wanted to start saving at 'S01' I can, instead of 'Nm'? If I change the schedule_index from 0 to 1, it does what I want but, it takes the first report in the input dir which is finalstats_nm, and saves it as Report_v1_S01 so it has "Nm" data saved under S01 which is not right. If I want to start at S01, I want it to grab the finalstats_S01 file from the input_dir, and save as Report_v1_S01.

import openpyxl as xl; 
import os
 
input_dir = 'C:\\Python\\Reports Combined'
output_dir = 'C:\\Python\\Reports Combined\\output'
template = 'C:\\Python\\Template.xlsx'
summaryFile = 'Report_v1_'

schedule_index = 0
schedules=['Nm', 'S01', 'S02','S03','S04','S05',S06']
  
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)
    wb=xl.load_workbook(input_file)
    ws=wb.worksheets[1]
      
    # Open template
    wb2 = xl.load_workbook(template) 
    ws2 = wb2.worksheets[1] 
     
    # 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+8, column = j+1).value = c.value 
     
    # saving the destination excel file 
	
    output_file =os.path.join (output_dir, f"{summaryFile}_{schedules[schedule_index]}.xlsx")
    schedule_index += 1
    wb2.save(output_file)

[bowlofred]

I'm not sure what you're asking. It starts with 'Nm' because that's the first element in the schedules list on line 10. If you remove the 'Nm' element, it will instead start with 'S01'. Is that what you want?

Also, there appears to be a problem with that line because the element doesn't have quote marks on both sides.

[Kristenl2784]

I'm not sure what you're asking. It starts with 'Nm' because that's the first element in the schedules list on line 10. If you remove the 'Nm' element, it will instead start with 'S01'. Is that what you want?

Also, there appears to be a problem with that line because the element doesn't have quote marks on both sides.

I explained this poorly. How would I make it so that the for loop file in files is set to a range? Like maybe I want the script to start off on file 3 in my input_dir, instead of starting at the first file?

[bowlofred]

The problem is that instead of looping over some particular names, it's looping over the files in the directory, and those names are (currently) in that particular order. So if the filenames ever changed, this would break.

Better would be to have the two synchronized to the same list. Only open the files that match the names.

As a hack, you could continue if the index equal zero. But the current structure is brittle and depends on the directory entries matching a list in the code.