The previous Advanced Data Import – Importing a CSV file article demonstrated the power and flexibility of PIPE-FLO® Professional’s new Data Import feature by taking advantage of Python™ scripting to generate or retrieve data. One flaw in the presented approach is the difficulty of locating and correcting bad data in the .csv file being read. If it is run with invalid data types or empty fields, it would abort the script and return an error for the line of code where the exception was encountered. Fortunately, Python™ provides some avenues for users to catch these problems in advance and deal with them in a more expedient manner. This extra effort results in a Data Import script that is robust, accurate, and time-saving across multiple files and users.
This example will build upon the previous techniques and only the newly introduced lines are commented on for an explanation. If you have questions about lines that are not commented, see the previous article in the series: Advanced Data Import – Importing a CSV file.
The example below reads pipe data (name and length) from a .csv file, checks the data for validity, and imports the valid items into a PIPE-FLO® Professional model. When exceptions are encountered, they are tallied and reported in the Data Import – Output window. The pipe names correspond to Tutorial Complete v12- US Units.pipe, which can also be found in My Engineering Files>Projects>Sample Systems. The text below can also be copied into a text editor to create the bad_pipe_data.csv and check_pipe_data.py files necessary to run this example in PIPE-FLO®.
The bad_pipe_data.csv file consists of pipe name, length, and size in comma delimited rows. Notice that there are some erroneous values:
PIPE 16,150,6 in
The Data Import file check_pipe_data.py locates bad_pipe_data.csv and uses a loop to read, test, and set the values in PIPE-FLO®. Errors and update counts are printed to the Output window where they occur. The # indicates lines that are commented out for an explanation of the script.
dir = os.path.dirname( os.path.abspath( __file__ ) )
data_file_path = os.path.join( dir, ‘bad_pipe_data.csv’ )
doc = pipeflo().doc()
errors = 0 #initialize the error count variable
updates = 0 #initialize the update count variable
with open( data_file_path ) as csvfile:
reader = csv.reader( csvfile )
for data_row in reader:
pipe_name = data_row
length_value = data_row
try: # Set environment to test values without aborting the rest of the script when errors are encountered.
p = doc.get_pipe( pipe_name ) #assign pipe name call to the variable ‘p’
except RuntimeError: #check for runtime error (pipe name in .csv file does not exist in model)
print( ‘Did not find pipe’, pipe_name ) #print problem to the output window in PIPE-FLO
errors += 1 #adds 1 to the error count for eventual reporting – OPTIONAL
continue #return to the top of the for loop, ignoring the erroneous data and continuing with the next row of the .csv file
try: #set environment to test values
pipe_length = float( length_value ) #try to assign the value as a floating point number
except ValueError: #check for value type error which indicates data does not match the expected type
print( ‘Invalid value type’,pipe_name, length_value ) #print problem to the output window
errors += 1
p.set_length( length (pipe_length, feet_length))
updates += 1 #add 1 to the count of updated demands
print( ‘Import finished.’ ) #print to the output window
if updates > 0: #if updates exist print count – OPTIONAL
print( updates, ‘items updated.’ )
if errors > 0: #if updates exist print count – OPTIONAL
print( errors, ‘errors.’ )
Once created, the file can be opened in PIPE-FLO® by dragging the file onto the FLO-Sheet or clicking the Data Import button and opening the file from there.
Important: The .csv file must be properly named and in the same folder as the .py file for this script to work.
Notice that the script prints the test failures and update/error counts in the Output tab of the Data Import dialog, and the Errors tab is empty. It should look like this:
Did not find pipe PIPE 16
Invalid value type 10-TW-6 AAA
Invalid value type 10-TW-7
1 items updated.
The real value in this script is not the time saved to input the values from the .csv file for a single project, but the flexibility to drop any .csv file with the appropriate column format into the folder and rerun the script at any time. This could be used for future revisions to this model or be applied to any PIPE-FLO® model and any .csv file pair as long as the pipe names match and the sizes are valid for the pipe specification in the model. In addition to the time saved, the reduction in manual data transcription leads to better data integrity. Essentially, the time investment to develop a flexible script can provide savings and accuracy on all current and future models.