SPREADSHEETS WITH PYTHON

This section covers using Python to read and write csv files. A very useful tool for manipulating data in a spreadsheet.
Limitations - saving your spreadsheet as a csv file does limit the features you can use in the spreadsheet, however you can still link the csv file to other spreadsheet documents that do retain all functionality.

WHAT IS A CSV FILE

CSV stands for Comma Separated Value. This type of file can be used for many applications including databases and spreadsheets. A comma separates each element within the document and it is the comma that allows programs such as MS Excel to put the data in to individual cells, rows and columns. You can view csv files in most text editors and you will see a comma separating each value, as can be seen in the example screenshot from MS Word.

READING THE CSV FILE WITH PYTHON

The code below shows two methods of reading the csv file through Python. The first method is useful if you want to keep it simple and just fetch the details from the csv file. The second method is more useful if you want to add more functionality to your program as we will see later in this section. If you want a csv file to test your code please feel free to download the 'activities.csv' file as seen in the examples.

---

​DOWNLOAD: CSV ACTIVITIES FILE

---

NOTE: Save your csv file in the same location that your Python file is saved, otherwise you need to pass in the file path and file name.

This video runs you through accessing the spreadsheet from Python, printing the content and searching for a value within the document.

USING THE OPEN METHOD - EXAMPLE CODE

import csv
file = open('activities.csv')#Replace the file name with your file

for line in file:
   print (line)

USING THE WITH OPEN METHOD - EXAMPLE CODE

import csv
with open ('activities.csv', 'r') as file:
   sports = csv.reader(file)
   for row in sports:
      print (row[3])

NOT WORKING ? CHANGING THE DELIMITER

If the code does not work, first check that the csv file is saved in the same location as the Python file. Otherwise you may need to change the delimiter. the delimiter is what separates the values and not all CSV files use a comma to separate the values. Open the CSV file in a word editing document and chack what separates the values. Then add the code to tell python what the delimiter is, as can be seen below.

with open ('activities.csv', 'r') as file:
   sports = csv.reader(file, delimiter = ' ')

SEARCHING THE CSV FILE

This section looks at taking a user input and searching the csv to find the value then returning the row of details where the value is found.

import csv

level = input("What level do you want to view? ").title()

with open ('activities.csv', 'r') as file:
   sports = csv.reader(file, delimiter = ' ')
   for row in sports:
      if row[2] == (level):
         print (row)

CREATE A PYTHON LIST FROM THE CSV DATA

Opening the csv file and creating multiple lists in python to read and store the data. A great method to use especially if you already have the data in a spreadsheet but want o create lists to hold the data in Pyhton.

This could also be quiet useful with the more recent CIE GCSE Computer Science coding tasks.

import csv

activity_ID = []
activity_type = []
activity_level = []
activity_description = []

with open ('activities.csv','r') as file:
   sports = csv.reader(file)

   for row in sports:
      activity_ID.append(row[0])
      activity_type.append(row[1])
      activity_level.append(row[2])
      activity_description.append(row[3])

print (activity_ID)
print (activity_type )
print (activity_level)
print (activity_description)

WRITTING TO A CSV FILE

This video talks you through writing to a csv file and taking a user input and writing the input to the csv file. The method below writes the column heading and then goes into a loop to enter the column content, asking the user each time to enter the details that will be appended to each column.

import csv

with open ('activityV3.csv','w+') as file:
   myFile = csv.writer(file)
   myFile.writerow(["Id","Activity","Description","Level"])
   noOfActivities = int(input("Please enter how many activities you want: "))
   for i in range (noOfActivities):
      activityID = input("Activity " + str(i + 1) + " : Please enter the activity ID: ")
      activity = input("Activity " + str(i + 1) + " : Please enter the type of activity: ")
      desc = input("Activity " + str(i + 1) + " : Please enter the description of the activity: ")
      level = input("Activity " + str(i + 1) + " : Please enter the level of the activity: ")
      myFile.writerow([activityID, activity, desc,level])

APPENDING A CSV FILE

A quick append method to add more data to the end of a CSV file

EDITING A CSV FILE

This methods demonstrates using a list to edit a specific part of the CSV file. The video and code show a user-friendly way of showing the user the content of the file, asking the user what changes they would like to make and then writing the file.
​

#EDITING A CSV FILE
#Task 1: Import CSV and create an empty list
import csv
myList = []

#Task 2: Open the file and populate the empty list
with open ('activityv4.csv','r') as file:
   myFile =csv.reader(file)
   for row in myFile:
      myList.append(row)

#Task 3: Show the user the content of the file and print row number to make selection easy
print ("Please see details of the csv file below:")
for i in range (len(myList)):
   print ("Row " + str(i) + ": " + str(myList[i]))
   
#Task 4: Allow the user to select which row to edit
editRow = int(input("\nWhich row would you like to change? Enter 1 - " + str(len(myList)-1) + " :"))
print ("Please enter the new details for each of the following :")

#Task 5: Allow the user to make add the changes and append changes to the list
for i in range (len(myList[0])):
   newDetails = input("Enter new data for " + str(myList[0][i]) + " :")
   myList[editRow][i] = newDetails

#Task 6: Show the user the new list and confirm changes
print ("\nPlease see the details of the new file below:")
for i in range (len(myList)):
   print ("Row " + str(i) + " :" + str(myList[i]))

#Task 7: If changes are needed write the new file
changeCSV = input ("\nWould you like to make the changes to the csv file ? Y/N").lower()

if changeCSV == ("y"):
   with open ('activityv4.csv','w+') as file:
      myFile = csv.writer(file)
      for i in range (len(myList)):
         myFile.writerow(myList[i])