LogoLogo
  • Welcome to Tailent Automation Platform documentation
    • Start for Free
  • Getting Started
    • State Machine Approach
    • TAP Studio
      • How to install TAP Studio
      • TAP Studio Overview
      • Variables and Data Types
      • Transitions
        • Normal transitions
        • Exception transitions
      • How to Create a New Automation Project in TAP Studio
      • How to add an Activity to Favorites
      • How to Open an Existing Automation Project in TAP Studio
      • How to Save an Automation Project
      • Control Identifiers in UI Browser
      • List of Activities that use Control Identifier as Input Parameter
      • Press Key - Values of Key parameter
      • Outlook vs standard IMAP protocol
      • Hardware & Software Requirements TAP Studio
    • TAP Assistant
      • Basic Scheduling
      • Setting Custom Log Sizes
    • TAP Frequenty Asked Questions
  • Actions
    • TAP Actions
      • CSV
        • Append CSV
        • Read CSV
        • Write CSV
      • Application
        • Kill Process
        • Start Process
        • Wait for Control
      • Clipboard
        • Get Clipboard Text
        • Set Clipboard Text
      • Control
        • If Control Exists
        • Wait for Control to Disappear
        • Check Control
        • Select List Item
        • Set Focus
        • Set Text
        • Get Value
        • Get Native Value
        • Get Window State
        • Set Window State
      • Custom
        • C# Script
      • Data
        • JSON Deserialization
      • Database
        • Connect To Database
        • Database Query
        • Database Non Query
        • Disconnect From Database
      • Diagram
        • Invoke Workflow
        • Sequence
      • Direct text
        • Click Text
        • Extract Direct Text
      • Excel
        • Write Table
        • Set Range Color
        • Rename Sheet
        • Read Range
        • Read Cell Formula
        • Insert Rows
        • Insert Columns
        • Format Range
        • Fill Rows
        • Fill Columns
        • Delete Sheet
        • Delete Rows
        • Delete Columns
        • Create Sheet
        • Clear Range
        • Open Excel WorkBook
        • Close Excel WorkBook
        • Get Cell Value
        • Set Cell Value
      • Flow
        • Assign Value
        • Show Message Box
        • Wait
      • Loging
        • Log to FIle
        • Write to Console
      • IO
        • Copy File
        • Create Directory
        • Create File
        • Delete Directory
        • Delete File
        • Move File
        • Read Text File
        • Write To File
      • Keyboard
        • Press Key
        • Type Text
        • Type Password
      • Mail
        • Get Email Messages
        • Move Mail Message
        • Save Email Attachment
        • Get Outlook 365 Emails
        • Save Outlook 365 Attachments
      • Mouse
        • Click Control
        • Click Point
        • Move Mouse
        • Move to Control
        • Click Image
      • Office
        • Word.Replace
        • Get Word Page Count
      • PDF
        • Extract Sections from PDF
        • Get PDF Page Count
        • Read text from PDF
      • UI
        • Assign Control Identifier
      • User
        • Choose File
        • Choose Folder
        • User Input Dialog
      • Visual
        • Get Image
        • Extract Text
      • Web
        • Wait for Page
        • Run JavaScript
        • Wait for Element Attribute
        • Set Element Text
        • Get HTML Element
        • Open Browser
        • Click Element
        • Get HTML
        • HTTP REST Request
      • ZIP
        • Add to ZIP
        • Extract ZIP
  • TAP Release Updates
    • Release Updates
      • TAP Release Notes 24.8
      • TAP Release Notes 24.01
      • TAP Release Notes 21.12
      • TAP Release Notes 21.10
      • TAP Release Notes 21.06
      • TAP Release Notes 21.04
      • TAP Release Notes 21.02
      • TAP Release Notes 20.12
      • TAP Release Notes 20.11
      • TAP Release Notes 20.09
  • Use Cases
    • Use Cases
      • TAP Studio - Define Invoices Naming Convention
      • TAP Studio - Extract Accounting Data from Invoices
      • TAP Studio - Control Identifier Fine Tuning
      • TAP Studio - Move File
      • TAP Studio - Download Invoices from Email and Move Emails
      • TAP Studio - How to input invoice data into an Accounting System
      • TAP Studio - How to open all desired files of a certain type in a folder
      • TAP Studio - How to parse a custom excel date format without errors
  • Knowledge Base
  • Scripting Utility Classes
    • Tailent.Control Class
      • Tailent.Control.ControlExists
    • Tailent.Console Class
      • Tailent.Console.WriteLine
    • Tailent.StringUtils Class
      • Tailent.StringUtils.FuzzSetMatching
    • Common Scripts Repository
      • C# Scripts Repository
        • Read Excel
        • Write Excel
        • Add LOV in Excel
        • Change Excel Cell Color
        • RegexSplit (Text Filtering)
        • Start a process
        • Filter HTML Contents
        • Reading CSV Contents
        • LINQ Filtering
        • Data Conversion
          • Double to DateTime
  • Solution Migration
  • Microsoft 365 Automation
    • Creating an application Instance
  • Remote Desktop Automation
    • Setting up the remote context
  • Knowledge Base
    • Configuring Proxy Servers
    • tap.runtime.config - Debug flag
    • Unblocking Action .dll Files
Powered by GitBook
On this page

Was this helpful?

  1. Use Cases
  2. Use Cases

TAP Studio - How to parse a custom excel date format without errors

PreviousTAP Studio - How to open all desired files of a certain type in a folderNextScripting Utility Classes

Last updated 3 years ago

Was this helpful?

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);

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!

You can find out mode about FromOADate here:

https://docs.microsoft.com/en-us/dotnet/api/system.datetime.fromoadate?view=net-5.0