How to get the variable of date using assign activity

Help
1

Dear all,

i am currently assigning each column header to be a variable.

image
image1366Ă—768 97.4 KB

How can i assign for the variable for month?

i cannot get the date format same as the excel file. (d/m/yyyy)

i use the for each row to loop for all the rows then use the write line activity to get the row items that fulfill the condition.

Thank you.

Best regards,

Hong

2

Hello Eelin,

It sounds like you’re trying to scrape an excel sheet for the dates, is that correct?

It sounds like you’re doing it correctly, likely doing a read range, grabbing all of the data out and exporting it to a datatable, and then for each row to check it. When you get data out of an excel sheet, the problem is that it doesn’t know that a date is a date, it thinks it’s just a normal string. You will have to parse the string in order to convert to a datetime object or simply to get out the month if that’s all you need.

As a way to figure it out, I would suggest creating a new sequence and assign one of the string dates to a variable and try parsing just the one without dealing with the excel sheet. That way you can ensure that you’re able to process dates how you’d expect and you can then use what you learned in your automation.

Edit: I would suggest trying to figure it out on your own and reading up on VB’s DateTime object, but, if you just want the answer, here’s a sample of a way to do it: Test_StringToDate.xaml (4.8 KB)

3

Seems like you are getting the output but the format of date you are getting date is wrong. Am I correct

4

Buddy @eelin

You were almost done buddy

you can get the date time type for this
Try like this to convert tgis Value to a datetime
Once reading with for each row loop assign the the value of the column month to

Out_date = Datetime.ParseExact(yourcolumnnamevariable.ToString,“d/M/yyyy”, System.Globalization.CultureInfo.InvariantCulture)

Its caps M buddy not small case m for month

Cheers buddy hi

5

@dmccammond

Below is the pic for my workflow

image
image1366Ă—768 84 KB

i have create the variable, Month (type of string) & parsedDate (type of DateTime).

what i want to do is i want to have a condition whereby for eg.

To get the row item that fulfill (AccName= “TRAVEL-TRIP” and Month= " 1/1/2019")

image
image1366Ă—768 84.6 KB

How can i get that?

Kindly advise me.

Thank you

6

@eelin
When you scrape data, the date format will be returned as string. Use Log and check in which format date column is saved in datatable.
Instead of using foreach loop and declaring multiple variables, use filter data table activity by specifying the required column and the date in the format that you are receiving in datatable.
hope this helps!

7

Hi Buddy @eelin

  1. Before going for if condition using an assign activity create a variable named Date_Condition with datatime datatype to store the value of condition 1/1/2019
    Like
    Date_Condition = Convert.Datetime(“1/1/2019”,“d/M/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

  2. Write if condition like this buddy

Datetime.ParseExact(row(“Month”),“d/M/yyyy”,System.Globalization.CultureInfo.InvariantCulture) = Date_Condition and AccName= “TRAVEL-TRIP”

This would work buddy

Cheers

8

Is that working buddy @eelin

Cheers

9

Dear @Palaniyappan,

Thanks for your reply.

However, i would like to assign the Month= row.Item(0).ToString

I need to get the data for the month.

How can i actually assign the variable “Month” to get the date and then make it can read it as date format, then with some condition, i need to use write line activity to get some output.

Besides, i need to get the data from different month.

The condition will be as below:-
row item that fulfill (AccName= “TRAVEL-TRIP” and Month= " 1/1/2019")

Kindly advise me how to do that.

Thank you.

10

Buddy @eelin

  1. to get the data for the month using for each row loop to iterate throught the row and use a assign activity like below buddy
    Month = Datetime.ParseExact(row.item(0).ToString,“d/M/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

Where Month is of type Datetime

  1. Inorder to mention that in writeline activity write like
    Month.Tostring

  2. write condition like
    AccName = “TRAVEL-TRIP” and Month = Conver.Datetime(“1/1/2019”)

Hope this would work buddy
Cheers buddy

11

i have tried but it has the error.

image
image1366Ă—768 93.3 KB

Kindly advise me is there anything wrong

Thank you.

1 Like
12

Its convert.Todatetime buddy not convert.datetime…

Cheers

13

@Palaniyappan

I have edit it.

However, when i run the workflow, there is error message as per below:-

image
image1366Ă—768 90.7 KB

Thank you.

14

Buddy may i see what you have assigned on left side of the assign activity

Cheers

15

Main.xaml (11.8 KB)

i attached my workflow here.

Thank you

16

Buddy here is the workflow, you were right actually, made few changes

hope got resolved

Main (2).xaml (11.9 KB)

Cheers buddy kindly let know whether this works or not else will tweak the code to get this resolved

17

image
image1366Ă—768 74.4 KB

Thanks for sending me the workflow but there is no way for me to get it.

Kindly refer to the pic

Thank you.

Best regards,

Hong

18

@Palaniyappan

What changes have you perform?

Thank you

19

Will send you again Buddy…sorry buddy forgot…bad memory :slight_smile: .no worries…will send back again…

Cheers

20

ok noted.

Thank you