How to extract the date text from dynamic excel range and compare with current date?

Eggplant Functional
, ,
1

Hello Team,

Conider I have the data as below in my excel.
image

attached example has 10 rows of data. This will change dynamically may be 8 or 15 rows.

From that i need to read the data of column values validFrom and Valid Upto

and compare with current date.

If any of the date is exceed current date logwarning message.

otherwise log success message.

2

I have used image rectangle to read the text.
#put ReadText(iGPM_Excel/Valid_From_TL, iGPM_Excel/Valid_From_BL)as date into validity – failed
put ReadText(iGPM_Excel/Valid_From_TL, iGPM_Excel/Valid_From_BL)as text into validity
split validity by newline|
log validity

output as below:
[“01.01.2017”,“01.01.2017”,“01.10.2019”,“01.10.2019”,“01.01.2020”,“01.01.2020”,“01.09.2021”,“01.09.2021”,“01.01.2018”,“01.01.2018”,“29.02.2024”,“29.02.2024”,“30.11.2023”,“30.11.2023”,“31.01.2024”,“31.01.2024”,“30.12.2025”,“31.12.2023”,“31.12.2023”,“31.12.2023”]

Now How to convert as date and compare with curernt date ?

Expected: The above list contains future date compare to current date.

Another list:
[“01.01.2017”,“01.01.2017”,“01.10.2019”,“01.10.2019”,“01.01.2020”,“01.01.2020”,“01.09.2021”,“01.09.2021”,“01.01.2018”,“01.01.2018”,“29.02.2024”,“29.02.2024”,“30.11.2023”,“30.11.2023”,“31.01.2024”,“31.01.2024”,“30.12.2023”,“31.12.2023”,“31.12.2023”,“31.12.2023”]

Expected : The above list dates are past date.

3

SenseTalk has a built in functions to access Excel file programatically. Have you tried these?

I think those methods are more precise and less error prone than reading the text with OCR.

:warning: These only work if the Excel file is accessible from the Eggplant Functional. If the file resize on the SUT, then the file need to be copied from SUT or place it in a shared folder where both SUT and Eggplant Functional can access.

4

Hey @Thanagaraj,

Absolutely agree with @Keiichi_Yamamoto.
If you can use the excel functionality.
If you cannot for what ever reason:

  • Use the remoteclipboard. Select the text in excel, copy it to clipboard (controlkey, “c”). And put the remoteclipboards content into a variable.
    Most likely you might need to do some formatting in EPF.
## before you need to make sure the mousecursor is placed in the excel file. 
typetext Controlkey, "a" 
typetext controlkey , "c" 
put remoteclipboard(1.5) into SutClipboardContent
put SutClipboardContent

once you have the content with both options you can use this to check your Dates.

## adding european based Date format into sensetalk dataformat defaults
insert "[da].[mo].[year]" before the timeInputFormat ##https://docs.eggplantsoftware.com/studio/stk-global-properties-values/#sensetalk-timeinputformat
put ["01.01.2017","01.01.2017","01.10.2019","01.10.2019","01.01.2020","01.01.2020","01.09.2021","01.09.2021","01.01.2018","01.01.2018","29.02.2024","29.02.2024","30.11.2023","30.11.2023","31.01.2024","31.01.2024","30.12.2025","31.12.2023","31.12.2023","31.12.2023"] into myDateList

repeat for each Mydate in myDateList
	if date(mydate) < date("01.01.2024")
		logsuccess "date is before year 2024" && mydate
	else 
		logwarning "date is after year 2024"&& mydate
	end if
end repeat

Cheers;+,
karsten

5

Thanks Karsten for code snipet.

Compare with current date:
insert “[da].[mo].[year]” before the timeInputFormat ##Local and Global Properties for Working with Values | EPF Docs
put [“01.01.2017”,“01.01.2017”,“01.10.2019”,“01.10.2019”,“01.01.2020”,“01.01.2020”,“01.09.2021”,“01.09.2021”,“01.01.2018”,“01.01.2018”,“29.02.2024”,“29.02.2024”,“30.11.2023”,“30.11.2023”,“31.01.2024”,“31.01.2024”,“30.12.2025”,“31.12.2023”,“31.12.2023”,“31.12.2023”] into myDateList

repeat for each Mydate in myDateList
if date(mydate) < date(date)
logsuccess “date is before year 2024” && mydate
else
logwarning “date is after year 2024”&& mydate
end if
end repeat

1 Like