Import and Edit a CSV file with validation rules

Question

I am very new to Python / coding in general so excuse me if this is a simple question I'm just struggling to find an answer.

I have been tasked to create a very simple application using Python to import a CSV file and be able to either add data to the file or edit the data. However the bit I am struggling with is putting validation rules in. Example would be in the ID column it needs to be at least 6 numbers.

Below is the code I've got so far which allows me to import the file and be able to edit an existing row and then saving it as a new file. This works fine but as I said can I add validation rules in. If so how would I manage this?

import csv
myList = []

#2: Open my test file and populate list
with open ('Treasury_Faults.csv','r') as file:
   myFile =csv.reader(file)
   for row in myFile:
      myList.append(row)

#3: Show content of file with row numbers for selection
print ("Please read the csv file data below:")
for i in range (len(myList)):
   print ("Row " + str(i) + ": " + str(myList[i]))
   
#4: Select which row for editing
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 :")

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

#6: Display the new list to accept the changes made
print ("\nPlease see the details of the new file below:")
for i in range (len(myList)):
   print ("Row " + str(i) + " :" + str(myList[i]))

#7: Accept changes and save file
changeCSV = input ("\nWould you like to make the changes to the csv file ? Y/N").lower()

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

---

Here is an example of the file

Answer 1

Consider using openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files

An example of Validation using Openpyxl:

>>> from openpyxl import Workbook
>>> from openpyxl.worksheet.datavalidation import DataValidation
>>>
>>> # Create the workbook and worksheet we'll be working with
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> # Create a data-validation object with list validation
>>> dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
>>>
>>> # Optionally set a custom error message
>>> dv.error ='Your entry is not in the list'
>>> dv.errorTitle = 'Invalid Entry'
>>>
>>> # Optionally set a custom prompt message
>>> dv.prompt = 'Please select from the list'
>>> dv.promptTitle = 'List Selection'
>>>
>>> # Add the data-validation object to the worksheet
>>> ws.add_data_validation(dv)