Need Help With a Script

[SunWers]

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

Larz60+ write Jan-05-2021, 05:07 PM:
Please add post each time you have a change, rather than edit original post.
It's important, for diagnosis, to be able to see code as it develops.

[SunWers]

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

Error:
runfile('C:/Users/vhubbard/Documents/Python Scripts/temp.py', wdir='C:/Users/vhubbard/Documents/Python Scripts')
Traceback (most recent call last):

  File "C:\Users\vhubbard\Documents\Python Scripts\temp.py", line 24, in <module>
    for y in ws2['C4']:

TypeError: 'Cell' object is not iterable

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

[buran]

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.

[SunWers]

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.

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.

[buran]

How do you expect to iterate over single cell object, that does not support iteration?

[SunWers]

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?

[SunWers]

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