dtから空白文字や改行コードやLineFeed文字を削除したい

[gorby (Noteworthy UiPath Forum contributer 2024)]

こんばんは。
UiPath Studio 2024.10.6Community editionのユーザです。

ExcelからRead Rangeアクティビティでdtに値を取得後、dtから空白文字や改行コードやLineFeed文字を削除するためには
Invoke Codeアクティビティでコード編集画面に下記コードを貼れば良いでしょうか？引数はdtです。

For Each row As DataRow In dt.AsEnumerable()
    For Each c As DataColumn In dt.Columns
        row(c) = row(c).ToString().Replace(" ", "").Replace(" ", "").Replace(VbCrLf, "").Replace(VbLf, "")
    Next
Next

[ashokkarale]

@gorby,

Use the LINQ like this.

Dim dtUpdated As DataTable = dt.Clone() ' Clone the structure of the DataTable

For Each row As DataRow In dt.AsEnumerable()
    Dim newRow As DataRow = dtUpdated.NewRow()
    For Each c As DataColumn In dt.Columns
        newRow(c) = row(c).ToString().Replace(" ", "").Replace(vbCrLf, "").Replace(vbLf, "")
    Next
    dtUpdated.Rows.Add(newRow)
Next

dt = dtUpdated

[gorby (Noteworthy UiPath Forum contributer 2024)]

Thank you!
However, any hindrance of my suggested code and any specific reason to make clone datatable?

[ashokkarale]

@gorby,

The reason for cloning the DataTable dt.Clone() instead of updating the source DataTable directly is to avoid modifying the rows while iterating over them, which can lead to unexpected behavior or runtime errors. Here’s a detailed explanation:

Key Points:

1. Row Collection Modification:

When iterating over the rows of a DataTable using a For Each loop, directly modifying or deleting rows can disrupt the enumeration process, potentially causing errors like “Collection was modified; enumeration operation may not execute.”

2. Safe Data Transformation:

By cloning the structure of the original DataTable dt.Clone(), you ensure that all the columns and their data types are preserved without any data. You can then safely create and populate new rows with the modified data.

3. Separation of Logic:

Cloning the table allows you to keep the transformation logic separate from the original data. This makes it easier to track and debug changes.

4. Data Replacement:

After populating dtUpdated with transformed rows, the final assignment dt = dtUpdated replaces the original DataTable with the updated one.

Alternative Approach (Direct Update):

If you still prefer to update the original DataTable, you can use a For loop instead of For Each:

For i As Integer = 0 To dt.Rows.Count - 1
    For Each c As DataColumn In dt.Columns
        dt.Rows(i)(c) = dt.Rows(i)(c).ToString().Replace(" ", "").Replace(vbCrLf, "").Replace(vbLf, "")
    Next
Next

This avoids the enumeration issue since the For loop uses indexing, which is safe for modifications.

LLM helped me to write this but it’s validated by me.