Filter and copy to excel

I want to filter data from my excelsheet…
In the spreadsheet, every column “B” the value is something like “SC-519219-2019 Testing”.
I only want the first 16 character or get “SC-519219-2019”
I have attached a list of the spreadsheet i used

I’m learning how to automate please guide me i really want to learn and use this to show in my company to buy/use this…

Test.xlsx (8.0 KB)

1 Like


Current update still trying to automate this using VBA to execute the codes but no idea how…

1 Like

You were almost done
—use excel application scope and pass the file path as input
—use a read range activity and get the output with a variable of type datatable named dt
—now use a for each row loop and pass the above datatable variable as input
—Inside then loop use a writeline activity like this
row(“yourcolumnname”).ToString.SubString(0,16).ToString

Or to assign back to the same column then

row(“yourcolumnname”) = row(“yourcolumnname”).ToString.SubString(0,16).ToString

Simple isn’t it
Hope this would help you
Cheers @haziqh

1 Like

What do put in my “yourcolumnname”?
I have insert my excelsheet above

I did this and got a error
I do not know whats my column no so instead i used index[0] to replace “yourcolumname”

1 Like

Fine
No worries

If we want the data of column B then
—Inside then loop use a writeline activity like this
row(1).ToString.SubString(0,16).ToString

Or to assign back to the same column then

row(1) = row(1).ToString.SubString(0,16).ToString

Here 1 represent the column index of column B as the column index usually starts from 0 for first column

Hope this would help you
Cheers @haziqh


cant seem to get it right…

1 Like

Fine no worries
Kindly enable once the writeline activity and let it be active
I would like to see the output of it getting displayed in the output panel so that we would be able to find the string
May be in addition we could know the length as well also that we could validate the length been passed
Like this without any SubString in writeline activity
row(1).ToString.Trim+” “+row(1).ToString.Length.ToString

Kindly share the screenshot of the value getting displayed in the output panel

Cheers @haziqh

@Palaniyappan

image

May i know whats the next step ?
could i attach my current xaml and excel , and help me see how i could filter each row in column “B” to be character 16…
thank you for replying & helping :slight_smile:

1 Like

If possible can I have a view I the excel alone as a screenshot
I saw the attachment with this thread but it doesn’t look clear
Kindly take a screenshot of the excel opened and with records visible

We are almost done
I hope it must be
row(0).ToString.SubString(0,16).ToString
But I would to make sure the structure of excel once
Give a try with this or no worries kindly share the excel screenshot please
Cheers @haziqh

@Palaniyappan

Instead of 16 i put it as 15 instead

How do i now make sure that all my column b is now 16 char ? because it hasnt been changed yet.

“Update”
I removed add headers in excel now got 2, looking closer to finish

@Palaniyappan any updates on how to finish it ?
basically writting the string of “row(0).ToString.SubString(0,15).ToString” instead of just putting it as writeline…

1 Like

Fine
Hope these steps would help you resolve this
—use excel apply scope and pass the file path as input
—Inside the scope use a read range activity and get the output with a variable of type datatable named dt and make sure that ADD HEADERS property is unchecked
—now use a for each row loop and pass the above variable dt as input
—Inside the loop use a if condition like this
row(0).ToString.Length >= 15
If this is true it will get into THEN part where we can have a assign activity like this
row(0) = row(0).ToString.Trim.SubString(0,16)

Or

It will go to ELSE part where we can have a assign activity like this
row(0) = row(0).ToString.Trim.SubString(0,15)

That’s all you are done
Hope this would help you
Cheers @haziqh

1 Like

Thank you @Palaniyappan for helping me learn and guide Uipath Programming.

It was really fast replies and solution!! :slight_smile: :slight_smile:

1 Like

No worries
Cheers @haziqh

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.