Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python loop problem
#1
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')
Reply
#2
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.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
(Jun-17-2020, 04:09 AM)buran Wrote: 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.

(Jun-17-2020, 04:09 AM)buran Wrote: 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.
Reply
#4
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)
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#5
(Jun-17-2020, 01:16 PM)buran Wrote: 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
Reply
#6
so what have you tried? It looks you just replace NNB part of the name with Report
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#7
(Jun-17-2020, 05:56 PM)buran Wrote: 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
Reply
#8
>>> 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'
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#9
(Jun-17-2020, 07:05 PM)buran Wrote:
>>> 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?
Reply
#10
(Jun-17-2020, 07:25 PM)Kristenl2784 Wrote: 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()
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  While Loop Problem Benno2805 1 537 Sep-06-2023, 04:51 PM
Last Post: deanhystad
  Loop reading csv file problem faustineaiden 1 1,539 Dec-11-2021, 08:40 AM
Last Post: ibreeden
  Infinite loop problem Zirconyl 5 2,923 Nov-16-2020, 09:06 AM
Last Post: DeaD_EyE
  Dataframe mean calculation problem: do we have to loop? sparkt 1 2,134 Aug-28-2020, 02:41 PM
Last Post: sparkt
  Problem with append list in loop michaelko03 0 1,638 Feb-16-2020, 07:04 PM
Last Post: michaelko03
  problem with for loop using integers python_germ 5 2,942 Aug-31-2019, 11:42 AM
Last Post: jefsummers
  problem in loop roseojha 3 2,238 Aug-26-2019, 09:03 AM
Last Post: perfringo
  Nested while loop problem + turtle DreamingInsanity 3 2,906 Jul-06-2019, 02:01 PM
Last Post: DreamingInsanity
  Problem Passing Arguement to do loop stephenmolnar 10 4,744 May-13-2019, 02:56 PM
Last Post: Gribouillis
  Nested for loop strange problem mcva 2 2,570 Mar-16-2019, 12:53 PM
Last Post: mcva

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020