Blue Prism Date formatting inconsistencies - save to Excel vs. save to csv

[JeremyRTDean]

Blue Prism Save to Excel produces a different results depending on:

• Data type of source data: Date or Text

• Save as Excel or Excel save as CSV

In summary results are opposite. eg. date format saved to Excel retains dd/mm but text date format changes to mm/dd whereas text date format saved to Excel csv is retained but date format is converted.

To demonstrate, create a collection with 3 data elements:

·         Date

·         Text

·         Text where the year is in yy format (not yyyy)

Export using 3 methods:

• Utility: Append To Text File
• 'Save As CSV' uses a custom object but in essence uses: wb.SaveAs(filename,6):
• Excel: Save Current Workbook as

Results:

Using example date 10/01/2023 (NZ/UK format 10 January 2023)

3 Data elements:

Date Format dd/mm/yyyy           Text Format dd/mm/yyyy            Text Format dd/mm/yy

Append to Text returns:

10/01//2023                                    10/01//2023                                 10/01//2023

Excel returns:

10/01//2023                                    1/10//2023                                 1/10/2023

Excel CSV returns:

1/10/2023                                    10/01//2023                                 10/01//2023

Excel with .CSV extension returns:

10/01//2023                                    1/10//2023                                 1/10/2023

------------------------------
Jeremy Dean
------------------------------

[PvD_SE]

Hi Jeremy,

When embarking on a similar experiment, I only had my process create dates in Date format and in Text format in a CSV. When opening this CSV in XL, it gives mixed results. Well, not really mixed as it actually looks like a genuine mess in XL! The format as well as the delimiter are set by XL without any regard to whatever I decided to set it to be in my process (Date/Text). So I assume the format is taken from XL options, likely combined with Windows Regional and Date settings.

Using yy rather than yyyy as the year format in your CSV data, does indeed make XL seriously mix up the format, providing this: yy/MM/20dd - in short: this is a royal mess!

Upon opening the CSV in NotePad++ however, the results are exactly as I would expect them to be. I get the correct formatting as well as the correct delimiter. As such, I would suggest XL to be the culprit on any form of formatting deviations and delimiter creativity, as it was at least in my test opening the CSV in XL.

In our shop, from early on we had challenges with date formatting in reports and documents. In order to make life a little easier for us developers, and provide a consistent method in data presentation to our business, an object was created that gets the date we need, most often the current date, and formats it to our date standard.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

[JeremyRTDean]

Thanks Paul,

My learning on this one was to go back to basics.  I needed to have a mix of Date fields in format Date and in format Text in an automation and when I started getting different results if the output was Excel or Excel-created CSV it was getting wildly confusing each time I made a tweak and ran a test.

So taking it back to a limited experiment (as above) narrowed the problem and could be tested quickly.

-Jeremy

------------------------------
Jeremy Dean
------------------------------