Find & GetText from Excel Files


#1

GetText from Excel.xaml (9.6 KB)
Hi all.
@rahatadi ^^

I want to find image and Get Number from Excel File as attached and below picture.

I attached 2 excel files… the number which I want to get from these 2 excel files at row 69 ( 1st file ) and 68 ( 2nd file ).

How to do that ??

Thanks you so much!
1.xlsx (20.8 KB)
2.xlsx (21.8 KB)


#2

Hi Let me understand your scenario.

you want to copy the number which is marked in yellow colour cell right ?
which is always beside to the green cell?


#3

@prasanna.Ui

Yes, I want to copy the number which marked in yellow based information marked green cell.

Because excel files have same format but the row not fix, in 1st excel file is row 69 but in 2nd file is row 68. So I want to use Find Image Activity to find the text which marked Green and then Get the number to copy into another excel file.

Thanks you!


#4

Hi @hadoanpapvn
You can get this using the activity Read cell.
Thanks & Regards


#5

hi hadoanpapvn,

find the attached file.check the text in green colour cell is reading
Get Text Based On Colour.xaml (11.8 KB)

I hope it will help you.


#6

@prasanna.Ui

Thanks you so much but mean not that.

I highlighted with colour ( Green + Yellow ) to see details which I want to say.

I want to Extract Number at H69 1st file and H68 2nd file base on the fixed text " V Thuế GTGT " which is highlighted with Green :slight_smile:


#7

hello @hadoanpapvn try below
dt.select("[ColumnD] Like ‘%V Thuế GTGT%’")(0)(“ColumnHName”).ToString
change the column name at “ColumnD” and “ColumnH” accordingly.

assumption: value “V Thuế GTGT” does not exist anywhere else in ColumnD


#8

hi @AkshaySandhu

Thanks you but could you pls do example for this case ?


#9

while reading excel file do not add headers and then log this…
dt.select("[Column3] Like ‘%GTGT%’")(0)(“Column7").ToString()


#10

hi @AkshaySandhu

Could you pls do example for this same with @prasanna.Ui has posted in previously post ? :slight_smile:


#11

Hello @hadoanpapvn
I dont think you require xaml for this… just follow below steps

  1. Use read range activity to read the excel file, uncheck the Add Header property and then output it to a variable named dt
  2. Log a message with dt.select("[Column3] Like ‘%GTGT%’")(0)(“Column7").ToString()

this should work for you… just give it a try :slight_smile:
if it does not work, just upload your xaml we will correct it…


#12

Hi @AkshaySandhu

Pls kindly check as attached file and below picture.
GetText from Excel.xaml (8.4 KB)


#13

you are almost done…
try writing the code manually…cause copy pasting code from forum somtimes changes characters

attached file for reference
Main.xaml (7.5 KB)


#14

hi @AkshaySandhu

Done, thanks you so much.

But I have another question… I have many same excel files. I want to extract number all of files not only on one file and then write to a another excel file. How to do that ?

Your way seems do on one file only.


#15

my code will work for all the excel file as far as the format of all the excel file is same… in this case number of columns…
I guess your excel file is a result of export from PDF or Word file…(if possible please confirm)…


#16

hi @AkshaySandhu

Your code is correct but I want extract number from many excel files at the same time ( keep in same directory ) . It will extract one by one :slight_smile:

I think must be use more For Each activity to it read all of excel files which stored in same directory.

how to do that ?

Thanks you!


#17

yes you are correct we will need foreach loop

in foreach loop: Directory.GetFiles(“C:\Temp1”,"*.xlsx")
and move whole sequence inside this foreach activity,
in read range use item.ToString


#18

Hi @AkshaySandhu

I don’t know how to combine some activity as you said.

Could you pls guide me step by step?

I want to extract and then write into another excel file.

Thanks you so much!


#19

Main.xaml (8.9 KB)
just add append to csv or append range after the log message


#20

Hi @AkshaySandhu

Thanks you… I understood.

But when write data into excel file, use append range activity… Might we use Add Data Row before Append Range Activity ?