CODEWITHSUNDEEP
c#exceldatetimedatatableblueprism
Mr.Robot
Mr.Robot

Reputation: 426

BluePrism Reading data from excel datetime shifts

KeyWords: DataTable, DateTime, BluePrism
Situation: Reading data from excel file into datatable, datetime shifts.
Example: I have a simple excel file which contains table below:

ID | Date
1    03-07-2015
2    07-05-1998
3    11-11-1987

After reading excel file into c# DataTable using this method (Read xls file to Import to DataTable ) I get the following table:

ID | Date
1    2/7/2015 9:00:00 PM
2    6/5/1998 9:00:00 PM
3    10/11/1987 10:00:00 PM

What i've tried:

  1. Reading excel file differently (no luck);
  2. Finding out DataTable.Locale. When I run it on my c# console, locale is my computer's locale - da-DK. However, when I run the same function in code stage on Blue Prism, locale is changed to en-US. I've tried changing it to da-DK in BluePrism, but datetime parsing is not impacted.

When I import excel using only c#, it works fine. However, it does not when I run exact same code on Blue Prism. I understand that when string is being converted into Datetime, it is set as date 00:00:00 <PM|AM>. Why along the way subtraction happens is beyond me.

Upvotes: 1

Views: 2919

Answers (2)

Jong jong Oclarit
Jong jong Oclarit

Reputation: 1

I had the same issue but it was solve using CStr function, it retains the original value from the excel file.

Example: 

Select CStr([Start Pay period]) as [Start Pay Period] From [Worksheet$]

Upvotes: 0

Vesnog
Vesnog

Reputation: 785

I presume you are running the Blue Prism object on a computer with the local time set to UTC +3. Blue Prism always converts datetime variables retrieved via various channels into UTC values; therefore it subtracts 3 hours from the original entries. These channels may be MS Excel, SQL query, text file etc.

It becomes especially problematic if you store a datetime variable in a file use it store it back and call it again. Then Blue Prism will perform consecutive subtractions. The best way to deal with this is add LocalTime() - UTCTime() to every datetime item at the moment you retrieve them.

Upvotes: 1

Related Questions