Converting "dd.mm.yyyy hh:mm:ss" variable from csv file into datetime variable

Help Studio
, ,
1

Hi there,

I have csv file which I read into a datatable. Now I would need to calculate days between two fields. The problem is that the values in the other column are in this format “17.12.2019 09:01:38”, when I open the csv in excel it shows that the format is “custom”. Any ideas how to convert this to datetime?

Regards,
Mikko

2

Hi Mikko - you can do the convert column and assign to a new variable like below
dtFirstTime = CDate(dtRow(“Date”).ToString)

3

Not sure if I got it.

So you mean I just use assign activity and

  • dtFirstTime = new datatable variable

  • What should I put in to the “dtRow”?

4

@MikkoL Can you show us the Format of Dates in both the Columns ? Also Do you want to perform a Subtraction between Dates?

5

I actually want to add a column “Days” which has the number of days between the field which has the format “17.12.2019 09:01:38” and today.

I’m planning to use DateDiff(DateInterval.Day, “”,“date.today”)

6

@MikkoL Can you show the Value of first row of Date Column using a Message Box after reading the csv file using a For Each Row?
Based on that format you can then use DateTime.ParseExact() to convert it into Date format and then Subtract it.

7

Sure. It shows “17.12.2019 09:01”

8

@MikkoL Can you try the following Steps :

  1. Read Csv File, Get Output as Datatable, say DT.

  2. Add Data Column to DT, say Days

  3. Use For Each Row of DT,
    Inside For Each Row, Use an Assign Activity like below :
    row(“days”) = (DateTime.Now-DateTime.ParseExact(row(“dateCol”).ToString,“dd.MM.yyyy HH:mm”,System.Globalization.CultureInfo.InvariantCulture)).Days.ToString

  4. Write the Datatable to a Excel File using Write Range, The days Column Should be Updated .