Get data from multiple sheets in excel using unique ID and Save to CSV using python-CodePudding

I have been working with a excel book with multiple sheets. Example i have students list with Unique number for each student and multiple subjects undertaken by the said student and their attendance as shown below:
sheet number 1 - 18CS45
| USN | Name | Attended | Total | Percent | |------------|-------|----------|-------|---------| | 1US22CS001 | Johny | 10 | 10 | 100 | | 1US22CS002 | Wick | 5 | 10 | 50 |

Sheet Number 2 - 18CS56
| USN | Name | Attended | Total | Percent | |------------|-------|----------|-------|---------| | 1US22CS001 | Johny | 10 | 10 | 100 | | 1US22CS002 | Wick | 5 | 10 | 50 |

I wanted to pull data row from sheet 1 and sheet 2 based on USN and save it as CSV of the each and individual student. how to use python openpyxl or csv package in achieving this

CodePudding user response：

There is a basic way of doing it using functions, you can try this out.. I have explained each part of the code as well.

I have used csv library in this case

import csv

def pull_data(sheet1, sheet2, usn_number):
    # Open the first sheet
    with open(sheet1, 'r') as sheet1_file:
        sheet1_reader = csv.reader(sheet1_file)
        # Open the second sheet
        with open(sheet2, 'r') as sheet2_file:
            sheet2_reader = csv.reader(sheet2_file)
            # Open the csv file
            with open('data.csv', 'w') as csv_file:
                csv_writer = csv.writer(csv_file)
                # Loop through the first sheet
                for row in sheet1_reader:
                    # Check if the usn number is in the first sheet
                    if usn_number in row:
                        # If it is, add the row to the csv file
                        csv_writer.writerow(row)
                # Loop through the second sheet
                for row in sheet2_reader:
                    # Check if the usn number is in the second sheet
                    if usn_number in row:
                        # If it is, add the row to the csv file
                        csv_writer.writerow(row)
    
pull_data('sheet1.csv', 'sheet2.csv', 'USN_NUMBER')