Extract value from DataTable when condition is satisfied


#1

Hello friends,
I have a problem in extrcting data from an Excel DataTable.
Here I attach my Excel file containing the DataTable.
Scrapecliente.xlsx (7.6 KB)
I have to loop through all the rows.
If in a row I find a particular amount, for example the amount 104.60 in the column highlighted in grey,

, the Bot should extract the number at the beginning of the row containing 15 digits. (in this case 201890700292475)
Can anyone help me in resolving this issue?
I should finish it in a short time.
Thank you so much friends.
Camilla :slight_smile:


#2

Hi @CamiCat,
As i understand , I saw the excel sheet. If you don’t have the large amount rows. can format the excel by using split by whitespace. And load it as separate sheet or separate file. after that if you read into datatable it very easy to filter the record.

If not possible to format it. you have apply the split functionality each row.
May the array index should be 2 and you can compare.

But my best advice is to format the rows. It will improve the performance also.

May I Know how may rows will have the excel sheet?

Regards
Balamurugan


#3

Thank you @balupad14.
The number of rows will be variable.
Can you give me a possible solution to this problem?
Thank you so much,
Camilla :slight_smile:


#4

yes @CamiCat,
I am formatting your excel file . Give me some minute i will send the solution with source

Regards
Balamurugan


#5

Thank you so much @balupad14.
You are so kind and gentle :slight_smile:


#6

Hi @CamiCat,
Here I have attached the source for the formatting the data.

File :Camicate.zip (17.1 KB)

To find the value you can apply after write range activity.

dtResult.Select(“TransValue=‘104.60’”)

Regards
Balamurugan


#7

Thank you so much @balupad14.
I’ll try and let you know if this solves my problem :slightly_smiling_face:


#8

Yes @CamiCat

The below array is have all the values. But currently i have add only first three column. If you want , you get all the values from the array.

Regards
Balamurugan


#9


Whe I open the workflow I get this error, can you explain me why please?
Camilla.


#10

Hi @CamiCat . it is invoke file. Delete it invoke the file assign variable like below. Again I Have attached the file
It has 2 xml files. Keep all the file in the same folder.

File : SplitColumn.xaml (9.0 KB)
Main.xaml (10.8 KB)
Scrapecliente.xlsx (9.4 KB)

Regards
Balamurugan


#11

HI @CamiCat,
Are you able to run it ? The output is stored in the xl sheet named as “Result.xlsx” . you can check the output.

Regards
Balamurugan


#13

Yes, I’m getting the result.
I should insert an If condition to see wheter the TransValeu is present or not; the value must be equal to another value extracted from an Excel file.
If the TransValue is correct I should extract the Index of the row and find the Sno corresponding.
CAn you please help me again?
Thank you so much,
Camilla


#14

Hi @CamiCat,
Now I have changed source as your condition to fix.

File : Camicate.zip (4.9 KB)

image

Regards
Balamurugan


#15

Thank you so much @balupad14.
I’ll try and let you know as soon as possible.
Camilla :slight_smile:
Your help is precious.


#16

Can I ask you another question?
How is it possible to clear all the data in the Excel file before inserting the information of the next transaction?
Thank you so much @balupad14.
Camilla


#17

Hi @CamiCat,
Can you use the SendHot Keys Ctrl + A and Alt + h +e+a
or try to add new sheet Shit + F11.

Regards
Balamurugan


#18

Hi @CamiCat,

                 It is working :. I tested it.
  1. In the select range have to specify the first cell and last cell.
  2. SendHot Keys Del key.

Regards
Balamurugan


#19

Thank you @balupad14.
Is this one the solution for the problem of deleting all the content of the Excel file?
Thank you so much for your kind and precious help,
Camilla.


#20

@CamiCat
To clear spreadsheet I recommend using the Clear All, because Delete hotkey could leave some hidden formatting. For example, it sometimes will remember the last row of data even though you deleted it… so Append Range will see those empty rows has having something in them, if that makes sense.

to Clear all, it’s in the Ribbon by the key combination Alt,h,e,a
TypeInto "[k(alt)]hea"

So I think that’s the best way, but Delete might work.


#21

Thank you so much @ClaytonM and @balupad14 for the answers.
I think I’ll use the solution: TypeInto “[k(alt)]hea”

I tried it and it is working.
Thank you so much.