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
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
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
Option 2: Direct inspections within the immediate panel
ACTION: Set breakpoint on the failing activity
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
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
Recommendations
About the FirstAid Series
The first aid series offers various instructions on how to tackle a difficulty with the first steps
Questions
For questions on your specific case open a new topic and get individual support