Invoke Code to convert date format MM/dd/yy h:mm tt to yy-MM-dd hh:mm:ss

Help Studio
1

Afternoon All,

Hoping someone can help me with a date format question. I’m using the invoke code to convert the dates from an excel sheet prior to writing out a delimited file. I’m getting an error on the code.

Input date example: 1/29/21 12:00 AM
I want to output the following format : 2021-01-29 0:00:00

Using Invoke Code - CleanTable.AsEnumerable().ToList().ForEach(Sub(row)row(“Effective Date”)=DateTime.ParseExact(row(“Effective Date”).ToString,“MM/dd/yy hh:mm tt”,System.Globalization.CultureInfo.InvariantCulture).ToString(“yy-MM-dd hh:mm:ss”))

Get error :
Invoke code to convert effective date : Exception has been thrown by the target of an invocation.

Any help would be appreciated.

2

@jsilvestre - Could you please share the sample data?

3

Is row(“Effective Date”) a datetime? If so you just need to do row(“Effective Date”).ToString(“yyyy-MM-dd hh:mm:ss”)

4

Hi!

Input date example: 1/29/21 12:00 AM
I want to output the following format : 2021-01-29 0:00:00

The parse should be:
DateTime.ParseExact(row(“Effective Date”).ToString,“%M/dd/yy h:mm:ss tt”,System.Globalization.CultureInfo.InvariantCulture).ToString(“yy-MM-dd hh:mm:ss”))

Since your input date has 1 in month, instead of 01

5

If row(“Effective Date”) isn’t a datetime, then…

Date.Parse(row(“Effective Date”).ToString).ToString(“yyyy-MM-dd hh:mm:ss”)

6

SampleData.xlsx (10.1 KB)

The posted and effective dates are the columns I’m trying to convert.

7

You have the columns formatted as Date, so you need to just change the column format to what you want.

8

@jorgejogyt,Tried the example but I received the same error during the invoke code.

9

The columns are formatted as Date in the spreadsheet, they’re not text. So you have to change the format in the spreadsheet to what you want.

10

@postwick,

I’m using the excel application scope with the read range to pull in the table. Once inside I want to convert the dates prior to writing a pipe delimited file. The bot will only have read access to the source.

Thank you

11

Is the column in the DT a datetime or string? Either way you’re overcomplicating things using Invoke Code. Just do a For Each Row with assigns to change the format (assuming the columns are strings). Or use the For Each to build the pipe delimited data into one long string, converting the dates as you go, and then write that to the file.

12

it’s a date

13

I’m asking about the datatype of the column in the DT. Is it datetime or string?

14

@jsilvestre -

without using Preserve format, your data will print as OADate

image
image1867×667 54 KB

With Preserver Format, it will print as below…

image
image1272×614 39.4 KB

So, from the OADate we have to convert the it to the required format. I am looking at this…

15

Sorry, I’m using the Read Range from the excel application with the AddHeaders and PreserveFormat options on. I’m not sure but I think that’s a string.

16

So just do a For Each Row to build the pipe delimited string, converting the dates on the fly.

17

@jsilvestre - is this your expected output??

image
image1247×715 37.5 KB

18

yes, that’s the expected result. Do I turn of the preserve format option?

19

Yes…

But hold on…If you don’t use preserve format…other column values will get changed while writing the output…let me think…

20

@prasath17 , Thanks to your help with writing out the data using the Preserve format option, I figured out my issue. I was able to use my original code just updating the format

CleanTable.AsEnumerable().ToList().ForEach(Sub(row)row(“Effective Date”)=DateTime.ParseExact(row(“Effective Date”).ToString,“MM/dd/yyyy 00:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy-MM-dd 00:mm:ss”))