Python Forum
Need to copy column of cell values from one workbook to another with openpyxl
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need to copy column of cell values from one workbook to another with openpyxl
#1
I am extracting data from one workbook's column and need to copy the data to another existing workbook.

This is how I extract the data (works fine):

wb2 = load_workbook('C:\\folder\\AllSitesOpen2.xlsx')
ws2 = wb2['report1570826222449']
#Extract column A from Open Sites
DateColumnA = []
for row in ws2.iter_rows(min_row=16, max_row=None, min_col=1, max_col=1):
    for cell in row:
        DateColumnA.append(cell.value)
DateColumnA
The above code successfully outputs the cell values in each row of the first column to DateColumnA.

I'd like to paste the values stored in DateColumnA to this existing destination workbook:

#file to be pasted into
wb3 = load_workbook('C:\\folder\\output.xlsx')
ws3 = wb3['Sheet1']
But I am missing a piece conceptually here. I can't connect the dots. Can someone advise how I can get this data from my source workbook to the new destination workbook? Thanks!
Reply
#2
Copy and paste Excel ranges
Reply
#3
Thanks I saw that article but it didn't use iter_rows and iter_cols, which I was determined to use. Anyway, I figured out this solution:

for x, rows in enumerate(DateColumnA):
    ws3.cell(row=x+1, column=1).value = rows
    #print(rows)
    wb3.save('C:\\folder\\output.xlsx')
Works great but now I need to determine how to write the data to output.xlsx starting at row 16 instead of row 1 so I don't overwrite the first 16 existing header rows in output.xlsx. Any ideas appreciated.
Reply
#4
Figured out how to write the source data to a different starting row on destination sheet. I do not need to dump the values in to a list as I did above:

row_offset=5
for rows in ws2.iter_rows(min_row=2, max_row=None, min_col=1, max_col=1):
    for cell in rows:
        ws3.cell(row=cell.row + row_offset, column=1, value=cell.value)
        wb3.save('C:\\folder1\\DestFile.xlsx') 
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Get an average of the unique values of a column with group by condition and assign it klllmmm 0 220 Feb-17-2024, 05:53 PM
Last Post: klllmmm
  Converting column of values into muliple columns of counts highland44 0 205 Feb-01-2024, 12:48 AM
Last Post: highland44
  How to copy work sheet data one workbook to other? sayyedkamran 2 645 Nov-03-2023, 09:10 AM
Last Post: Larz60+
  xlwings error when reading a workbook Mishal0488 1 1,036 Aug-01-2023, 02:05 AM
Last Post: deanhystad
  PowerBI: Using Python Regex to look for values in column MarcusR44 1 920 Oct-14-2022, 01:03 PM
Last Post: ibreeden
  How to combine multiple column values into 1? cubangt 15 2,626 Aug-11-2022, 08:25 PM
Last Post: cubangt
  Help in opening and editing an excel workbook from a web-browser test 4 1,357 Aug-10-2022, 02:31 PM
Last Post: test
  Copy a column from one dataframe to another dataframe Led_Zeppelin 17 10,985 Jul-08-2022, 08:40 PM
Last Post: deanhystad
  Creating a numpy array from specific values of a spreadsheet column JulianZ 0 1,076 Apr-19-2022, 07:36 AM
Last Post: JulianZ
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,570 Mar-30-2022, 11:05 PM
Last Post: Pedroski55

Forum Jump:

User Panel Messages

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