How to remove rows from data table

Help Activities
, ,
1

Hi,

I have data shown below :
image

I have a column Customer Name, and I want customer name John Doe start on A2. How do I remove the blanks, and other stuffs?

3

But the customer name can be variant. It can be jane doe. Any other options?

4

hey

use a read range activity and save you DT

then use this

DTExcel.AsEnumerable.Where(Function(x) x("Customer Name").ToString.Trim.ToLower.Equals("Jhon Doe")).CopyToDataTable

then use write range activity in your excel

Regards!

5

Is there any specific way to identify correct customer…like it should always be in format First Name space Last Name

6

Do I have to assign the function you give? If so, what is the variable type and the customer name can be variant… It can be Jane Doe, Smith etc

7

Unfortunately no… I got the data from another excel and have to write it to another excel

8 
DTExcel.AsEnumerable.Where(Function(x) x("Customer Name").ToString.Trim.ToLower.Equals(yourStringVariable)).CopyToDataTable
9

Hi @Rhys18 ,

Maybe we are not understanding the requirement completely, Could you maybe also provide the Expected Output for this Input data ?

10

Hi,

Sorry I didn’t provide in details. The expected output is like this :

image

The first picture showing John Doe is appearing on A16… I want John Doe appear on A2… This goes for every single customer name (Jane Doe on A3, Mark on A4, etc)

11

@Rhys18 ,

Still the requirement is a bit fuzzy.

Do you have the list of Customer Names that you want to identify and arrange it in the Excel ? or do you want the Robot to identify the Customer Names and arrange it in the beginning ?

If it’s the second option, we would require more details about what would be the contents present in the column, we would need to know if there can be any pattern we could find to understand that it is a Customer Name. Simply speaking customer name is just a name, a string text. To Identify it as a name automatically we may need to involve ML Packages in the process.

12

Actually no, It doesn’t need to identify customer names or arrange it… Simply like a copy paste but without blank space and unnecessary value that I marked on red box :

image

So from that, become like this :

image

No unique customer name or anything. Simply like a copy paste but to New Excel File

13

@Rhys18 , We would either need to know what are the Unnecessary values or what would be the customer name values, we could also make classification based on their patterns, suppose there are always two names in Customer Name, such as “John doe”, here we can make the assumption that Customer names will always have two names separated with spaces and produce a solution along this logic.

Similarly for Unnecessary values, if we have a common pattern, like it will always be a Single word, no spaces, we can use this pattern for Identifying unnecessary values and remove it.

Do we have such kind of patterns that are observable ?

14

Ohh no, there is no such pattern

The excel first name can be variant. It can be 2 words (John Doe), it can be company name (Moto Cars ABC 123) for example… So there is no pattern that can be observed. Can I just copy paste the value from the excel file to another excel file? I tried the copy/paste range activity. It’s working but since there is no template file, the width of the column need to be adjusted alone… I need to do with template file

15

@Rhys18 ,

We can Copy/Paste the data, also the column width adjustment can be done.

But I believe the Query was to remove unnecessary values from the Excel, keeping only the required values.

So we do not have a particular way to identify these values or we do not know the background of the Process before this data is retrieved, Hence, we cannot provide you with a solid solution for this.

We could however remove the blank spaces/Empty values in the column.

16

Okay so, the excel file is downloaded from my company website. And this is the pattern I can give :

image

The excel from my company website is actually Customer name starting on column E, and then the customer ID starting on Column M. I just assign the customer name and ID to datarow, and then write it and the result are shown at the picture at top.

The only unique (and always will be) is from A1:B12. A13, B13 etc is the value of customer name, and their ID. So is this A1:B12 can be something unique?

17

@Rhys18 ,

Yes, We could remove the values with this range from the Excel if this is always constant.

Try using the Delete Range Activity for this operation.
image

18

Thank you it works !!

1 Like
19

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