CSV into Excel - date and time fields need formatting

Help Studio
,
#1

I want to append into excel, data from CSV. But, (mostly) the date and time fields need formatting

CSV_data
CSV_data1440×860 90.8 KB

As you can see from the image, the data on row 40688 is correct (greek format) and the data on row 40689 don’t have the correct format.
For instance, the cell M 40689 need to change from “3/17/2021 8:51:00 PM” to “17/3/2021 8:51:00 μ.μ.”.

Any ideas?

#2

Hi @iplagian
Welcome to UiPath Community !
Well maybe you could try the following:

  • first read range the datatable (DT)
  • then use foreach row activity to loop in the DT
  • inside the foreach row, assign a string, let’s call it date_string, like that: date_string = row(“ASSIGNMENTFINISH”).toString
  • then put IF activity, and the condition would be:
    not date_string.contains(“μ”)
  • then create a DateTime in your variables, let’s say date_datetime, like that:
    image
  • use assign activity: date_time = DateTime.ParseExact(date_string, “dd/MM/yyyy HH:mm:ss tt”, None)
  • use assign activity: date_string = date_time.toStrng(“G”,CultureInfo.CreateSpecificCulture(“ell-el”))
  • use assign activity: row(“ASSIGNMENTFINISH”) = date_string

Let us know if it works, I did not try it

#3

Hello @Hiba_B
Thank you for your reply.
When I try to assign the date_time to date_datatime using “None” (date_time = DateTime.ParseExact(date_string, “dd/MM/yyyy HH:mm:ss tt”, None)), there is error.
So, I changed “None” to “System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat”.
But still there is error: when date_string has the value “03/22/2021 12:17:00” and I tried the above, the error was: “String was not recognized as a valid DateTime.”
Any ideas?

#4

Hi @iplagian
can u append the date in format of text ?

#5

Alright, then you can remove the 3rd argument (it’s optional).
The problem is that the value 03/22/2021 12:17:00 doesn’t have μ nor PM/AM. So for the form dd/MM/yyyy HH:mm:ss tt the robot does not recognize the tt (which is supposed to be AM or PM).
Ok so the solution might me to use the try catch activity:

  • in the try, drag&drop date_time = DateTime.ParseExact(date_string, “dd/MM/yyyy HH:mm:ss tt”)
  • in the catch, add a new catch with the exception System.Exception. Inside, put an assign activity with date_time = DateTime.ParseExact(date_string, “dd/MM/yyyy HH:mm:ss")

Give it a try and let us know ! Especially if there are other date forms expected in the column :joy:

#6

@Hiba_B, what I did: I read the CSV range, then I write it into an intermediate excel file. It looks like this (see attached file)

ExcelDates
ExcelDates1448×900 41 KB

The information exists, but it doesn’t appear properly. There are no other date formats. I’ll try what you suggest.
@NIVED_NAMBIAR I’ll check it and I’ll tell you.

#7

The fact that it does not have 2 characters for dd or MM or HH is alright, it still works with the given format

#8

@Hiba_B The format “dd/MM/yyyy hh:mm” is alright in the cell, but it doesn’t display the “ss tt” at the end, although it exists (above, in the function line of excel).

#9

You’re right. But if I remember the robot reads the whole form with ss tt, could you check ?

#10

@Hiba_B @NIVED_NAMBIAR
I’m quite close.
I assigned date_String=Datetime.ParseExact(row(“ASSIGNMENTFINISH”).ToString,“MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.CreateSpecificCulture(“el-GR”)).ToString(“dd/MM/yyyy HH:mm:ss tt”)
Although I use CreateSpecificCulture(“el-GR”) (with “ell-el” there was error), date_String has the value of “16/03/2021 09:35:00 AM” and not “16/03/2021 09:35:00 π.μ.” as I would like.
Any ideas?

#11

Hi @iplagian

i think the issue is u had provided dd/MM/yyyy HH:mm:ss tt as string format to be obtained

i don’t whether it would be exact but try this out

Datetime.ParseExact(“02/03/2021 16:40:45”,“MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy HH:mm:ss π.μ ”)

#12

Hi @NIVED_NAMBIAR
What about the case where it’s PM (in greek “μ.μ.”)? I don’t think it would be good to hardcode it.

#13

In the expression of @NIVED_NAMBIAR replace π.μ by tt

#14

Also, date_String=Datetime.ParseExact(row(“ASSIGNMENTFINISH”).ToString,“MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.CreateSpecificCulture(“el-GR”)).ToString(“dd/MM/yyyy HH:mm:ss tt”,System.Globalization.CultureInfo.CreateSpecificCulture(“el-GR”))

You have to precise in the .tostring() the wanted format, otherwise by default if will be the english way

1 Like
#15

Very nice @Hiba_B!
The result is very good: “17/03/2021 12:20:00 μμ”.
The only thing is that the dots are missing, e.g. “17/03/2021 12:20:00 μ.μ.”.
I tried with dots in “t.t.”, and although the “μ.μ.” is ok, the “πμ” becomes “π.π.”.
I have no clue why.

#16

Oh it’s alright, in the second system.globalization you have to add the point:
date_String=Datetime.ParseExact(row(“ASSIGNMENTFINISH”).ToString,“MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.CreateSpecificCulture(“el-GR”)).ToString(“dd/MM/yyyy HH:mm:ss t.t”,System.Globalization.CultureInfo.CreateSpecificCulture(“el-GR”))

Ah sorry I just read completely what you wrote; I am thinking into a way

#17

Ok what if you add only a point before tt like “.tt” what does happen ?

#18

@Hiba_B, it just add a point before, e.g. “.πμ” or “.μμ”.

#19

Hmm if you remove the point (like keep it tt) do you confirm that “πμ” stays “πμ” ?

#20

@Hiba_B, yes that’s true. If I leave it “tt” then only apears “πμ” or “μμ”.
But in this case the dots are missing. I also tried “t.t” (with only 1 dot, so I could add the last dot afterwards), but still “πμ” becomes “π.π”, which is wrong.