Hi @Yoichi : Issue is not resolved yet. Pl find attached the workflow for your reference. Kindly do the needful. After executing this workflow, it gives an error, pl. see the 2nd screenshot.
From this Image and the error that you receive when trying to process it, I do believe that different date values are in different format in the excel file and hence you get error when you try to convert to one type of date format and Compare.
For verifying if that’s the real problem, Can you just Loop over the datatable and use a Write Line to print the Date values ? So that we can confirm if this is the case.
The output is perfect because I have handled the exception of Blank records, but as soon I enter the white space either trailing or leading in the Account Opening Date column then return the following error:
I have used your Main.xaml and DataFormat.xlsx file, there is no error generated when I execute the workflow.
I am not sure if the file provided is a modified one, but there are no such errors and we cannot debug if the same problem that you get doesn’t occur on our end.
However, I believe the Leading and Trailing White Spaces are handled according to your Workflow, since there is a Trim Operation in place.
Can you Check the Data that you have is same as the below data that you have Provided?
I am only concerned how to handle if my date column has trailing or leading spaces? Because when I enter the white space in record no. 2 then gives me an error “Conversion from String “22/02/2006” to type ‘Date’ is not valid”. How to get rid of this issue? Thanks,
I was able to keep the data according to your format and had also added Trailing and Leading spaces which resulted in a Different format of Date and thus CDate would give out an Error. Check the Image below.
As you can see, the Formats are different, So I suppose, if there exists a whitespaces along with the date, it would appear in the format as highlighted in the image.
In order to handle this format as well, we can use the below function to convert the date to one format.
This is used in an Assign Activity and assigned to row("ETB/NTB")
As you can see, I have used two format Strings since there are two formats detected in the Excel.
Please Check the Updated Workflow below and Let us know if it doesn’t work. Main (1).xaml (13.5 KB)
@supermanPunch : Pl use this attached excel file with .xaml file that one you shared with me because I have added the white spaces in record no. 2. Please you can get the better idea.
I see, the formats that we get are different in your System and when modified in my System, It appears differently, and hence we were not able to make it proper.
But however, when we usually develop these date format scenarios, we do need to know it’s formats, if there are multiple formats we need to get all the multiple formats detected and then use it, so as to be able to perform operations further.
So in this case as well, I have detected 4 different formats, So in order to Incorporate those 4 I have created a String Array which contains all possible date formats, which can then be used with DateTime.ParseExact() for conversion to one Date Format.
In the process of Developing we need to identify all possible formats and add it to the Dateformats array as to not get any Conversion Error.
Please Check the Workflow Below : Main (1).xaml (14.3 KB)
Let us know if this is not the expected answer you were looking for.
