CODEWITHSUNDEEP
pythonexcelloopsrow
Kaiyuan Zheng
Kaiyuan Zheng

Reputation: 33

Extract row one by one from excel table, and save each extracted row individually as new excel file using loop

I have one header excel file (header.xlsx) with only one row, and one excel table file (station.xlsx) with multiple rows.

The aim is to extract first row from (station.xlsx), and then append the extracted first row to (header.xlsx), and then save them as new excel file called (1.xlsx).

After that, I want to extract second row from (station.xlsx), and then append the extracted second row to (header.xlsx), and then save them as (2.xlsx).

After that, I want to extract third row from (station.xlsx), and then append the extracted third row to (header.xlsx), and then save them as (3.xlsx)...

The loops will be continued until 167 rows from (station.xlsx) are extracted and append and save as new excel file individually.

# import module
import os
import openpyxl 
# load excel with its path 
wrkbk1 = openpyxl.load_workbook(r"C:\Users\kxz237\Desktop\test\header.xlsx") 
sh1 = wrkbk1.active
wrkbk2 = openpyxl.load_workbook(r"C:\Users\kxz237\Desktop\test\stations.xlsx") 
sh2 = wrkbk2.active

# iterate through excel and display data 
for i in range(1, sh2.max_row+1): 
    output_file = r'C:\Users\kxz237\Desktop\test\i.xlsx'
    sh1.append(sh2.row_values(i))
    wrkbk1.save(output_file)

AttributeError: 'Worksheet' object has no attribute 'row_values'

Could you help me fix the python code?

My original excel files are attached.enter link description here

Upvotes: 0

Views: 50

Answers (1)

yellow_dot
yellow_dot

Reputation: 176

It's easy with pandas:

import pandas as pd

header = pd.read_excel(r"test\header.xlsx") 
df = pd.read_excel(r"test\stations.xlsx", header=None) 
 
for i in df.index:
    df.loc[i,:].to_frame().T.to_excel(f'test/{i}.xlsx')

Upvotes: 0

Related Questions