For each excel row from bottom up

[joel.ng]

hi all,

trying to run a loop where i parse through a column and split each row into several rows that i insert below the current row. however, running this from top down means that the loop stops at the original number of rows. could i do this in reverse so row count at the top is unaffected or is there another route i should consider?

many thanks!

[ppr (Peter Preuss)]

Maybe you can share some sample data.

We had understood a scenario like:

ID	Items
1	A,B,C
2	D,E,F

and a needed output like:

ID	Items
1	A
1	B
1	C
2	D
2	E
2	F

We could solve it with an Essential modelling and/LINQ approach.

Assumption: Excel data is readin into a DataTable - dtData

LINQ Approach for above example:

Assign Activitiy
dtResult = dtDataClone

Assign Activity
dtResult =

(From d in dtData.AsEnumerable
From s in d("Items").toString.Split(","c)
Let ra = new Object(){d("ID"), s.Trim()}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

---

The LINQ form above decomposed to essential modelling would look like this:

Assign Activitiy
dtResult = dtDataClone

• for each row Activity| row in dtData
  • for each Activity| item in row(“Items”).toString.Split(","c)
    • Add DataRow Activity:
      ArrayRow: new Object(){row(“Name”, item.Trim}, DataTable: dtResult

[joel.ng]

thanks Peter for the very comprehensive solution breakdown. this is almost there, i just have to work in some regex instead of splitting by a delimiter. so it’d be something like this (x is junk text):

ID	Items	y
1	xxxxxx A 123,B 234,C 345 xxxxxx	702
2	xxxxxx D 123,E 234,F 345 xxxxxx	702

and a needed output like:

ID	Items	y
1	A	123
1	B	234
1	C	345
2	D	123
2	E	234
2	F	345

[ppr (Peter Preuss)]

Based on your inputs a regex could look like this:

with refering to Groups:

[CheatSheet] - System.Text.RegularExpressions | RegEx

This CheatSheet introduces the basic use of regex functions. With further examples also special cases are presented. Introduction From the namespace System.Text.RegularExpressions following methods are offered: Regex.Match Regex.Matches Regex.isMatch Regex.Replace Regex.Split A simple usage for example would look like this: [grafik] Recommendation: add System.Text.RegularExpressions to the imports: [grafik] it allows to use the shortened statement, as the namespace part can be ommited…

And above suggestion just need a small adaption

Assign Activitiy
dtResult = dtDataClone

Assign Activity
dtResult =

(From d in dtData.AsEnumerable
Let mts = System.Text.RegularExpressions.Regex.Matches(d("Items").toString, "([A-Z]) (\d+)")
From m in mts.Cast(Of Match)
Let ra = new Object(){d("ID"), m.Groups(1).Value, m.Groups(2).Value }
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

[joel.ng]

Thanks Peter, this worked!