String was not recognised as a valid time error in Build Data table

Learn Academy Feedback
#1

Hello,
I have a excel which has a column of Dates. I’m creating a new data table using build data table activity.
I’m reading the datecolumn in a string variable like strvar = row.item(“Value Date”).ToString
Again I’m converting that string to datetime using following code:
DateTimevar=Datetime.ParseExact(strvar,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)
Here I’m getting error as ‘String was not recognised as a valid datetime’.
Could anyone please help me with this

#2

What was the string you tried to convert that threw the error? Your code is expecting it to be in this exact format: dd/MM/yyyy if it is in any other format, your code will throw an error.

#3

My String was exactly in dd/MM/yyyy format it is example (31/07/2020
)

#4

Hi @Sneha2369

Can you provide the Excel so that working on it will be easy

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

#5

The error is stating that the string was NOT in the format. Is it possible there are whitespaces before or after the digits? What happens if you use a write line for each string during the code before attempting to convert? I have a feeling you will help you troubleshoot and find the error.

#6

DateFormat.xlsx (10.5 KB)

#7

Please find the attachment I have blank cells in middle is it because of that

#8

Hi @Sneha2369

Try the below statement once :-

DateTime.ParseExact(CDate(strvar).ToString("dd/MM/yyyy"),"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture)

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

#9

When I use write line 07/31/2020 00:00:00 i’m getting this format

#10

Hi @Sneha2369

So may i know that the new Datatable that you are creating that will only contains the rows who have Dates value in Column “Value Date” ??

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

#11

Hi @Sneha2369

So in the same statement just add as below :-

DateTime.ParseExact(CDate(strvar).ToString("dd/MM/yyyy"),"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")

Or another way is just do like below :-

CDate(strvar).ToString("dd/MM/yyyy")

This both way will eliminate the timing part at the end of the date

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

#12

Conversion from string"" to type ‘Date’ is not valid this is the error

#13

I have one date column to which I gave type as System.DateTime

#14

This error means there is an error in your input file. It is trying to convert an empty string into a date.

I would caution against using CDate as it may cause unexpected and uncaught errors if your data types are changing (e.g. switching from dd/MM/yyyy to MM/dd/yyyy, etc)

#15

Hi @Sneha2369

Have you tried what i have given ??

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

#16

yes…This is the error what I got
‘Conversion from string"" to type ‘Date’ is not valid this is the error’

#17

I have some empty Cells in the column.Is it because of that?

#18

Hi @Sneha2369

So bcz of the blank spaces in the “Value Date” Column it is giving you that error
image

May i know do you want the same Data in New Datatable ??

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

#19

Final output should be like thisFinaloutput

#20

if there is a blank cell it should fill the cell with above value… I have tried for this but it is filling the cell in 07/31/2020 00:00:00 format