Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Background:

I have a CSV file, which I need to ready and validate each element in each row and create a collection of a class, having valid data.

i.e CSV File looks like:

   EmpID,FirstName,LastName,Salary
    1,James,Help,100000
    2,Jane,Scott,1000
    3,Mary,Fraze,10000

Class looks like:

public class Employees
{
    public int EmpID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Salary { get; set; }
    public string ErrorReason { get; set; }
}

Here are the validations required for each field:

  • EmpID:

    • Its a mandatory field, hence cannot be null or empty
    • It should be only an integer
    • It should be not more than 2 digits
    • It should be present in database (query that database and check if an employee exits with this empid.
  • FirstName (same validation for LastName):

    • Its a mandatory field, hence cannot be null or empty
    • It should be only alphabets.
    • Not more than 30 characters are allowed
  • Salary:

    • Its a mandatory field, hence cannot be null or empty
    • It should be a decimal.

To achieve this, here is my approach:

  1. Read CSV file row by row
  2. For each element i.e EmpId, FirstName... etc do the required validations by calling individual methods having validation logic. eg: public bool ValidateIsDecimal(string value) { } public bool ValidateIsEmpIdExists(string value) { } etc
  3. If valid, fill corresponding property of "Employees" class.
  4. If NOT VALID, fill "ErrorReason" property, with appropriate reason as to what caused the validation to fail.(eg: Required filed was missing or datatype is not decimal etc)
  5. Add this Class to Employees collection (eg: List)

So, my question is, is this the right approach, or is there any other better/cleaner way of validating class properties.

share|improve this question
    
There is no C# 4.5. Did you mean C# 5.0? Though I don't see how does the version of the language change anything. –  svick Jan 9 at 18:43
2  
fluentvalidation.codeplex.com, or the Validation Application Block in Enterprise Library 6, if you have a lot of validation to do. –  Robert Harvey Jan 9 at 19:08
    
I second the note to use FluentValidation. It will allow you to easily fill ErrorReason with all of the reasons it isn't valid, not just the first reason found. –  Larry Smithmier Jan 9 at 19:16
    
I believe they meant the framework 4.5 rather than the language. The version of the framework is certainly relevant. –  Larry Smithmier Jan 9 at 20:08

4 Answers 4

up vote 2 down vote accepted

If I needed to input data and capture errors, I would probably do something more like:

public class Employee
{
    public int EmpID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Salary { get; set; }
}

public class ErrorEmployee : Employee
{
    public string[] ErrorReason { get; set; }
}
  1. Read CSV file row by row
  2. For each element i.e EmpId, FirstName... etc do the required validations by calling individual methods having validation logic. eg: public bool ValidateIsDecimal(string value) { } public bool ValidateIsEmpIdExists(string value) { } etc
  3. If valid, fill corresponding property of "Employees" class. Add this Class to Employees collection (eg: List)
  4. If NOT VALID, fill "ErrorReason" property, with appropriate reason as to what caused the validation to fail.(eg: Required filed was missing or datatype is not decimal etc) Add this Class to ErrorEmployees collection (eg: List)

I wouldn't want to pollute the regular Employee (singular, use the plural for a collection of them) object with error messages. As in the note above, check out the FluentValidation library for your validation and capture a list of errors rather than just the first found.

share|improve this answer

This is quite simple problem so there is not much you can do wrong in this. Your approach looks fine.

Only thing I noticed might be bad is fact you are querying DB for each row. It would be better if you queried the DB just once with all the IDs. But assuming only 100 rows are possible, then this should not be a problem. But I would at least write a comment this might be a problem in the future.

share|improve this answer

Three classes

  • CSVLine or a string[]
  • Employee
  • Validator

CSVLine:

{
    public int LineNumber {get; set;}
    public string EmpID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Salary { get; set; }
}
  • Read the line into CSVLine
  • Call the appropriate Validator methods on CSVLine properties or array elements as needed
  • If valid, create an Employee passing in the CSVLine or string array

If its not valid, you can log the CSV line and validation error(s) to a reject file. You can validate all attributes or fail fast and stop on the first error. Up to you. Also, there is opportuity to make CSV a generic structure, like a string[] if you don't want named fields in the CSVLine and just pass the array elements as needed to the validator. An array may be a better choice because you can validate the number of element it has (in this case 4) as well.

The validator could also be an interface so you could create many different types of validators. Or you can just have one class if this is the only file you are dealing with.

share|improve this answer

It's fairly straight forward unless there are any additional complications that you haven't listed. Something as simple as this (pseudocode) will do:

var customers = File.ReadAllLines("...")
    .AsParallel()
    .Select(l => l.Split(new char[] { ',' }))
    .Where(s => Tuple.Create(
        validateId(s[0]), 
        validateFirstName(s[1]), 
        validateLastName(s[2]),  
        validateSalary(s[3])))
    .Select(t => new Customer(t.Item1, t.Item2, t.Item3, t.Item4));

The only nuance here is that validateXYZ will validate and return the correct type as well.

As far as the "ErrorReason" is concerned, filling bad data and then a reason for the bad data seems like a bad idea.

I've written more complicated queries than this using this exact method over files with millions of lines where it completes in seconds. If you've got more data than that, then there might be more work to do.

(btw: for these types of problems, linqpad is your friend).

share|improve this answer
    
How are you going to output the bad lines for diagnostic purposes? –  Jon Raynor Jan 9 at 20:29
1  
@JonRaynor The general problem is data cleaning and for small volumes of data and small frequencies of this process, I'd clean the remaining data manually on a case-by-case basis. If the OP's scenario is not a one-off then this this exercise is (or had better be) a stop-gap for a b2b service that accepts strongly typed information. Dumping to CSV strings is not a reliable long term business process. –  Steve Evers Jan 9 at 21:17

protected by gnat Aug 12 at 13:10

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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