Home > Back-end >  Get data from multiple sheets in excel using unique ID and Save to CSV using python
Get data from multiple sheets in excel using unique ID and Save to CSV using python

Time:06-08

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')
  • Related