Copying a specific column from one existing excel file to another file in python

Adil Abid 0
Viewed 67

I am successfully copying the whole columns from one existing excel file to another file in python, but cannot copy a specific column from an existing excel file and writing it into another.

Here is my code

wb = load_workbook('Ambulance2Centroids_16622.xlsx')
wb2 = load_workbook('test.xlsx')

sheet1 = wb.get_sheet_by_name('Sheet1')
sheet2 = wb2.get_sheet_by_name('Details')

for i in range (1, 10):
    for j in range (1, sheet1.max_column+1):
        sheet2.cell(row=i, column=j).value = sheet1.cell(row=i, column=j).value
wb.save('Ambulance2Centroids_16622.xlsx')
wb2.save('test.xlsx')

enter image description here

Here, i am trying to get FROM_ID only.

jupyter-notebook openpyxl pandas python
1 Answers

A couple of things to note:
The get_sheet_by_name attribute is depreciated you should just use wb[<sheetname>] as shown below.
There is no need to save a workbook (wb) that you have not changed. Since you are only reading data from 'Ambulance2Centroids_16622.xlsx' to copy to 'test.xlsx' there are no changes to that wb and no need to save it.
The example below shows how to find the column in the original wb, in this case 'FROM_ID' and then copy the column to the destination wb 'test.xlsx'.

from openpyxl import load_workbook

wb = load_workbook('Ambulance2Centroids_16622.xlsx')
wb2 = load_workbook('test.xlsx')

# Use wb[<sheetname>] to assign sheets to variable 
sheet1 = wb['Sheet1']
sheet2 = wb2['Details']

search_text = 'FROM_ID'

for header_row in sheet1[1]:  # sheet1[1] means iterate row 1, header row
    if header_row.value == search_text:
        # Use the column letter of the found column to iterate the originating column and copy the cell value
        for dest_row, orig_col_c in enumerate(sheet1[header_row.column_letter], 1):  
            # Copying the originating cell value to column A (1) in destination wb  
            sheet2.cell(row=dest_row, column=1).value = orig_col_c.value

# Save test.xlsx only
# wb.save('Ambulance2Centroids_16622.xlsx')
wb2.save('test.xlsx')
moken0
Related