Trimming of White Spaces from Date Column

Hi All, Below is the date column and I want to remove the trailing and leading spaces from the date.

Date Column
20/02/2006
22/02/2019
08/03/2006

16/03/2006
21/03/2006
image

I have used the following code:

For Each Row
Assign Activity:
row(“Date Column”) = row(“Date Column”).Tostring.Trim

but afterwards, I want to compare the date with other static date, for example:

IF:
CDate(row(“My_Column”)).Date <= My_Another_Date_Col
Then
Assign: Something.

It gives me an error i.e., “Conversion from String “22/02/2019” to type ‘Date’ is not valid”.

Thanks,

Hi,

Can you try to use DateTime.ParseExact Instaed of CDate as the following?

DateTime.ParseExact(row("My_Column").ToString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture)

Regards,

1 Like

@Yoichi : Thank you for your swift response. I have tried this but more complexities occurred. Pl. suggest some easy process :slight_smile:

Hi,

Do you have any problem? Can you share it?
CDate cannot convert from “dd/MM/yyyy” style string. So we need to use DateTime.ParseExact method for it.

Regards,

@Yoichi : Pl. see the screenshot:

Hi,

I cannot get in details, however can you try to modifyCDate to DateTime.ParseExact at the following red arrow expression?

Or can you share your workflow, if possible?

Regards,

@Dr_Raza_Abidi_PhD

I think @Yoichi is right. Just convert your date in similer format.

DateTime.ParseExact(row(“My_Column”).ToString,“yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture) because your taking date 2018-07-02

Regards
Anand

You have to convert both with CDate to compare them.

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.

Thanks,

Main.xaml (12.1 KB)

Hi @Dr_Raza_Abidi_PhD ,

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.

Hi @supermanPunch : Pl find attached the workflow for your reference:

Main.xaml (12.1 KB)

Hi @Dr_Raza_Abidi_PhD ,

I think We can investigate on the Workflow later, What we would require now is a confirmation on the Date formats of your data in Excel.

If you can provide the excel file that you are working on, it would be helpful for us to give you the Proper solution.

Or If the Excel is Confidential, you can just Copy paste the Dates in another Excel and provide us the sample file.

@supermanPunch : As required, pl. find the attached workflow and example excel file for your reference:
Main.xaml (11.7 KB)

Main.xaml (11.7 KB)

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:

My concern is to handle the white spaces in Account Opening Date column. Pl. guide.

Thanks,

Date_Format.xlsx (10.9 KB)

@Dr_Raza_Abidi_PhD ,

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.

image

Can you Check the Data that you have is same as the below data that you have Provided?
image

@supermanPunch : I have the following data style in my excel:

20/02/2006
22/02/2006
08/03/2006
08/03/2006
16/03/2006
21/03/2006

20/04/2006
29/04/2006
09/06/2006
23/06/2006
image

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,

Hi @Dr_Raza_Abidi_PhD ,

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.

image

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.

DateTime.ParseExact(row("ETB/NTB").ToString,{"MM/dd/yyyy HH:mm:ss","dd-MM-yyyy"},System.Globalization.CultureInfo.InvariantCulture,DateTimeStyles.None)

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 : Thanks for your help. But when I manually inserted a trailing and leading 4 spaces in the excel sheet, and then run again.

Account Opening Date
20/02/2006
22/02/2006
08/03/2006
08/03/2006
16/03/2006
21/03/2006

20/04/2006
29/04/2006
09/06/2006
23/06/2006
07/07/2019
13/07/2006
14/07/2006
24/07/2006
25/07/2006
image

It gave me an below error. Pl see screenshot:

@Dr_Raza_Abidi_PhD ,

Could you Please send the Screenshot of the Output Panel after you get the Error ?

@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.

Many thanks,

Date_Format.xlsx (11.0 KB)

@Dr_Raza_Abidi_PhD ,

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.

1 Like