Convert multiple date formats into a standard way

[manjesh_kumar]

Dear Experts,

Good Day.

I have a situation where I need to convert multiple date formats into a standard way using LINQ.

INPUT

Part Number	Point	Week	Qty
ABC	BNG	17/2021	390
ABC	BNG	40-43/2021	390
ABC	BNG	11.02.2021	390

EXPECTED OUTPUT

Part Number	Point	Week	Qty	Week Date	Week Format
ABC	BNG	17/2021	390	4/19/2021	W202117
ABC	BNG	40/2021	97.5	9/27/2021	W202140
ABC	BNG	41/2021	97.5	10/4/2021	W202141
ABC	BNG	42/2021	97.5	10/11/2021	W202142
ABC	BNG	43/2021	97.5	10/18/2021	W202143
ABC	BNG	11.02.2021	390	2/11/2021	D20210211

Appreciate your support

Regards,
Manjesh

[Lakshay_Verma]

Hey,

1. This won’t be a good approach, Date format needs to be try.parse before conversion in order to make sure that it won’t fail.
2. To standardize, multiple parsing needs to be done with a different type of date format which can’t be done into linq.
3. Better approach would be to use a model that will try parse different formats and once done break parsing loop and move to next record.

[ppr (Peter Preuss)]

@manjesh_kumar
Before forcing of doing with LINQ lets divide and conquer on the different building blocks.

Part: Week Info recognition
Week portion is not part of the date format patterns, so we have to handle different. also we do have different cases to approach: week, week range, date.

For recognizing it we can make use of Regex and Group names:

we do init a regex object with the pattern
we do iterate and execute the regex.match
in a switch we do calculate which of the group was firing and will use the group name for the fork

Other Steps
within the cases we can do the additional parsing, date calculation and target datatable population.

Getting date from WeekNo, have a look here for a similar sample as starter help:

Convert Week into Date

@jahidKM lets do following: lets split the 03.2021 into year and week [grafik] init the first Year day, calculate the offset to the first friday of the year [grafik] retrieve the week offset (same week or next week) [grafik] So we can do the final calculation [grafik] Find starter help here GetFriday_FromWeekNo.xaml (7.1 KB) in .Net 5 a function for this was added, but unfortunately we cannot use it, as we do run via different infrastructre (.Net Framework)

For the part from above find starter help here:
manjesh_kumar.xaml (9.6 KB)

[Jeroen_van_Loon]

I think you can never get this 100% solid, if the sourcefields are human free-text input, since it’ll leave too many combinations to validate, especially if one value can be interpreted multiple ways.

if I gave this field 1-3-20 do I mean the week of March 1st 2020, January 3rd? Week 1, 3 and 20, week 1 through 3 in 2020?

You should expect a margin of error trying to decypher this. It depends of course on your process if this is acceptable or maybe even business critical.

A better solution would be to apply input validation on your entry forms. If that is excel it is certainly doable, or otherwise the input form in your front end, which usuually requires a few less accesible changes to the form. Even a proper workinstruction towards the people who provide the data might help.
In short; make it strict, dummy proof or robot friendly, anyway you want to call it But please don’t make the robot guess a format. Structured data is a prime requirement of RPA. (At least untill AI matures a lot more)

I understand the last paragraph isn’t always within your direct influence as an RPA developer, but I’d put on my ‘business analyst’ hat and provide that feedback towards your requesters, to either change the input, or expect and accept a lot of process exceptions.

[manjesh_kumar]

Dear @ppr ,

Appreciate you valuable suggestion/feedback.

@Lakshay_Verma @Jeroen_van_Loon : Thank you guys for valuable inputs too.

Regards,

Manjesh