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

[ppr (Peter Preuss)]

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

Click spoiler text when in a hurry:

The date formats displayed in EXCEL are not guaranteed to be in the same format after a read range in the data table. However, the format available in the data table must be used for the implementation details. Therefore, the contents of the data table must be checked if problems with the date format occur.

Example

Assignment

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

Input data:

Quarter Calculation

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

Initial Implementation

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

Issue

when executing the modeled flow, following exception occurs:

For any reasons the second row processing is failing

Analysis

Imports check

ensure that following namespace is imported:

Implementation check

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

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

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

Details

Option 2: Direct inspections within the immediate panel

ACTION: Set breakpoint on the failing activity

Details

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

Datatable row value check:

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

Summary

Datatable content check

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

Summary

Analysis Result

Observation

Dates from Excel

are not in the same format within the datatable after the read range:

Error interpretation

Second row date: 05/07/2021 00:00:00 is not within the configured format defined for the DateTime.ParseExact method

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) Not DateTime.TryParseExact(x("Date").toString.Trim, "dd.MM.yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, nothing)).Count

<Screenshot will be presented soon>

Filtering on the failing formats:

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

<Screenshot will be presented soon>

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:

Modified Flow:

Logs:

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

• DateTime.ParseExact Doku
  DateTime.ParseExact Method (System) | Microsoft Learn

• LINQ:
  [HowTo] LINQ (VB.Net) Learning Catalogue

Recommendations

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

About the FirstAid Series

The first aid series offers various instructions on how to tackle a difficulty with the first steps

[FirstAid] - Topic Catalogue

This topic indexes the various [FirstAid] topics. It is also updated when new [FirstAid] topics are published. FirstAid List Data Table Summary Link Keywords This FirstAid tutorial describes how to handle the EXCPTION: "The source contains no data rows" FirstAid DataTable, Exception, This FirstAid Tutorial describes how to analyze and handle DateTime parsing issues FirstAid DataTable, DateTime, Parsing, Invalid Date, Analysis Activities Summary Link Keywords This FirstAi…

Questions

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