Python loop problem - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Python loop problem (/thread-27682.html) Pages:
1
2
|
Python loop problem - Kristenl2784 - Jun-16-2020 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') RE: Python loop problem - buran - Jun-17-2020 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. RE: Python loop problem - Kristenl2784 - Jun-17-2020 (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. RE: Python loop problem - buran - Jun-17-2020 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) RE: Python loop problem - Kristenl2784 - Jun-17-2020 (Jun-17-2020, 01:16 PM)buran Wrote: with some small changes 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 RE: Python loop problem - buran - Jun-17-2020 so what have you tried? It looks you just replace NNB part of the name with Report
RE: Python loop problem - Kristenl2784 - Jun-17-2020 (Jun-17-2020, 05:56 PM)buran Wrote: so what have you tried? It looks you just replace 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 RE: Python loop problem - buran - Jun-17-2020 >>> 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' RE: Python loop problem - Kristenl2784 - Jun-17-2020 (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? RE: Python loop problem - buran - Jun-17-2020 (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()
|