topic RE: Even Out Date Format in Product Forum

Even Out Date Format

Shomesh — Tue, 19 Jul 2022 10:44:00 GMT

Hi, BOT is processing approx. 5 excel files per day. Each file have a column with date field. Date field is not consistent in all the files. Possibility: File 1: Date format - mm/dd/yyyy File 2: Date format - dd/mm/yy File 3: Date format - DD-MM-YYYY File 4: Date format - mm-dd-yy. We are using "FormatDateTime (Date, DateFormat)" to get date in one unique format. But since date field format varies for each file, BOT may produce wrong result or may throw an exception. Is there a way BOT can sense date format from excel file and parse it into desired format without any error?

Thanks.

------------------------------
Shomesh
------------------------------

RE: Even Out Date Format

MichealCharron — Tue, 19 Jul 2022 12:00:00 GMT

@Shomesh

Are the cells, in the date column in each file, formatted as text (with the apostrophe) or are they Excel date cells that have been formatted with a date format?

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Even Out Date Format

MichealCharron — Wed, 20 Jul 2022 03:01:00 GMT

If the cells in the date column are Excel date cells formatted with a date format, there is a relatively easy way to reformat the dates to a format that we can work with because the underlying data is a date serial number. If we take the example data show below:

We can use the "Format Cell" action in the "MS Excel VBO" VBO to format all the dates in column "C" to the format we need. Although the action's name implies that you can only format one cell, you can actually format a range of cells with it. In the "Cell Reference" parameter you can specify "C:C" to format all used cells in the "C" column.

Then use any of the actions to pull the data from Excel into a Blue Prism collection. In this example, we had formatted the dates as "MMM d, yyyy".

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Even Out Date Format

Shomesh — Thu, 21 Jul 2022 12:23:00 GMT

@MichealCharron

Thanks. The approach you suggested works great if the date field in the excel is in Date format rather if the field is text format how can I proceed that.

------------------------------
Shomesh
------------------------------

RE: Even Out Date Format

RonanConsidine — Fri, 22 Jul 2022 15:06:00 GMT

Hi Shomesh,

For that situation you can always convert text into date format using the Convert date function in a calculation.

Then if the date isn't in the right order you can use the FormatDate function as well to put it in the right format.

You can use the above functions in tandem as well to do them at the same time in the picture below.

Hope this helps

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

RE: Even Out Date Format

MichealCharron — Fri, 22 Jul 2022 19:05:00 GMT

@Shomesh

Darn, I was hoping you weren't going to ask me that. The method I would use involves extracting Excel data using Blue Prism's "Data - OLEDB" VBO (which I use where there is a complicated Excel extraction scenario) and Blue Prism's "Utility - Strings" VBO to test what formats we are dealing with. Bear with me on this one because there is a fair amount of the Alphabet to go through.

Aside: We try to format our processes vertically but I am using what our group calls the "caribou" format so that it will fit easily into an image.

(A) First off, we set up the Provider string in a data item with the following string to use the with the ACE driver (free with the MS Access Database Engine Redistributable if it isn't already installed):

Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0;HDR=YES;READONLY=TRUE"

Version numbers will vary depending on the version of the ACE driver you have and the version of Excel

(B) Use the "Set Connection" action in the "Data - OLEDB" VBO to connect the OLEDB to the Excel (in this case, it is my example in the previous reply):

The Database parameter is the full path to your Excel file. The provider parameter was that data item we set up in (A).

(C) Use the "Get Collection" action in the "Data - OLEDB" VBO to quickly grab a sample of the current date format in the source Excel:

The SQL selects the first value within the "Column Date" column in the worksheet named "Data" (the FROM uses the worksheet name with a "$" appended to it).

(D) We now have a sample format to look at and determine which format is used for dates in the Excel.

(E) Luckily, the four formats that you listed each have their own unique format that makes it easy to determine which format has month and day in which position in the string. We can determine which format we have to work with by calling the "Test Regex Match" action in the "Utility - Strings" VBO up to four times:

We run the regex patterns in each of the "Test Regex Match" actions against the value in the "Target String" parameter the we retrieved from the Excel in (C) and stored in (D). The four "Test Regex Match" actions are exactly the same except for the "Regex Pattern" parameter that needs to test the four different date formats.

Date Format	Regex Pattern
MM/dd/yyyy	"\d{2}/\d{2}/\d{4}$"
dd/MM/yy	"\d{2}/\d{2}/\d{2}$"
dd-MM-yyyy	"\d{2}-\d{2}-\d{4}$"
MM-dd-yy	"\d{2}-\d{2}-\d{2}$"

(F) The testing for regular expression matches (E) always returns a True or False into the "Matched?" data item.

(G) The "Format Match?" decisions branch off depending whether the current value of the "Matched?" data item (F) is True or False.

(H) An exception stage is thrown in for good measure if none of the format's match.

(I) I'm going to assume, by your question, that your standard date format does not start with the year first. Bearing that in mind, two of the four formats can be directly converted into the system's date format and the other two cannot. In my case having the day first will through off direct date conversion so for those date formats, I want to create an expression to place into my next SQL query to reformat the string so that it can convert directly. For this, I can use the "Date Reformat" calculation stage to format a string as an ACE SQL expression:

The expression string will take the middle two numbers, add a "/", add the first two numbers, add a "/" and then add the last two number of the year.

"MID([Column Date],4,2)+""/""+LEFT([Column Date],2)+""/""+RIGHT([Column Date],2)"

(J) If you are looking for the (J) step in the process image, there isn't one. I missed it when inserting the letters and I'm not going to go back and change the process image.

(K) Since the second set of date formats can be directly converted into a date, I use the "Date Pass-Thru" calculation stage to create a string with the name of the date column in the Excel:

(L) The "Reformat Date Formula" data item now holds either an expression for switching the numbers around in the Excel date column or just the column name in the Excel date column.

(M) Here is where the magic starts to happen. I've set up a data item that contains a SQL query that will retrieve all the columns in the worksheet "Data" in the Excel but also has a formula and placeholder for the date column to convert it into a date and then format it in any date format we want (in this case "MMM d, yyyy"):

SELECT [Column 1]
, [Column 2]
, FORMAT(CDATE(@DateReformat),"MMM d, yyyy") AS [Column Date]
, [Column 4] 
FROM [Data$]

This takes whatever is put in place of the place holder "@DateReformat", converts it to a date using the function "CDATE" and then take that date and format it as "MMM d, yyyy"

(N) Here is where the magic ends. Using the "Get Collection" action from the "Data - OLEDB" VBO again, we do a quick replace of the "@DateReformat" placeholder we have in the "SQL Query" data item (M) with the string we have in the "Reformat Date Formula" data item (L) before we pass it into the "SQL" parameter:

(O) What we end up with is all the data in the Excel along with reformatted date data being brought directly into your Blue Prism collection:

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------