How to validate the csv file in asp.net with C#

Question

I am working on uploading the csv file and saving the data into database. I have done with saving part but what i need is to validate the csv with correct data farmat.

 -----------------------------------------------
    Name  Address    Age  Gender
    ---------------------------------------------
    a        Hyderabad  23     M
    b         Banglore 25   F
    c        Mumbai    26 M
    ---------------------------------------------

The Data In csv file need to be in above format while upload. If they enter data in below format and trying to upload it then an error message need to be display saying "Upload Valid CSV File".

 -----------------------------------------------
    Name  Address   (Column Name Is missing)  Gender| Column0
    --------------------------------------------
    a       Hyderabad  23  M       ( Some Junk Data)
    b       Banglore 25   F         | 
    c        Mumbai  26   M       |
    ---------------------------------------------
     
    --------------------------------------------
    Xyz        olp
     -------------------------------------------

I have googled a lot but did not found valid links which suit my problem.

I don't think you need to google anything much here, you just have to try little bit and maybe read something about tryParse method. — Ms. Nobody
– Ms. Nobody, Commented Aug 28, 2014 at 10:06
@Ms.Nobody, Thanks for the reply, I think tryparse is not the complete sol.....,The schema of the csv file need to be same as the first table, if customers tries to upload csv file different schema then an error message need to be raised. Solution not must be like getting the each row and spliting and comparing, I need the schema and validate the schema. — Santosh Kumar
– Santosh Kumar, Commented Aug 28, 2014 at 10:14

Philip Pittle · Accepted Answer · 2014-08-28 10:21:48Z

4

There's not going to be a magic bullet for this. By design CSV if rather fluid so you wont be able to validate it the same way as you would an xml file for example.

But you can do a few things, for example:

 public void ValidateCsv(string fileContents)
 {
     var fileLines = fileContents.Split(
           new string[] { "\r\n", "\n" }, StringSplitOptions.None);

      if (fileLines.Count < 2)
         //fail - no data row.

      ValidateColumnHeader(fileLines[0]);

      ValidateRows(fileLines.Skip(1));
 }

 public bool ValidateColumnHeaders(string header)
 {
      return header.Trim().Replace(' ','').ToLower() == 
         "name,address,age,gender";
 }

 public bool ValidateRows(IEnumerable<string> rows)
 {
      foreach(row in rows)
      {
          var cells = row.Split(',');

           //check if the number of cells is correct
           if (!cells.Length == 4)
                return false;

           //ensure gender is correct
           if (cells[3] != "M" && cells[3] != "F")
               return false;

           //perform any additional row checks relevant to your domain
      }
 }

Most of the validation is going to pretty specific to your business domain, so you'll need to decide for yourself what constitutes 'bad data'. For example, you could check that your Age column is a positive number.

answered Aug 28, 2014 at 10:21

Philip Pittle

12.4k8 gold badges69 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Santosh Kumar Over a year ago

@Phillip Pittle, Thanks for the reply, I was in search of the solution as you mentioned above.

Mert Gülsoy · Accepted Answer · 2014-08-28 10:30:45Z

2

Use regex. If you have a certain template for each line in csv file i think regex is the best solution. Here is an example:

Template: 12345[tab]String(20 chars)[tab]String(1 char an one of these: M,N,O)
Regex: ^[0-9]{1,5}(\ ){0,4}\t.{20}\t[MNO]$

Lines:
12345   abcdefg                 M  --->Match
54345   abcdefg ghft            O  --->Match
12      vfjnvfjn vfjnvfn    K      --->No Match because it is faulty
12      vfjnvfjn vfjnvfn        N  --->Match

answered Aug 28, 2014 at 10:30

Mert Gülsoy

2,9291 gold badge22 silver badges25 bronze badges

1 Comment

user2723803 Over a year ago

@SantoshKumar Can you let me know the solution

Antonio Bakula · Accepted Answer · 2014-08-28 10:31:53Z

2

I would suggest CsvHelper, it's great CSV library and there are methods to deal with malformed CSV's, here it is: https://github.com/JoshClose/CsvHelper

Look at the documentation:

http://joshclose.github.io/CsvHelper/

There is an example how to read file and do something when CSV is mallformed:

var csv = new CsvReader( textReader );
while( csv.Read() )
{
    int intField;
    if( !csv.TryGetField( 0, out intField ) )
    {
        // Do something when it can't convert.   
    }
}

Be sure to look at the documentation on link above, there is an option to ignore reading exceptions:

csv.Configuration.IgnoreReadingExceptions = true;

answered Aug 28, 2014 at 10:31

Antonio Bakula

20.7k7 gold badges80 silver badges106 bronze badges

Collectives™ on Stack Overflow

How to validate the csv file in asp.net with C#

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related