Not able to write Empty Values(DateTime) from Collection to Excel

[vinodchinthakin]

Hi All,

I have a scenario, Where there are some Empty Values are present of a Column(DateTime) in a collection. while writing collection to excel these empty values are replaced by 12:00:00 AM in Excel.

Pls find below screenshots.

It's happening only for Empty values for Columns which are DateTime Format. Do we have any solution to avoid this.?
Note: I am using BP V6.4.1, and Office 365 for Excel

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Hi Vinod,

I will suggest you few approaches where the first two approach deal with making these values null within the collection itself before writing it into excel while the last approach can be used if you have written the data in excel as you are doing now. The last approach works within the excel file itself.

Approach I:

Have you tried to check with column data type as Text instead of DateTime in the collection before writing it to the excel. There are multiple ways to do that as one way can be to create a similar collection with defined fields just keeping the date time field as text type and then using a calculation stage just set the new collection value to old collection.

Approach II:

Another way can be to use a custom action to change the data type of the column to text and then write it to excel for which I think I posted a thread as well while back. 

Let me know in case you can't find that action, I can help you with that.

NOTE: In either of the two approaches if while converting the data type of the collection field, you get unwanted value as "01-01-0001 00:00:00", then you can replace this value with null throughout your collection.


Approach IIII:

Last approach would be to do this in the excel file itself, where you can use a custom action to execute the replace functionality. You just need to extend the 'MS Excel' VBO or 'MS Excel - Extended' VBO by creating a new action called 'Replace' with following input and output arguments:

Add a page reference stage to 'CheckInstanceAndWorkbook' page as shown below:



And now you can use a code stage with following parameters:



Code:

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

 

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

 

Try
Success = True
ws.Range(range).Replace(What:=findString, _
                Replacement:=replaceString, _
                LookAt:=1, _
                SearchOrder:=1, _
                MatchCase:=False, _
				ReplaceFormat:=False)
Catch e As Exception
    Success = False
    Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
End Try​

Now you can simply use an action stage and call this action once published from Process Studio by supplying the workbook name, worksheet name, handle and range along with find string as "00:00:00" and the replace string as ""

It will replace all the cells having the 12:00:00 AM value to null.

NOTE: Ensure that while using any action related to Excel (i.e, Create Instance, Open Workbook, Close Instance) you are using the same business object where you have created this custom action. If not, then you will get a bad handle exception.

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[vinodchinthakin]

Hi Devneet,

Thanks for taking your time. I have Implemented Approach 3 by using an single action Replace from new updated MS Excel VBO found on DX.
In all 3 approaches, we need to replace a particular value either from collection (01-01-0001 00:00:00) or from Excel (12:00:00 AM) with NULL.
Just am wondering is it behavior of Excel/BP where it enters default values of DateTime instead of NULL, or can we do anything within existing code while writing collection to Excel.

​

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Hi Vinod,

Issue is with how VB .NET/C# treats null values in the DateTime data type. Even if it seems to you null, in reality it is not. The reason being is null can be a valid text value but not a date time value. So for date time the null equivalent is "01-01-0001 00:00:00"

This you would need to handle anywhere wherever you want to treat the date time null values as a proper text value be it an external source like excel, CSV or some system or even if you are doing the same within Blue Prism's collection and data items.

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[RonanConsidine]

Hi Vinod,

It might be easier to look at the data you are passing back and the format you are passing it back in for this completed date column.

In your collection before you write to excel is the format dateTime or is it text?
If you were doing this manually would you want the cell to be left blank or would you want it to have INCOMPLETE or N/A in the column?
Lastly, because DateTime is an awkward format to work with, does it need to be used to get use out of the time imformation given or can a string of text be used so that blank values don't generate the 12:00:00 value?

Let me know if you have any more questions

------------------------------
Ronan Considine
Senior Business Analyst
Blue Prism
------------------------------

[Tejaskumar_Darji]

I was facing the same issue so I ended up here on this thread. Whether the collection Data type is Text or DateTime in both cases it changes the null value to something in excel due to auto-formatting.

So I kept the collection as text and added a logic to replace "1/1/0001 12:00:00 AM" with empty in a separate loop before writing to excel.

And after that doing write to excel in resolving this issue. Certainly, it took an extra loop but couldn't find any better approach.

------------------------------
If I was of assistance, please vote for it to be the "Best Answer".

Thanks & Regards,
Tejaskumar Darji
Sr. Consultant-Technical Lead
------------------------------