![]() |
Need Help With a Script - 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: Need Help With a Script (/thread-31814.html) |
Need Help With a Script - SunWers - Jan-05-2021 Hi, I'm still working on trying to get a script together for importing data to my cutter reports. Seems like I've had this script a million different ways... I decided to parse the .csv file manually since the date and time gets exported together from the cutters. I've been opening the .csv file from Excel and modifying the date and time with the separator line to indicate between the two. Here is how far I've gotten and information about the script. Source is ImportFile.xlsx that contains all the data from the cutters that I need to move to the destination file. This file has other columns that I do not need to copy over. These rows of data will contain cut data for dates Monday through Friday. This file only has one tab. Destination is Weekly-temp.xlsx that contains a blank form that the data needs to be imported in by corresponding columns. Tab used to import is the first tab and the title is 'Cutter A - Marker Report'. Columns that need to match from ImportFile to Weekly-temp are (Cutfile Name, Start Time, End Time, Total Time, Cut, Dry Haul, Sharpen, Bite, Interrupt, Processing, Idle, and Dry Run) and the data will start on row 8. Columns go straight across in order of above but skips column G, H, and I. I'm thinking data needs to be pulled by the date on the Weekly-temp and match up with the ImportFile. On the Weekly-temp, it has 5 sections each one with the day of the week and the date at the top of each section. Weekly-temp has 50 rows and normally this is all I need per day. Here is the script I have so far... I can get the importfile data copied over to the weekly-temp but it is pulling all information over and some are in the wrong columns. Any help would be greatly appreciated and if there is a different way I should be doing this, please advise. # Importing openpyxl module import openpyxl as xl; # Opening the source excel file filename = "ImportFile.xlsx" wb1 = xl.load_workbook(filename) ws1 = wb1.worksheets[0] # Opening the destination excel file filename1 = "Weekly-temp.xlsx" wb2 = xl.load_workbook(filename1) ws2 = wb2.active # Calculate total number of rows and columns in # source excel file mr = ws1.max_row mc = ws1.max_column # Copying the cell values from source excel file # destination excel file for i in range (1, mc + 1): for j in range (1, mc + 1): # reading cell value from source excel file c = ws1.cell(row = i, column = j) # writing the read value to destination excel file ws2.cell(row = i, column = j).value = c.value # Saving the destination excel file wb2.save(str(filename1))Thanks, Veronica RE: Need Help With a Script - SunWers - Jan-05-2021 OK.... Update I've changed my script once again. Here is what I have now: import openpyxl wb1 = openpyxl.load_workbook(r'ImportFile1.xlsx') wb2 = openpyxl.load_workbook('Weekly-temp1.xlsx') ws1 = wb1['Import'] # Raw data from cutters ws2 = wb2['Cutter A - Marker Report'] # Report for Raw data to be imported ws3 = wb2.create_sheet(title='Results') for x in ws1['B2:B100']: for y in ws2['C4']: if ws1['B2:B100'] == ws2['C4']: ws3.write_row(row=8).value wb2.save('Weekly-temp1.xlsx')But when I run this file it gives me the following error What would I be doing wrong? Note: Right now I'm adding a new sheet ('Results') to the Weekly-temp just to see if I can get it to import over. Eventually I want this data to go into the correct columns/rows of the Cutter A - Marker tab of the Weekly-temp.xlsx. Thanks, Veronica RE: Need Help With a Script - buran - Jan-05-2021 (Jan-05-2021, 05:49 PM)SunWers Wrote: What would I be doing wrong?Isn't it obvious from the traceback? ws2['C4'] is a cell object and you try to iterate over it, which is not possible.
RE: Need Help With a Script - SunWers - Jan-05-2021 (Jan-05-2021, 06:34 PM)buran Wrote:(Jan-05-2021, 05:49 PM)SunWers Wrote: What would I be doing wrong?Isn't it obvious from the traceback? I must be misunderstanding somewhere... I'm trying to filter through cells B2-B100 (Dates) on ws1 for matches for C4 (Date) on ws2. Once it finds the matches, then I wanted it to write the data from each row to ws3 starting at row 8. For the TypeError: "object is not iterable", does this mean it cannot be called? If so, is it where I created the data through a different script earlier and saved it to this excel file? So Sorry... I'm trying to understand. RE: Need Help With a Script - buran - Jan-05-2021 How do you expect to iterate over single cell object, that does not support iteration? RE: Need Help With a Script - SunWers - Jan-05-2021 (Jan-05-2021, 09:00 PM)buran Wrote: How do you expect to iterate over single cell object, that does not support iteration? Oh I think I understand... if I had more than one cell in ws2 the script would work? So do I need a completely different script? RE: Need Help With a Script - SunWers - Jan-07-2021 I think I'm finally getting closer to what I'm trying to achieve. As mentioned above I have two worksheets, one is an import file and the other is a blank report to add the import data. This is the script I have now. from openpyxl import load_workbook wb1 = load_workbook('Cutter A - Import.xlsx') wb2 = load_workbook('Weekly-temp1.xlsx') sheet1 = wb1.get_sheet_by_name('Cutter A - ImportFilev1') sheet2 = wb2.get_sheet_by_name('Cutter A Import') for i in range(1,sheet1.max_row+1): for j in range(1,sheet1.max_column+1): sheet2.cell(row=i,column=j).value = sheet1.cell(row=i,column=j).value wb2.save('Weekly-temp1.xlsx') Only problem I have now is that it's pulling all data from the import sheet. I need all rows but I want specific columns. I'm thinking I need to change the, "for j in range (1, sheet1.max_column+1)" to look for those specific columns. I'm not sure how to do that.... The columns I need is A:F and O:V. How would I do this? Thanks, Veronica |