TAP Studio - How to parse a custom excel date format without errors
Hello once again and welcome to a new tutorial about RPA done in TAP Studio the Tailent IDE in which you can build magnificent robots to ease your life, improve the productivity of your business and make your employees to actually enjoy their job for once.
I have decided to write this tutorial because recently I had to write a robot and a part of its work was to parse a custom date format from an excel file and add it into a variable.
Easy enough, right? Well unfortunately an error had to ruin the day:
“String was not recognized as a valid DateTime.”
Well, it is pretty cut and dry when we read the error message, but how do you fix it?
Let’s explore together the wonder of how to parse a custom date format such as this one - [$-en-US]yyyy-mm-dd hh:mm:ss - while avoiding any errors that would give you headaches for days.
First, let’s say our Excel table would look like this:
Two columns, one filled with names, one filled with dates.
Let us add a new action of Open Excel Workbook with the excel document you wish to open and the path to it.
Note that you have to see the variable of the workbook as excelDocument as shown in the picture below
After adding the Open Excel Workbook Action drag a C# script as well under it and generate a Code Starter. Right after this add the following library at the top: using OfficeOpenXml;
Here we need to read that variable we created with the type of excelWorkbook by using the following line of code:
ExcelWorksheet worksheet = Tutorial.getWorkSheet(1);
I named mine ”Tutorial”, for obvious reasons.
After this, if we want to read all the lines, we need to get the last row of that excel sheet and create a for loop. The code would look like this:
int RowsCount = worksheet.Dimension.End.Row;
for (int i=3;i<=RowsCount;i++)
{
}
Inside this loop we have to read each cell from the desired column.
The big problem arises here.
You can read the name column, alongsite most of the datatypes the cells can have in excel by using:
string name = worksheet.Cells[i, 1].GetValue<string>();
Unfortunately, this does not work for the custom format type. If you try to to parse the 2nd column by using the same method as above, you get the error:
“String was not recognized as a valid DateTime.”
The code would look like this:
DateTime mydate = DateTime.Parse(worksheet.Cells[i, 4].GetValue<string>());
In order to avoid this happening you would have to parse it as a long by using “long.Parse” with a 2nd variable and cast it to string:
long dateNum = long.Parse(worksheet.Cells[i, 2].Value.ToString());
After the variable was stored in the variable dateNum you can use it in the variable “mydate” by using FromOADate.
DateTime mydate = DateTime.FromOADate(dateNum);
You can find out mode about FromOADate here: https://docs.microsoft.com/en-us/dotnet/api/system.datetime.fromoadate?view=net-5.0
In order to verify the date was parsed successfully, you can use a MessageBox but note that you have to specify a format to be displayed.
This would be an example:
MessageBox.Show(mydate.ToString("dd/MMM/yy"));
Your c# code window should look like this:
If everything went right, you will be met with a Message Box containing the date you parsed.
I hope this helps you in building the robots you desire in TAP Studio and I will see you in the next tutorial!