TN - 1317 Using System.IO.StreamReader to read a CSV file into an Array.

Description

This article from InSource shows how to use the System.IO.StreamReader to read a CSV file into an Array.

• Author: Glenn Yancey
• Published: 12/15/2022
• Applies to: Application Server 2014 R2 and greater

Details

There are times where we need to read in rows from a Comma Separated File (CSV), that would be broken up into multiple fields.   After we read in these rows, we then want to populate an array that can be used to assign to Object Attributes.   The .NET Class known as (System.IO.StreamReader) is designed to allow us to read through a text file.  Let’s see how we accomplish this with a script when we are reading a CSV file with comma delimiters.  The script below is merely for showing the concept of reading rows from the CSV file, but not the use of TRY CATCH functionality for error handling.   

1)     This is the CSV file that I am using with two rows.  

·        Row 1: Name of the Fruit

·        Row 2: Amount of Sweetness in that Fruit in Degrees Brix.  

2)     Create a Template from the $UserDefined template with a PV attribute to contain the PV of the Fruit’s sweetness.    We also create a script that will populate that PV based on the ROW that contains the fruit name of the instance that it finds when reading this file. 

3)     I created a script on the Template called “ReadBrix” that executes ONCE when deployed and placed OnScan.  

The following are local variables defined through a Dimension (DIM) statement. 

SR is the construct that we are creating off the System.IO.StreamReader .NET Class.

Fields[] is the String array that we creating.

MyDelim is the variable that we are using to define HOW we are splitting each row.

S is used as to contain the values of the row before parsing/splitting.

firstValue and secondValue represent the 2 fields in a row.

'Declarations

dim sr as System.IO.StreamReader;

dim Fields[2] as STRING;

dim MyDelim as System.Char;

dim S as STRING;

dim FruitName as STRING;

dim firstValue as STRING;

dim secondValue as STRING;

FruitName is used to pull in the name of the Container which is the name of the product line which will be used for a search.  

'Assign file locations, delimiters, and name of container.

FruitName = MyContainer.Tagname;

The following function System.IO.File.OpenText defines location and name of the CSV file to read in.

MyDelim is a local variable used to define the System.Convert.ToChar(“,”) as comma for the delimiter.

sr = System.IO.File.OpenText("c:\Fruits\BrixByFruit.csv");

MyDelim = System.Convert.ToChar(",");

The WHILE loop is using the Peek() function of the SR construct to execute and read in all rows until it reaches the end, denoted by the “-1”.  Once finished with reading all row, then close out the WHILE loop with “EndWhile”. 

The S.Split() enforces the delimited to split the values of the row into 2 fields to match the amount of columns in the CSV file.  By reading this into the array known as Fields[], it now has values siting in 2 parts of the array.

Fields[1] is the first column which contains the name of the fruit in which it represents.  

Fields[2] now contains the value of the amount of sweetness in the fruit in the Engineering Unit known as “Brix”.  

' Read Rows

while sr.Peek() > -1

      s = sr.ReadLine();

      Fields[] = S.Split(MyDelim);

I’m doing a search via an IF..THEN..ELSE statement to say IF the value of Fields[1] is the same as the name of the Container object, THEN read in the value in Brix of that fruit from the Container is named from the value in Fields[2].   Once finding the row in question, then END out the search with “ENDIF”.  

LogMessage is a built-in used to populate the error logger with custom flags from the script. Here, I populate the logger with the split fields reconstructed with a sentence.

I am populating the PV of the current instance with the value from Fields[2] which contains the value of the Fruit’s degree of sweetness in Brix.

      if Fields[1] == FruitName then

            LogMessage(FruitName);

            LogMessage("The " + Fields[1] + " has the sweetness of " + Fields[2] + " in Degrees Brix.");

            Me.PV = StringToReal(Fields[2]);

      endif;

endwhile;

sr.Close();

4)     Then after creating 2 instances of them, I assigned them to 2 fruits.    I changed the Contained name to “[Refractometer]” as well.   

5)     Checking in the Object Viewer, the PV of the Refractometer was populated with the right values per their fruit as noted by the above CSV file.