Ienumerable match to datatable as a date type help

Hi Guys,

Struggling with this one.
Reading a few pdfs using regex, pulling various values, one such is the date.
Im getting all the values required then adding to a datatable then finally writing to Excel.
All the dt colums are strings.
All works perfectly, however in Excel I cannot perform calcs on dates as they are strings.

Where im coming undone is changing the date column in the dt from string to date plus converting the matches output to a date type.
Here goes,
Result5 is the var out from matches.

Ive tried converting the Result5(0) to a new date var
var_datetime = Datetime.ParseExact(Result5(0).tostring," dd/MM/yyyy ",System.Globalization.CultureInfo.InvariantCulture)

This is the add data row within a for each (iterating through the pdfs).
{Result1(0).tostring,Result2(0).tostring,Result3(0).tostring,Result4(0).tostring,var_datetime.tostring,Result6(0).tostring,Result7(0).tostring,Result8(0).tostring,Result9(0).tostring}
I couldnt remove the .ToString after the var_datetime as I was getting an error.

Below is a snippet from the error…
System.FormatException: String '02/05/2003
’ was not recognized as a valid DateTime. at System.DateTime.ParseExact(String s, String

Cheers guys :slightly_smiling_face:

Hello
Try moving the ToString at the end of:
var_datetime = Datetime.ParseExact(Result5(0), “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString

What do you have your var_datetime variable set to?

Regards

Hi,

var_datetime variable is set to System.DateTime

Tried adding the .tostring at the end but its showing an error.

Thanks

@MikeC
Have you tried putting the data into the Excel then using Format Cells activity to change the range to Date format?

:joy: :joy: :joy: :joy: YES first thing I tried lol, im a keep it simple stupid guy…
As you can see it works fine but dates align left, seen as text so cant perform calcs.

Screenshot 2023-01-19 204501

@MikeC
This is how I did it and it worked:
image
Built Data Table with some examples you provided in strings like 02/05/2023
Then added a Add Data Column and name it whatever
image
Loop over the Data Table
Assign CurrentRow.Item(“New Dates”) = DateTime.ParseExact(CurrentRow.Item(“Date”).ToString, “dd/MM/yyyy”, Globalization.CultureInfo.InvariantCulture)
Then Write Range Data Table to Excel
image
Then you can Remove the old Column of dates

Regards

1 Like

GGGGOOOODDDDDDAAAMMMIIITTT !!!

Sorry buddy, yet another schoolboy error on my part, I forgot to trim the regex output.
4 bloody hours messing around with code :crazy_face:
Works fine after I trimmed it

My bad, thank you, sorry for wasting your time :joy:

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