Python Forum
Dynamically Copy and paste column
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Dynamically Copy and paste column
#1
Hello all,
I am new to the forum and python I am seeking some assistance as I have searched and am not able to find the answer I need. What I am doing is coping a certain column and pasting it into another workbook right now I am specifying how many rows to copy and paste as you can see below in copyrange and pasterange. Is there a way in python to copy and paste based on the amount of rows with data? Ex: column A has data from A2:A10 but next weeks file has data from A2:A14 I have my code below I am using to copy and paste. Any help is appreciated!

# File to be copied
wb = openpyxl.load_workbook(
    "C:/Users/u678153/Desktop/Account_Detail.100219-added MI_210 vFINAL.xlsx")  # Add file name
sheet = wb["Sheet1"]  # Add Sheet name

# File to be pasted into
template = openpyxl.load_workbook("C:/Users/u678153/Desktop/07_19 ITS Backup current.xlsx")  # Add file name
temp_sheet = template["Download"]  # Add Sheet name


# Copy range of cells as a nested list
# Takes: start cell, end cell, and sheet you want to copy from.
def copyRange(startCol, startRow, endCol, endRow, sheet):
    rangeSelected = []
    # Loops through selected Rows
    for i in range(startRow, endRow + 1, 1):
        # Appends the row to a RowSelected list
        rowSelected = []
        for j in range(startCol, endCol + 1, 1):
            rowSelected.append(sheet.cell(row=i, column=j).value)
        # Adds the RowSelected List and nests inside the rangeSelected
        rangeSelected.append(rowSelected)

    return rangeSelected


# Paste range
# Paste data from copyRange into template sheet
def pasteRange(startCol, startRow, endCol, endRow, sheetReceiving, copiedData):
    countRow = 0
    for i in range(startRow, endRow + 1, 1):
        countCol = 0
        for j in range(startCol, endCol + 1, 1):
            sheetReceiving.cell(row=i, column=j).value = copiedData[countRow][countCol]
            countCol += 1
        countRow += 1


print("Processing...")
selectedRange = copyRange(6, 3, 6, 26884, sheet)  # Change the 4 number values
pasteRange(1, 3, 1, 26884, temp_sheet, selectedRange)
# You can save the template as another file to create a new file here too.s
template.save("C:/Users/u678153/Desktop/07_19 ITS Backup current.xlsx")
print("Range copied and pasted!")
Reply
#2
Try if using calculating row and column count at source book itself and pass them as args for copy-paste helps.

import openpyxl


# File to be copied
wb = openpyxl.load_workbook(
    "Book1.xlsx")  # Add file name
sheet = wb["Book1"]  # Add Sheet name
 
# File to be pasted into
template = openpyxl.load_workbook("Book2.xlsx")  # Add file name
temp_sheet = template["Book2"]  # Add Sheet name

#rows count
r_count=0
colummn=sheet['A']
r_count=len(colummn)
#print(len(colummn))
print('after--->', r_count)

#column count
c_count=0  
for i in sheet.iter_rows(max_row=0):
	#print(len(i))
	c_count=len(i)
	break

print('after--->', c_count)

# Copy range of cells as a nested list
# Takes: start cell, end cell, and sheet you want to copy from.
def copyRange(startCol, startRow, endCol, endRow, sheet):
    rangeSelected = []
    # Loops through selected Rows
    for i in range(startRow, endRow + 1, 1):
        # Appends the row to a RowSelected list
        rowSelected = []
        for j in range(startCol, endCol + 1, 1):
            rowSelected.append(sheet.cell(row=i, column=j).value)
        # Adds the RowSelected List and nests inside the rangeSelected
        rangeSelected.append(rowSelected)
 
    return rangeSelected
 
 
# Paste range
# Paste data from copyRange into template sheet
def pasteRange(startCol, startRow, endCol, endRow, sheetReceiving, copiedData):
    countRow = 0
    for i in range(startRow, endRow + 1, 1):
        countCol = 0
        for j in range(startCol, endCol + 1, 1):
            sheetReceiving.cell(row=i, column=j).value = copiedData[countRow][countCol]
            countCol += 1
        countRow += 1
 
print("Processing...")

selectedRange = copyRange(1, 1, c_count, r_count, sheet) 
pasteRange(1, 1, c_count, r_count, temp_sheet, selectedRange)
template.save("Book2.xlsx")
print("Range copied and pasted!")
Best Regards,
Sandeep

GANGA SANDEEP KUMAR

If you like to append on target sheet with only new added rows,

import openpyxl


# File to be copied
wb = openpyxl.load_workbook(
    "Book1.xlsx")  # Add file name
sheet = wb["Book1"]  # Add Sheet name
 
# File to be pasted into
template = openpyxl.load_workbook("Book2.xlsx")  # Add file name
temp_sheet = template["Book2"]  # Add Sheet name

#rows count
s_r_count=0
colummn=sheet['A']
s_r_count=len(colummn)
#print(len(colummn))
print('source row--->', s_r_count)

#column count
s_c_count=0  
for i in sheet.iter_rows(max_row=0):
	#print(len(i))
	s_c_count=len(i)
	break

print('source column--->', s_c_count)

#rows count
t_r_count=0
colummn=temp_sheet['A']
t_r_count=len(colummn)
#print(len(colummn))
print('target row--->', t_r_count)

#column count
t_c_count=0  
for i in temp_sheet.iter_rows(max_row=0):
	#print(len(i))
	t_c_count=len(i)
	break

print('target column--->', t_c_count)

# Copy range of cells as a nested list
# Takes: start cell, end cell, and sheet you want to copy from.
def copyRange(startCol, startRow, endCol, endRow, sheet):
    rangeSelected = []
    # Loops through selected Rows
    for i in range(startRow, endRow + 1, 1):
        # Appends the row to a RowSelected list
        rowSelected = []
        for j in range(startCol, endCol + 1, 1):
            rowSelected.append(sheet.cell(row=i, column=j).value)
        # Adds the RowSelected List and nests inside the rangeSelected
        rangeSelected.append(rowSelected)
 
    return rangeSelected
  
# Paste range
# Paste data from copyRange into template sheet
def pasteRange(startCol, startRow, endCol, endRow, sheetReceiving, copiedData):
    countRow = 0
    for i in range(startRow, endRow + 1, 1):
        countCol = 0
        for j in range(startCol, endCol + 1, 1):
            sheetReceiving.cell(row=i, column=j).value = copiedData[countRow][countCol]
            countCol += 1
        countRow += 1
  
print("Processing...")

selectedRange = copyRange(1, t_r_count, 3, s_r_count, sheet)  # Change the 4 number values
pasteRange(1,t_r_count, 3, s_r_count, temp_sheet, selectedRange)
template.save("Book2.xlsx")
print("Range copied and pasted!")
Best Regards,
Sandeep

GANGA SANDEEP KUMAR
Reply
#3
Your best bet for doing this sort of operation is to use pandas.
I don't use it myself enough to be of great assistance, but the basics can be learned in a very short amount of time, and you can do a lot of clever work.
I'd recommend, to start: https://www.youtube.com/results?search_q...+tutorials
Reply
#4
(Dec-12-2019, 11:13 AM)Larz60+ Wrote: Your best bet for doing this sort of operation is to use pandas.
I don't use it myself enough to be of great assistance, but the basics can be learned in a very short amount of time, and you can do a lot of clever work.
I'd recommend, to start: https://www.youtube.com/results?search_q...+tutorials

Thank you, I have been using Pandas however the reason I am not with this workbook is because it has a pivot table embedded in the middle of the data and when I pull it into a df it messes the whole thing up.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy Paste excel files based on the first letters of the file name Viento 2 346 Feb-07-2024, 12:24 PM
Last Post: Viento
  What script to paste folders thenewcoder 1 637 Nov-29-2023, 09:40 AM
Last Post: Pedroski55
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,923 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  Please help me [copy and paste file from src to dst] midomarc 2 961 Nov-24-2022, 10:13 PM
Last Post: midomarc
  Copy a column from one dataframe to another dataframe Led_Zeppelin 17 10,984 Jul-08-2022, 08:40 PM
Last Post: deanhystad
  ImageTk Paste KDog 14 6,744 Jun-27-2021, 11:07 AM
Last Post: KDog
  Copy column from one existing excel file to another file mkujawsk 0 5,486 Apr-14-2021, 06:33 PM
Last Post: mkujawsk
  Cut and Paste Oshadha 3 2,386 Jan-20-2021, 04:27 PM
Last Post: spaceraiders
  Python Cut/Copy paste file from folder to another folder rdDrp 4 4,944 Aug-19-2020, 12:40 PM
Last Post: rdDrp
  openpyxl - How can I copy some row from an excel file and paste them in another one? aquerci 2 17,292 Jun-07-2020, 04:26 AM
Last Post: buran

Forum Jump:

User Panel Messages

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