Extract Date from Excel Document and Insert it on a Website

Help Studio
,
1

Good day,

I am reading a date value from an Excel spreadsheet in the following format: YYYY/MM/DD
image

I want to input the date in the same format (YYY/MM/DD) on a website.
image

I am using “Type Into” to populate the field.
image

When I run the program, this is the result that I get:
image

I have tried using DateTime.ParseExact and Convert.ToDateTime, but these cause incompatible data type errors.

Any assistance or guidance would be greatly appreciated!

2

Hi @Ivan_Potgieter

Have you tried with Click Before typing and Empty field in the properties of Type into?

1 Like
3

Hi @Gokul001,

Thank you for your response.

Both “Click before typing” and “Empty field before typing” are enabled. I suspect the issue might be resolved by changing the format of the result of the “CurrentRow.Item(2).ToString”, but I am unable to change the format without incurring errors.

1 Like
4

Try to mention like this in type into activity

Datetime.ParseExact(CurrentRow(1).ToString.Trim.SubString(0,10), “yyyy/MM/dd”, System.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy/MM/dd hh:mm”)

Cheers @Ivan_Potgieter

1 Like
5

@Ivan_Potgieter

Try with parse expression

DateTime.ParseExact(CurrentRow.Item(2).ToString"yyyy/MM/dd hh:mm",System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd")

You can change the format here as per the needing CurrentRow.Item(2).ToString"yyyy/MM/dd hh:mm"

Regards
Gokul

1 Like
6

Thank you for your response.

I added the code you provided into the “Type into activity”. Unfortunately, when I run the program, it still inserts the values in the same format as before. It is as if the format is not getting updated as it should.

image

7

Well is that field allowing to type in value manually @Ivan_Potgieter

8

@Palaniyappan When I type the date into the field manually it works without any problems.
image

However, I have noticed that if I don’t click on the year, month and day separately when filling in the field, the year ends up taking on 6 digits, meaning it takes the values of the month as well.
image

I have also noticed that if I add two 0’s at the front of the date and 4 0’s at the back, the automation runs fine.
image

9

I added a message box to output the result of “CurrentRow.Item(1).ToString”.

This is the result:
image

The odd thing is, the format is in dd/MM/yyyy hh:mm:ss and not yyyy/MM/dd.

When I try to output the result of: DateTime.ParseExact(CurrentRow.Item(1).ToString(), “yyyy/MM/dd hh:mm”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy hh:mm:ss”)

I get this error:
“Message Box: String ‘11/20/2016 00:00:00’ was not recognized as a valid DateTime.”

10

I will tell you simple alternative, use Regex, to identify first 2 digits, then second set of 2 digits, and then last four digits (used forward slash essentially in Regex). Store it as month, date, year. And then create another variable fulldate (String), and three variable in it, in the pattern you want. This will resolve your issue, if you need more details on regex, etc let me know.

11

“00” + DateTime.ParseExact(CurrentRow.Item(1).ToString, “MM/dd/yyyy HH:mm:ss”, System.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy/MM/ddHH:mm”) finally fixed the issue :grin:

Thank you to everyone for all your help!

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.