0

I have a requirement where I need to validate excel file before importing into application. Excel file will have around 15-20 defined columns. And we are having around 200-250 rules to validate the excel file.

Right now, one windows service is there, which is scanning the directories to process this excel files. windows service can process multiple files using multi threads. But single file will be validated row by row only. Currently validations has been written in single class using methods.

It is working fine so far but now we are getting requirement changes in rules and validations and I want to make rules and validation configurable and more manageable.

Examples of Rules:

  1. There should not be a Empty Values.
  2. Check Max and Min Length
  3. Date formats
  4. If value present in one column, other related columns value should be empty.

My questions are:

  1. How to Make Rules and Validation Configurable? I've checked Enterprise Library Validation Block. Which could be used using XML configuration. But I feel it will not be maintainable going ahead.
  2. How to make Rules and Validation Turn On and Off on the fly without changing and code and compilation? I want to achieve design somewhat like SonarQube Rules and Validation.
  3. Right now, using multi threading multiple files are being processed. Is it Ok to process single file records parallel as well? I don't think it will increase the performance as all thread being used for processing multiple files.
  4. Any design pattern for such kind of problem? I think Rules Pattern along with Pipeline kind of design could be used.
  • You don't say if you are only supporting .xlsx, or also old style .xls. Also what technology are you using to read the files? OpenXML? – Stewart Ritchie Aug 15 '17 at 7:37
  • Yes. We are using OpenXML and supporting both xlsx and .xls – Fenil Rathod Aug 15 '17 at 7:44
  • Ok, presumably you are using the old COM based libraries for .xls, as OpenXML is just for new for .xlsx. – Stewart Ritchie Aug 15 '17 at 7:48
  • If I needed to support both, I would aim to get the data into memory a collection of model classes first and get away from the specifics of having to deal with Interop code. But memory is likely to be a problem if the files include millions of rows. – Stewart Ritchie Aug 15 '17 at 7:52
  • Building a rules engine is not a massively hard thing... The rules code you currently have, have you extracted each rule type into its own class? If your code is procedural, this is the first thing to do. Leave the thinking about how to persist the rules to the very last. – Stewart Ritchie Aug 15 '17 at 8:04
1

The "pattern" you are asking for is called Domain Specific Language (DSL). Designing your own "language" for this type of problem, and an interpreter, can be simpler than it looks at a first glance.

Your rule language might be just a list of textual commands, stored in an excel sheet as well, where each row represents one rule, and the sheet has some columns like Active, Range, Rule type, Parameters.

  • Active is simply a column to control if the rule is turned on or off
  • Range should describe to which part of the input file the rule shall apply. Invent a description for the ranges you need to support, like a specific column, a specific set of rows, all cells in the table, all cells in the table except a header row, whatever you need for your use case
  • RuleType needs to contain a keyword for the type of rule like NotEmpty, MaxLength, MinLength, DateFormat, IsEmptyIfOtherColumnIsNotEmpty
  • Parameters contains the additional parameters like the allowed min/max length, or a format string for the date, or the depending column names.

If you try this out, you might be astonished how far this can get you. I implemented something similar very successfully at least two times, once for a processing language, and once for a validation language.

Since this should basically answer your questions 1,2 and 4, one additional word about 3: don't prematurely optimize with no need and no measurement. Sure you can try this, but why? Your program will still have to read each Excel file sequentially, and it will to log the outcome of the validation somewhere. These are I/O bound processes which cannot be parallelized. So processing of multiple rows in parallel might only bring you benefit if the rule processing itself will be the bottleneck and turns out to be much slower than the I/O, and if you have unused CPU cores available. So measure this first, and then think about this optimization, not the other way round.

  • I am agree with you on point 4 and will move ahead after measurement. And what you are suggesting I could achieve using C# and Lamda Expression Trees. Thanks! – Fenil Rathod Aug 15 '17 at 8:52
  • @FenilRathod: I am not sure you will need "Expression Trees" for this, sounds like overengineering to me, but YMMV. – Doc Brown Aug 15 '17 at 19:18
0

This answer is not meant to take away anything from Doc Brown's answer, but, to serve as a guidepost on your journey.

Be very wary of creating your own DSL or even making something configurable. It's not that you should never do either, but, you need to be aware of what path you are travelling down.

This article explains it very well, but I will give a quick overview:

http://mikehadlow.blogspot.com/2012/05/configuration-complexity-clock.html

The bottom line is, you're going to create a language to allow others to manage the rules. Where will you store those rules? In a database perhaps? What if they make a mistake and need to revert back? Better handle that too by versioning. Wait... versioning? Sounds like a source control system. Better store it in there instead.

Wait, the domain language doesn't do X today... we'll add it... then Y... then Z... Now you have a very complex language that you're maintaining which takes just as much time as maintaining the code itself used to, plus the DSL needs to be maintained, so someone (likely you, at some point when the business person realizes he doesn't understand it well enough) will have to maintain the DSL code too, and the DSL code will never be as robust as C#, because you'd end up just writing C# over again.

So, consider carefully if you really want to travel down this path. It's okay if you choose to do so, as there can be many benefits, but don't think that it's going to actually reduce complexity, or increase maintainability. It just moves the problems around.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.