Problems Adding/Removing Rows from DataTables

datatable
studio

#1

As part of a bigger process, I’m trying to read a CSV file (into a DataTable), select rows with a target string, then write out the selected rows to another file.

The obvious method is to:

Read the CSV file into a **DT**
In a ForEach loop (_for each_ **row** in **DT**)
   Use _Get Row Item_ to get a **ColValue**
   _If_ a row in which the **ColValue** matches a **MatchString**
       Use _Add data row_ to a second DataTable, OutDT
Output OutDT to a CSV file.

When it gets to the first Add data row , I get the error:

Add data row : This row already belongs to another table.

So I put in an Assign: OutRow = row and use Add data row specifying OutRow, but get the same error message.

Then I tried another tack…

Read the CSV file into a **DT**
Copy the DT using _Assign_ **OutDt** to **DT**
In a ForEach loop (_for each_ **row** in **DT**)
   Use _Get Row Item_ to get a **ColValue**
   _If_ a row in which the **ColValue** does not match a **MatchString**
       Use _Remove data row_ from **DT** (specifying the counter as the _RowIndex_) to delete unwanted rows from **DT**
Output OutDT to a CSV file.

This time the error is:

For each row : Collection was modified; enumeration operation might not execute.

This was not entirely unsurprising, so I copied DT, stepped through it and tried to remove unwanted rows from the new data table…

Read the CSV file into a **DT**
Copy the DT using _Assign_ **Dt** to **OutDT**
In a ForEach loop (_for each_ **row** in **DT**)
   Use _Get Row Item_ to get a **ColValue**
   _If_ a row in which the **ColValue** does not match a **MatchString**
     Increment a counter with _Assign_
       Use _Remove data row_ from **OutDT** (specifying the counter as the _RowIndex_) to delete unwanted rows from **OutDT**
Output OutDT to a CSV file.

I still get the same error, but can’t see why working on one table affects a second…

For each row : Collection was modified; enumeration operation might not execute.

I’d like to understand why neither of these approaches work. I’m thinking I’ll have to abandon what I’ve done and read the CSV file into Excel then manipulate the rows in there…

AJ


#2

So here’s a solution that will work once I know how best to convert a data row into text variables :slight_smile: .

When a match is found in the data table read in, then write the row (as data values) to a file (rather than try to use another table). I could use a “Get row item” for every column in the data table then construct a string by concatenating all the elements returned. I wondered whether there’s a way of assigning all the data values of a data row to an array of strings to avoid umpteen calls to “Get row item”. I’ll still need to construct an output string (that includes comma separators) using the array, though. So, ideally, there’s some way of achieving the following conceptual result:
strRowOutput = row.datatableConvertRowToString

eg, for a row in a CSV file that looks like: “Aaa,Bbb,Ccc,Ddd,Eee,Fff,Ggg,Hhh,Ijk,Jlm,Lmn,Mno,Nopq,Okk,Plk”,
strRowOutput can be set to “Aaa,Bbb,Ccc,Ddd,Eee,Fff,Ggg,Hhh,Ijk,Jlm,Lmn,Mno,Nopq,Okk,Plk” in a single call.


#3

This could help:

Particularly the importrow part.


#4

There might be:
strRowOutput = String.Join(",", row.ItemArray.Select(Function(x) x.ToString))
This also might work but can’t test now:
strRowOutput = String.Join(",", row.ItemArray)

String.Join takes 2 parameters - values separator (here it’s a colon) and a collection of strings or values directly convertible to string.
In the shorter version we pass as 2nd argument row.ItemArray which is an array of all values in a row (stored as objects).
In the longer version we take that array and select the .ToString output of each of them.

While this doesn’t directly help, it might be useful to know that every iterator stores a version of the collection it’s responsible for. Whenever a collection is modified, that version number is incremented and the iterator is made invalid (as it can no longer keep it’s contract that it will iterate through whole collection from beginning to end, without skipping any and without going over).
But if you iterate with an index (keeping a counter and accessing elements with f.e. yourDT.Rows(counter)) that contract is no longer made and you can modify your collection freely. On the other hand you yourself are then responsible for the iteration to go correctly.

I’m not sure if the link I posted contains that, but you could also use AddDataRow activity, but don’t pass the row itself, but row.ItemArray (in the first? argument of that activity - I don’t have UiPath with me, but I don’t doubt that you’ll jnow which one).


#5

Many thanks for:

… I can see that being of huge value to me and others, not least of which as a very convenient data table visualisation/debug tool ( “Checking row: " + String.Join(”|", row.ItemArray) in a Write Line activity. I’m going to put it in my list of useful constructs for future use :smile: .