Excel data extraction; String manipulation and Split

Hi All!

Question for the community; I am extracting data from a specific row location in an excel table through an assign of var = row.item(3).ToString; The value it is pulling from the excel table has several values in it; For example purposes lets say Cat, Dog, Chicken, Bird, Fish are all viable options; So the value being extracted will look like “Cat, Dog, Chicken, Bird, Fish”; I am looking to split these values and store them into their own variables; Currently doing this by an assign of stringArray = var.Split(CChar(", ")); Works like a charm, I can call each array value with no problem;

Each row of this excel document in that specif column can contain different values; Ex below

  1. Cat, Dog, Fish
  2. Chicken, Bird
  3. Bird
  4. Dog, Fish, Chicken, Bird

I tried initializing variables as place holders in the event a “max” length String was entered after split (for this example purposes max would be 5 placeholder vars). Doing this throws an Error, “Index was outside the bounds of the array.”

Q: How can I loop through this String Array and dynamically allocate new variables as needed?

To be honest I do not understand the issue? If you have string splitted into array, you can easily reffer to array item using its index (counts from 0). To check array length, use arrayName.Length
So you can view all your strings using for each or while and of course reffer them using arrayName(index).

From the other hand you can use dictionary for it, you can store there the pairs of values. So ‘key’ will be your variable name and ‘value’ will be var value. Example attached.Main.xaml (7.3 KB)

1 Like

To expand on what I am trying to do is the information that I am reading in from the Excel file needs to be cross check to a website forum with check boxes. If any of the values in the String being generated are found on the forum they need to be selected.

I’ve logged the values being collected prior to the While Loop and they are present in the stringArray. The stringArray.Length is properly returning as well.

I’ve tried using a While loop with a condition “count < stringArray.Length”; Adding to count by " count = count + 1" at the end of the loop. It is compiling but its not finding the values;

Within the While Loop I’m using an If Control Activity to print “Cat is Found”… Used the following conditions with no result.

  1. stringArray(count) = “Cat”
  2. stringArray(count).ToString = “Cat”
  3. stringArray(count).ToString.Equals(“Cat”)

I’m taking this step by step at the moment and am just trying to properly recognize each value when it is present.

After looking at your recommendations I feel like I’ve made progress. Just not enough =)

isn’t it that using it you receive not “Cat”, but " Cat"? With space before?

I made another example basing on your description, maybe it will give you better idea about while and count Main.xaml (6.3 KB)

1 Like

Thank you!

This is now solved =D

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.