【Question】

I'm writing a Python script that works with two CSV files. Let’s call them csv1.csv (The original file to read) and csv2.csv (exact copy of csv1). The goal is to find the row and column in the CSV file that correspond to the modified user-defined input.

format:(Continues for about 2-3 thousand lines)

Records:

LNLIM,ID_CO,OD_DV,ID_LN,ST_LN,ZST_LN,ID_LNLIM,LIMIT1_LNLIM,LIMIT2_LNLIM,LIMIT3_LNLIM

'FPL','SOUT','137TH_LEVEE_B','B','137TH_AV','LEVEE','A',1000,1100,1200

'FPL','SOUT','137TH_DAVIS_B','A','137TH_AV','NEWTON','A',1000,1100,1200

...

Let's say that the user is looking for 137TH_AV and NEWTON. I want to be able to go row by row and compare the two columns/row indices ST_LN and ZST_LN. If both columns match what the user input then I want to capture which row in the CSV file that happened on, and use that information to edit the remaining columns LIMIT1_LNLIM LIMIT2_LNLIM LIMIT3_LNLIM on that row with new analog values.

I want to get the 3 new values provided by the user and edit a specific row, and a specific row element. Once I've found the place to replace the number values I want to overwrite csv2.csv with this edit.

1      Determining where the line segment is located in the array

import sys

import csv

import os

import shutil

LineSectionNames=[]

ScadaNames=[]

with open('Vulcan_Imp_Summary.csv','r')as file:

reader=csv.reader(file)

for row in reader:

LineSectionName=row[1]

ScadaName=row[29]

LineSectionNames.append(LineSectionName)

ScadaNames.append(ScadaName)

#Reformatting arrays for accurate references

LineSectionNames=[character.replace('\xa0','')for character in LineSectionNames]

LineSectionNames=[character.replace('?','-')for character in LineSectionNames]

ScadaNames=[character.replace('\xa0','')for character in ScadaNames]

#Setting Line Section name as key and Scada name as value

ScadaDict={}

For I in range(len(LineSectionNames)):

ScadaDict[LineSectionNames[i]]=ScadaNames[i]

#Prompt user for grammatical name of Line Section

print('Enter the Line Section Name:(Example=Goulds-Princeton)\n')

user_input=input()

#Reference user input to dictionary value to convert input into SCADAformat

Def reformat():

print('Searching for Line Section...'+user_input)

ifuser_inputinScadaDict:

value=ScadaDict[user_input]

print('\n\t Match!\n')

else:

print('TheLineSectionnameyouhaveenteredwasincorrect.Tryagain.\nExample=Goulds-Princeton')

reformat()

#Copying the exported file from Genesys

path='I://PSCO//DBGROUP//PatrickL//'

shutil.copyfile(path+lnlim_import.csv',path+lnlim_import_c.csv')

#Using the SCADA format to search through csv file

print('Searching csv file for...'+user_input)

#Reading the copied file

record_lnlims=[]

id_cos=[]

id_dvs=[]

id_lines=[]

id_lns=[]

st_lns=[]

zst_lns=[]

id_lnlims=[]

limit1_lnlims=[]

limit2_lnlims=[]

limit3_lnlims=[]

with open('lnlim_import_c.csv','r')as copy:

reader=csv.reader(copy)

for row in reader:

record_lnlim=row[0]

id_co=row[1]

id_dv=row[2]

id_line=row[3]

id_ln=row[4]

st_ln=row[5]

zst_ln=row[6]

id_lnlim=row[7]

limit1_lnlim=row[8]

limit2_lnlim=row[9]

limit3_lnlim=row[10]

record_lnlims.append(record_lnlim)

id_cos.append(id_co)

id_dvs.append(id_dv)

id_lines.append(id_line)

id_lns.append(id_ln)

st_lns.append(st_ln)

zst_lns.append(zst_ln)

id_lnlims.append(id_lnlim)

limit1_lnlims.append(limit1_lnlim)

limit2_lnlims.append(limit2_lnlim)

limit3_lnlims.append(limit3_lnlim)

#Reformatting the user input fromGOULDS-PRINCETON to'GOULDS' and PRINCETON'

input_split=user_input.split('-',1)

st_ln1=input_split[0]

zst_ln1=input_split[1]

st_ln2=st_ln1.upper()

zst_ln2=zst_ln1.upper()

st_ln3="'"+str(st_ln2)+"'"

zst_ln3="'"+str(zst_ln2)+"'"

#Receiving analog values from user

print('\n\t Found!\n')

print('Enter the Specified Emergency Rating(A)for110%for7minutes:')

limit1_input=input()

print('Enter the Specified Emergency Rating (A) for 120% for 7 minutes:')

limit2_input=input()

print ('Enter the Specified Emergency Rating (A) for 130% for 5 minutes:')

limit3_input=input()

Whenever I print the row_index it prints the initialized value of 0.

i=0

row_index=0

for I in range(len(st_lns)):

if st_ln3==st_lns[i] and zst_ln3==zst_lns[i]:

row_index=i

print(row_index)

limit1_input=limit1_lnlims[row_index]

limit2_input=limit2_lnlims[row_index]

limit3_input=limit3_lnlims[row_index]

csv_list=[]

csv_list.append(record_lnlims)

csv_list.append(id_cos)

csv_list.append(id_dvs)

csv_list.append(id_lines)

csv_list.append(st_lns)

csv_list.append(zst_lns)

csv_list.append(id_lnlims)

csv_list.append(limit1_lnlims)

csv_list.append(limit2_lnlims)

csv_list.append(limit3_lnlims)

#Editing the csv file copy to implement new analog values

With open('lnlim_import_c.csv',w') as edit:

for x in zip(csv_list):

edit.write("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}\t{10}\n".format(x))

 

It’s hassle to query a structured text file, locate a record and edit it in Python. You can try to handle it in SPL (Structured Process Language). The script is simple and easy.

First you need to define two parameters - user_input and update.

Here’s the SPL script:

	A
1	=file("csv1.csv").import@t(;",")
2	=user_input.array("-")
3	=update.array()
4	=A1.pselect(ST_LN=="'"+A2(1)+"'"    &&   ZST_LN=="'"+A2(2)+"'")
5	>A1.modify(A4,A3(1):LIMIT1_LNLIM,A3(2):LIMIT2_LNLIM,A3(3):LIMIT3_LNLIM)
6	=file("csv2.csv").export@t(A1)

A1: Read in the text file.

A2: Suppose the user_input value is a string like 137TH_AV-NEWTON, then we split it as sequence by the separator “-”.

A3: Suppose the update value is a string like 1000,2000,3000, then we split it as sequence by the separator “,”.

A4: Get sequence numbers of the records meeting the specified condition.

A5: Modify records of A1’s table sequence at specified positions; the new field values are the 3 members of A3’s sequence.

A6: Output the modified A1 to csv2.csv.