Take user input and store those in excel sheet using Python

In this tutorial, we will learn how we can take input from users and save those into a spreadsheet file using Python. This functionality is important in Python as many a time developers are required to store the user data. The stored data are used for analytics and development sometime in the future.

Importing the CSV module

We can divide the whole program can into five separate tasks. First of all, we will import the CSV module to store the values in an Excel Sheet. In this program we are going to create a new CSV file by using the writer() method on the CSV module, take the user input and write that to file in the form rows to the file using the writerow() method.

Opening or creating the file

After importing the CSV module, we are going to create a new file. We will name the file as EducationDetails.csv and open it as the file. We will also add ‘w’ as a parameter, which is to write to the file. Then we’re going to apply the writer() method on CSV using that file, and store it in a variable called myFile.

Writing the column headers

The next thing we’ll do is write the column headers on which we want the details. For example, we can add the course, institute, marks, etc. for the relevant education. We will pass these header strings as a list to the writerow() method on the file object we created above. If we run this program now, this will just create the file with the column headers. We can check if this worked or not by opening the CSV file.

Getting the number of rows to add

We can also just add a question for users on how many records they want to add, which will require us to add another user input for the number of education details they want to add. We will typecast this input as an integer as this will be a number.

Using for loop to write user input to the file

The next task is to ask the user for details on the column headers we just created. This will allow us to populate these details into the CSV file. For taking user input, we use the input function with suitable prompts and store these inputs in appropriate variables. Again, we will apply the writerow() method on the file object using input variables in a list as the parameter to write these details to the file. To make it a bit more convenient for the user, we can add the education number at the beginning of the input prompts.

Finally, we will loop through the range of the number of educations specified by the user to get the specific details of each education level. For this, we will put the input variables and the final writerow() method created above inside a for loop.

Python program to store user input data in excel sheet using Python

#1 Importing the CSV module
import csv

#2 Opening or creating the file
with open('Academic Details.csv', 'w', newline="") as file:
    myFile = csv.writer(file)
    
#3 Writing the column headers    
    myFile.writerow(["EducationLevel","CourseStream","UniversityInstituteBoard", \
                     "PassingOutYear","MarksinPercentage"])

#4 Getting the number of rows to add   
    noOfEducationLevels = int(input("Enter how many education levels you want: "))

#5 Using for loop to write user input to the file   
    for i in range(noOfEducationLevels):
        edu = input("Education "+ str(i+1) +": Enter level(10th/12th/Graduation...): ")
        course = input("Education "+ str(i+1) +": Enter course/stream: ")
        institute = input("Education "+ str(i+1) +": Enter University/Institute/Board: ")
        year = input("Education "+ str(i+1) +": Enter Passing Out Year: ")
        marks = input("Education "+ str(i+1) +": Enter marks in percentage: ")
        myFile.writerow([edu, course, institute, year, marks])

Input:

Enter how many education levels you want: 3
Education 1: Enter level(10th/12th/Graduation...): Post graduation
Education 1: Enter course/stream: PGDM Finance
Education 1: Enter University/Institute/Board: LBSIM Delhi
Education 1: Enter Passing Out Year: 2022
Education 1: Enter marks in percentage: 70
Education 2: Enter level(10th/12th/Graduation...): Graduation
Education 2: Enter course/stream: B.Com (Hons.)
Education 2: Enter University/Institute/Board: Delhi University
Education 2: Enter Passing Out Year: 2018
Education 2: Enter marks in percentage: 60
Education 3: Enter level(10th/12th/Graduation...): 12th
Education 3: Enter course/stream: Science
Education 3: Enter University/Institute/Board: CBSE
Education 3: Enter Passing Out Year: 2015
Education 3: Enter marks in percentage: 90

CSV file:
Take user input and store those in excel sheet using Python
Also read: Take user input and save those in .txt file using Python

Leave a Reply

Your email address will not be published. Required fields are marked *