A fairly common activity if you are interacting with external systems is to exchange data with the external system via a CSV. The primary reason people do this is to avoid having to create a large number of registers or data exchange elements for an unknown quantity of values.
Think about the following simple example. Say you have a robot stacking boxes on a pallet. For each job the robot does it has a list of legal boxes that can go on that pallet. The data from your MES system might look like this.
J1234, L1, L2, L3, L4
This string may be saying a new job is “J1234”, and the list of acceptable lots/boxes that can go on this particular pallet is “L1, L2, L3, L4”
One approach might be to run down the string character by character, peeling off characters and sticking them in some holding register then doing something with that value when you get to a delimiter. It works but it’s really inefficient and quite messy.
Regular expressions to the rescue!
The first thing you need to figure out is how to write a regular expression to pick apart a CSV. I’ve seen, and used, about 10 different approaches but I’ll start you off with the most simple one.
According to my trusty RegexBuddy explanation tool here is a technical explanation
Match any character that is NOT a “,” between one and unlimited times, as many times as possible, giving back as needed (greedy)
In english that means match all the characters up until you find a comma. It doesn’t matter if it’s one comma or multiple commas. Then start your next match when you find the next non-comma. What’s neat about this approach is that you don’t have to worry about skipping over blanks. It works automatically. If you did care about blanks you could use a ? in place of the +. This indicates you only want 1 match at a time. Play with your favorite regex design tool (my favorite is Regex Buddy) and see what you get.
So, on to the actual code. It’s pretty short and commented so I’ll just do a dump here.
‘Dimension your variables
Dim RegexObj as System.Text.RegularExpressions.Regex;
Dim MatchResults As System.Text.RegularExpressions.MatchCollection;
Dim Match As System.Text.RegularExpressions.Match;
Dim Index as Integer;
‘Setup the Regular Expression with the configured Regex
RegexObj = New System.Text.RegularExpressions.Regex(Me.Regex,System.Text.RegularExpressions.RegexOptions.IgnoreCase);
‘Use the regular expression to compare against the CSV
MatchResults = RegexObj.Matches(Me.csv);
‘Redim the array to match the number of results
Me.Array.Dimension1 = MatchResults.Count;
‘Loop through the matches and copy them into the array
for each Match in MatchResults
Index = Index + 1;
Me.Array[Index] = Match;
One of the neat tricks you’ll see is that I redim my array on the fly to the count of actual matches. One piece of code I left out was clearing the array before populating it, but that is really oustide the scope of what we’re doing here.
Another option you could use is the .Net split function or the Regex split functionality. Either way there are lots of options for splitting CSV’s so try out a few and figure out what works best for you.