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,
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
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
yes @CamiCat,
I am formatting your excel file . Give me some minute i will send the solution with source
Regards
Balamurugan
Thank you so much @balupad14.
You are so kind and gentle
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
Thank you so much @balupad14.
I’ll try and let you know if this solves my problem 
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
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
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
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
Hi @CamiCat,
Now I have changed source as your condition to fix.
File : Camicate.zip (4.9 KB)
Regards
Balamurugan
Thank you so much @balupad14.
I’ll try and let you know as soon as possible.
Camilla
Your help is precious.
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
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
Hi @CamiCat,
It is working :. I tested it.
Regards
Balamurugan
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.
@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.
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.
ashokkarale
Anil_G
lrtetala
B_o_D
postwick
shaik.muktharvalli1
marina.dutta
Evelyn_Lu
Ejaz_Ab
ppr