How to Extract Date from excel file

Help Studio
, ,
1

I am trying to extract a date from excel data but it returns time with zeros like this 1/20/2022 00:00:00 how to remove zero

using assign activity for below expression but getting error
Currentrow(“Payroll_Date”) = Currentrow(“Payroll Date”).ToString.SubString(0,10).ToString.Trim
image

image

2

Hi @Sam_H Convert to this format and then compare

DateTime.ParseExact(Currentrow(“Payroll_Date”).ToString,“M/d/yyyy”, CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)

3

Currentrow(“Payroll Date”).ToString.split(" "c)(0).Trim

4

@Tapan_Behera1

i’m not comparing just extracting it from excel

5

@Gangadhar_Athili

Getting same error

6

getting same error

7

Okay then just use this only: DateTime.ParseExact(Currentrow(“Payroll_Date”).ToString,“M/d/yyyy”, CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)

8

Or you can also use

System.Text.RegularExpressions.Regex.Match(Currentrow(“Payroll_Date”).ToString,“\d{1,2}/\d{1,2}/\d{2,4}”)

\d{1,2}/\d{1,2}/\d{2,4}

9

Hi @Sam_H

Try this, If your want to See your Datetime value as string as same as like your Excel sheet

datetime.ParseExact(Currentrow(“Payroll Date”).tostring,“M/dd/yyyy”,system.Globalization.CultureInfo.InvariantCulture).ToString(“M/dd/yyyy”)

Thanks,
Harivishnu

10

For each row - get row item / assign - CurrentRow(0).ToString should work.
If not, kindly put a write line to see the value and post it here.

11

@Sam_H

Just re enter the expression in the assign activity

In windows legacy, it seems double quote is normalized and any double quote char acer is accepted. However, at this time, in windows (.net6) ,in fact, only " (0x22) is accepted to express string literal. To make matter worse, it seems to occur only compiling. (Expression validator don’t raise an alert.)

Currentrow("Payroll_Date") = Currentrow("Payroll Date").ToString.SubString(0,10).ToString.Trim

Regards
gokul

12

@Gokul001 Getting this Error now
image

13

Hi @Sam_H

Try Like this

Currentrow("Payroll_Date") = CDate(Currentrow("Payroll Date").ToString).Tostring("dd/MM/yyyy")

Regards
gokul

14

can you please check this out i have tried this as well

LOG.xlsx (10.0 KB)

TestFlow.xaml (10.8 KB)

15

Can you tell what do we need to do ? @Sam_H

Share the output also

16

i have to extract the date and compare it to some other excel sheet

17

What data you need to extract? @Sam_H

18

i have to extract date based on this i have to open excel file which is saved in some other folder

19

Check out the XAML file @Sam_H

TestFlow.xaml (16.8 KB)
LOG.xlsx (9.6 KB)

20

I have to extract the only date which is available in logfile and show it in MSGBOx