Removing spaces in data table

Help Activities
1

Hello,

I have a data table - when I output the data table so I can see what it looks like, there are spaces after after full stops like this with every new row:

image

Is there a way I can get the bot to trim the start of every row so there are no spaces at the beginning?

2

Hi @E.T.S

Assign activity:
dataTableWithSpacesRemoved = yourDataTableVar.Clone()

dataTableWithSpacesRemoved = (From row In yourDataTableVar.AsEnumerable()
                             Let updatedName = row.Field(Of String)("Name").Replace(" ", "")
                             Select newRow = dataTableWithSpacesRemoved.Rows.Add(row.ItemArray)
                             ).CopyToDataTable()

Regards

3

Hi,

Thanks for your reply!

Does this remove every space - as I would only like to trim the start of every row

4

@E.T.S

Can you show the table

because you are sating start but there is a period.

cheers

5

Hi @E.T.S

Try the below linq expression in assign activity -

dt = (From row In dt.AsEnumerable()
      Let newRow = row.ItemArray.Select(Function(item) If(TypeOf item Is String, DirectCast(item, String).Replace(" ", ""), item)).ToArray()
      Select dt.Rows.Add(newRow)).CopyToDataTable()

Hope it helps!!

6

Hi @E.T.S

dataTableWithTrimmedSpaces = (From row In yourDataTableVar.AsEnumerable()
                              Let updatedName = row.Field(Of String)("Name").TrimStart()
                              Select newRow = yourDataTableVar.NewRow().ItemArray.CopyToDataRow().SetField("Name", updatedName)
                             ).CopyToDataTable()

Regards

8

Can you try check the Preserve format option in the Read range workbook activity. Then it will give the correct format of the data in the excel to datatable… @E.T.S

9

@E.T.S

Can you show the excel…Need some clarity on the ask and what the issue is

cheers

11

I am unable to provide the Excel unfortunately due to it sensitive information

Apologies I believed new rows were not being recognised but when checked they are

The issue I am having is spaces are being added to the beginning of my row - unsure how to trim the start of the row

So The first cell in the row will have space then text like this: " abc"

I am trying this:

image
image1826×687 42.6 KB

12

Please could you show me how to implement this

13

Hi @E.T.S ,

We would ask for a Sample or dummy data which would replicate the same formatting as that of the original data that you would have, In this way the methods suggested/applied to the Sample should also be applicable to the original one.

14

@E.T.S

If only first column has those spaces and are to be trimmed…then you can use the below in invoke code with dt as in/out argument

dt.AsEnumerable.ToList.ForEach(sub(r) r(0)=r(0).ToString.Trim)

cheers