Cleaning up the data table

Hi everyone,

I extracted an data table from MS Access. The issue is, that after pasting it in Excel in many rows of data table the word “Schaltfläche”.
I want to delete this whenever it occurs, and to delete empty spaces (trim data).

I know how to trim/delete/replace text in one cell of data. But how can I do this with whole dt/Excel table?

Here is an Excel file!

ThankstestUiPath.xlsx|attachment (17.9 KB)

Use a For Each Row activity, then inside that, use a For Each activity iterating over row.Table.Columns. This will iterate over every cell of the table.

PD ID 2.8Forum.xaml (18.4 KB)
Thanks, but it Looks that I am making some mistake. Can´t crack it…
Could you have a look on the workflow?

@sspi1153
as far I did understand your requirement you want to replace starting/ending spaces (so called trim) for each row in every cell/row column

I would suggest following:

  • change the statement from row.Table.Columns to ExtractDataTable.Columns.toList
  • change the TypeArgument to DataColumn (for each activity nested within the for each row)
  • use assign:
    • to: row(item.Columname), row(item.Columnname).toString.Trim

for output activity you can use row(item.Columname).toString

About deletion of row/cells with Schaltfläche I would suggest to start with a filter datatable for it

Hi ppr,
thanks. But I don´t want to delete rows/columns with “Schaltfläche”. I just wan´t to delete “Schaltfläche” and keep the rest of the text.

ah ok. so row(item.Columnname).toString.Replace(“Schaltfläche”,“”).Trim should do it

Hi,
it not really working.

“change the statement from row.Table.Columns to ExtractDataTable.Columns.toList” - I can´t do this. I am getting message ‘toList’ in not a member of ‘System.Data.DataColumnCollection’

It might be, that I am doing something wrong.
Here is again the workflow.

ThanksPD ID 2.8Forum.xaml|attachment (10.7 KB)

@sspi1153
please check your post, something went wrong with the attached XAML as it is not downloadable
Also check following for getting prepared

PD ID 2.8Forum.xaml (10.7 KB)

Should be ok now :slight_smile:

Hi @lucas.mcgonagle You can fix it by adding the Assembly Reference manually in your xaml file: [image] Simply open the file in Notepad and create new line with this String: System.Data.DataSetExtensions Then reopen your project and the error should be gone. Please be careful while doing so and always keep a backup

Blockquote

Not sure if I can take a risk and jeopardize the complete work.

@sspi1153
Make a change to ExtractDataTable.Columns instead of ExtractDataTable.Columns.toList then it fix the validation issue

Hi again!
Thanks - it works. It cleans and trims the data.

But I am having a troupe with outputing it.
It builded new dt, and tried with add data row activity. That did not worked well. Data is not properly structured - what should be in columns is stacked into rows.

So I am trying with add column activity. I never used this one, so it is not working as well :confused:

Here my WF again - could you have a look again ?

Thanks a lotSequence1.xaml|attachment (13.7 KB)