🚑 🆘 [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues

This FirstAid Tutorial will describe how DateTime parsing issues can be analyzed and handled.

Click spoiler text when in hurry:

DateTime formats displayed in EXCEL are not guaranteed that it will present in the same format within the datatable after a read range. But the format present within the datatable is to use for the implementation details. Therefore the datatable content has to be inspected whe datetime format issues are occuring.

Example

Assignment

Following Excel data will be processed and the corresponding Quarter is to calculate for the particular task date

Input data:

input_raw

Quarter Calculation

Quarter = CInt(Math.Ceiling(YourDateTimeVar.Month / 3 ))

Initial Implementation

tmpTaskDate = DateTime.ParseExact(row("Date").toString.Trim, "dd.MM.yyyy", CultureInfo.InvariantCulture)

grafik

Issue

when executing the modeled flow, following exception occurs:
grafik

For any reasons the second row processing is failing

Analysis

Imports check

ensure that following namespace is imported:
import_systemglobalization

Implementation check

grafik

The configured format pattern looks similar to the format from the excel data

A further analysis is needed

Option 1: detailed tracking within the watch panel

For a detailed tracking a breakpoint is set and the details will be analyzed within the watch panel

ACTION: Set breakpoint on the failing activity

Details

setBreakpoint_raw

ACTION: Click Debug File and get paused by the breakpoint (ensure no other breakpoints ae set or continue till the relevant breakpoint)

ACTION: Configure the watch expression: row("Date").toString

Details

grafik

ACTION: Inspect the value displayed from the watch expression and verified it if the format is matching to the implementation

REPEAT: Click Continue till the analysis need is served or the execution fails
grafik

Details

grafik

Option 2: Direct inspections within the immediate panel

ACTION: Set breakpoint on the failing activity

Details

setBreakpoint_raw

ACTION: Type in your statement and confirm it with ENTER (sometimes a second ENTER is needed)
grafik

Datatable row value check:

dtData.Rows(0)("Date").toString
change the row index to that datarow index that is to inspect

Summary

grafik

Datatable content check

For smaller data rows counts the datatable can be inspected by the following:
dtData

Summary

grafik

Analysis Result

Observation

Dates from Excel
input_raw
are not in the same format within the datatable after the read range:
grafik

Error interpretation

Second row date: 05/07/2021 00:00:00 is not within the configured format defined for the DateTime.ParseExact method
grafik
dd.MM.yyyy vs. MM/dd/yyyy HH:mm:ss

Therefore the DateTime parsing is failing. A fixing strategy will be presented later in the tutorial.

Huge volume data analysis

In case a data table contains more rows as it can be inspected e.g within the locals / watch / immediate panel or the failing row is unclear following technique can be used for quickly identifying the failing parsings:

Check the count of failing formats

dtData.AsEnumerable.Where(Function (x) DateTime.TryParseExact(x("Date").toString.Trim, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, nothing)).Count

Filtering on the failing formats:

dtData.AsEnumerable.Where(Function (x) DateTime.TryParseExact(x("Date").toString.Trim, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, nothing)).CopyToDataTable

Fixing strategy

DateTime.ParseExact methods offers a method signature where an array with different formats can be provided. So the parseExact can handle multiple formats in one go

Modified Implementation

Variables:
grafik

Modified Flow:

Logs:
grafik

Conclusion

  • Excel values visually checked will not give a reliable representation on how values are later present within the datatable
  • DateTime failure issues are to analyze on the base where the values are passed to the conversion method (DataTable)
  • With a set of debugging techniques the issue can be quickly analyzed within the different debugging panels
  • With some LINQ statements also high volume data can be checked
  • a different call of the ParseExact method can also handle multiple formats for the parsing

References

Recommendations

  • Training courses from Academy
  • Debugging course from Academy (Highly recommended)

Questions

For questions on your specific case open a new topic and get individual support

15 Likes
Doing Date Subtraction in Excel
Linq query to Update Datatable 1000 of Date format rows as "dd-mm-yy" from "DD/MM/YYYY HH:MM:SS"
How to convert date, if cell is empty
Write a time higher than the one obtained in string format
Compare two Columns in Excel and check if first columns dates are more than 30 days for the second column
Forum Engagement Daily Reports
Remove data row if the value cannot be find in an array
Error in data table LINQ query
Convert to DateTime that are in different formats
How to extract the date format from excel correctly?
Get Max and Min Date in Column
Printing Specific Columns
Convert String to DateTime throws Error
String was not recognized as valid date Time in C#
DateTime Manipulation - String was not recognized as a valid DateTime
Filter DataTable for Future Dates
Read the data as per the date and write that data in output
Message Box: String was not recognized as a valid DateTime
Specified cast is not valid on Assign Activity
Convert yyyy-mm-ddThh:mm:ss into date yyyy-mm-dd
Get Row Item Datetime Error
Getting error while converting String to datetime
Formatting date column
How to check if a string value is in correct date format (dd/MM/yyyy)?
Convert string to DateTime not working
Summing and Avg Excel Integer Values or Cell Values
Adding Rows containing currency
DT.select
Linq to filter "Date" column in datatable
Download email attachments from outlook, create folders in local directory
How to extract data for last 7 consecutive days from an excel data table
Linq to compare dates