Excel Data Manipulation Advice

Help
1

Hi Everyone.

I am trying to replace the record values from excel.
Eg: The column name is “Transaction Details” and the values are “Payment to 254716119824 - mumbi mwendwa”

Now I want to replace “payment to 254” to "0"716119824 (Just the number need to be written in the same cell)

Can anyone please advise.

Regards
VishnuBulkReport_9919150.xlsx (25.8 KB)

2

Hi @winningvish

You can achieve it by looping through your Data Table using For Each Row activity. In the loop you should do a simple assign with a Regex that will delete non-digit characters:

row.Item("Transaction Details") = System.Text.RegularExpressions.Regex.Replace(row.Item("Transaction Details").ToString,"[^\d+]","").ToString
1 Like
3

hello @loginerror

Thank you so much for your quick response. I have implemented the workflow as you explained. But it is failing to identify the row with “Transaction Details”.

Requesting you to check my work flow.

Many thanks in advance Maciej

Regards
Vishnu
Main.xaml (9.5 KB)
project.json (674 Bytes)

4

This small modification should fix it:
image

Without specifying the range, Read Range will read entire sheet starting from cell A1, while your table starts from A13 :slight_smile: (this is why header names are not properly assigned).

Also, you can check this box in the properties of your Write Range:

image
image745×55 1.24 KB

That is just so that it writes down the Data Table to Excel together with those headers.

5

Honestly saying that, you are brilliant and awesome @loginerror

After making some minor changes as you advised, the output is as I desired. for that I’ve used for each row 2 times, but missing the 0 in front of the number in excel file.

As per my knowledge we cant add 0 (before numbers) in excel until and unless we change the number format to text. At this stage is it possible to add 0 in UiPath using Regex?

Would you please advice how we can achieve this?

Attached the updated XAML file.project.json (674 Bytes)
Main.xaml (11.2 KB)

Kind Regards
Vishnu

6

Hi @winningvish

See attachment:
KeepZeros.zip (23.9 KB)

Because Excel Application Scope uses the Excel to do the operations, it will automatically apply the default format cell. One way to change would be to invoke VBA code that changes the data type for that column.

However, a simpler (and faster) approach is to not use Excel Application Scope but the other set of Excel activities that work directly on the files. This way the zeros will be kept :slight_smile:

1 Like
7

Mate really I am amazed the way you think. Now the work flow is as expected. Very much appreciated your help and support.

A bit confused why it would not work for Excel Application Scope and worked for Without excel application scope.

Would like to ask you one last question, is it possible to loop through this same workflow for the multiple excel files in a folder?

Regards
Vishnu

8

It worked without Excel Application Scope because of the difference between the two methods.
Excel Scope opens the Excel file in Excel itself and it gives it a chance to modify the data types of acolumn.
Without Excel scope, the data is written directly to the file without opening the Excel itself. This way Excel has no opportunity to modify data type of the column, and this is also why after opening you get those green marks for the cells in that column.

For your next question, see here:
image

You can use Directory.GetFiles(“yourPathToTheDirectory”) as an argument of a For Each loop. It returns an array of strings, so make sure to select String as your TypeArgument of the For Each loop:
image

2 Likes
9

Thank you @loginerror perfectly understood a new concept cause of you. Did exactly the same for the next question.

Much appreciated.

Is there any UiPath What’s App group that I can join if you don’t mind.

Regards
Vishnu

10

Hi @loginerror

Trust you are doing well. would you please let me know how can do vlookup.
I have 2 excel files in a folder. file-1 and file-2
Have to compare the columns of “Transaction details” and “Amount” in file 1 with the columns of “Phone Numbers” and “Amount” in file 2
If matches with the phone number and amount, in file-1 need to create a column as “OK/NOT_OK” and print OK if matches otherwise NOT_OK.

Is it possible using UiPath?

Please advice.File1.xlsx (12.4 KB)
File2.xlsx (32.5 KB)

Thank you so much in advance

Regards
Vishnu

11

Hi @winningvish

See attached project:
ComparingExcelFiles.zip (38.4 KB)

It is a bit rough way of handling that request, but I think it works well here.

What it does:

  • loops over each row from File1
  • selects rows from File2 that match exactly your condition (= it will select a row from File2 that has Phone Number equal to Transaction details AND that has the same amounts)
  • checks if exactly one match was found

If yes, it writes OK, if not, NOT OK

I hope it helps and feel free to study the example and modify it as you wish :slight_smile:

1 Like
12

Hi Everyone,
would anyone please let me know how can we do vlookup.
I have 2 excel files in a folder. file-1 and file-2
Have to compare the columns of “Transaction details” and “Amount” in file 1 with the columns of “Phone Numbers” and “Amount” in file 2
If matches with the phone number and amount, in file-1 need to create a column as “OK/NOT_OK” and print OK if matches otherwise NOT_OK.

Is it possible by using UiPath?

Thank you so much in advance.

Regards
Vishnu
File1.xlsx (12.4 KB)
File2.xlsx (32.5 KB)

13

@winningvish

On top of my previous post, see here for a working solution using the Lookup Data Table activity :slight_smile:
WithVlookup.xaml (9.0 KB)

1 Like
14

@loginerror you are brilliant. appreciated your quick help and support.
Well understood comparing the two excel files adding the status code.
A bit confused regarding the withvlookup file over comparingExcelfiles.
Would you please elaborate a bit.

Thank you very much

Regards
Vishnu

15

Hi @loginerror

Trust you are doing well. Would you please help me how can I loop through multiple excel files for comparing the phone numbers and Amount.

Its unable to write the status code in MKOPA file. Would you please have a look on it.

Thank you so much in advance.MKOPATest.zip (123.2 KB)

Please find the attached project.

Regards
Vishnu

16

Hi @loginerror,

I Solved the issue as per the given directions.

Thanks a lot :slight_smile:

Regards
Vishnu

17

Hi @loginerror

Trust you are doing well.

Would you please help me to add this condition If the Amount is less than 50 write in the column Status Code “Not Ok to Pay” in below IF condition
P_MPESAdt.Select(“[Transaction Details]='”+row.Item(“Phone Number”).ToString.Trim+“’ and [Amount]='”+row.Item(“Amount”).ToString.Trim+“'”).Count =1

Regards
Vishnu

18

Hi,
I am doing flipkart website Data Scrapping. after data received in Excel datatable. i want to filter for pivot the data in sheet2.
I am not able to split/trim or index the required value from sheet1.
Can you guide the same.Mobile_Brandwise.zip (21.9 KB)

image

From Mobile Name: Samsung Galaxy only these words required
From Mobile Price: Remove - ₹ - only want Value.
From Mobile GB: Only Middle word - 64B ROM, don’t want RAM & Expandable

Copy this data to Sheet2 in pivot or filter format to make a chart of these data.

19

@Aparna_Jagtap

Split first column values with Space(" ") then result will be array(for ex… arr).

  arr(0)+" "+arr(1)

Above statement gives you required answer.
For example Your required “Samsung Galaxy J6” as output then split with respect to “(” then result will be array.
array(0) gives you required result.

StringVariable.SubString(1)      Gives you required result

Split the string with respect to “|” then result will be array.

array(1)       will give required result
20

Hello @winningvish,

i need the same solution which you mentioned here… please help me on this…
i want to compare mutiple columns in excel1 and excel 2…and if the column matches update it as ‘ok’ not match if it doesnt match …

Thanks,
Suresh.