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
------------------------------
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.
------------------------------
Ronan Considine
Senior Business Analyst
Blue Prism
------------------------------
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.
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.
(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
------------------------------
