🚑 🆘 [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

14 Likes