How to Validate a csv file while reading

Question

_{Monday, July 30, 2012 10:36 AM}

I have a csv file i am processing to read, the step to read the file is completed. but how can i validate the csv file in other to display error message if there is any error in the input data on any row.

I mean how can i handle the loop to read the number of line in a csv file . and if there is any error i want to specify the line with error.

I welcome any suggestion. thanks

All replies (20)

_{Tuesday, July 31, 2012 12:11 PM ✅Answered}

It should be LineNbr++ and not LineNbr = ln.Count. I believe ln.Count would give you the length of the string and not the line number.

_{Monday, July 30, 2012 12:08 PM}

1. Use a 3rd party library with the feature built in.

2. Build it yourself. If you really want detailed error handling building it yourself might be a good way to go. This is usually done using state machine called a Finite State Machine (FSM). http://en.wikipedia.org/wiki/Finite-state_machine. This is basically a homegrown version of regular expressions.

3. Use regular expressions and parse each line seperatly.

_{Monday, July 30, 2012 12:23 PM}

I don't want to use a third party library whatsoever. i just want  a guideline on code step. below is my code for your view:

public ActionResult Index(HttpPostedFileBase FileUpload)
        {
            ViewData["showGrid"] = true;

            try
            {
                    HttpPostedFileBase fb = Request.Files[0];
                    List<ClsFileContent> fileContent = new List<Models.ClsFileContent>();
                    StreamReader sd = new StreamReader(fb.InputStream);
                    
                        string ln = string.Empty;
                        string[] ary = null;
                        ln = sd.ReadLine();
                        do
                        {
                            ln = sd.ReadLine();
                            if (ln != null)
                            {
                                ary = ln.Split(',');
                                fileContent.Add(new ClsFileContent
                                {
                                    FirstName = ary[0],
                                    MiddleName = ary[1],
                                    LastName = Convert.ToInt16(ary[2]),
                                });
                                if(!IsNumeric(ary[2]))
                                {
                                    throw new Exception("value not number");
                                }
                            }
                        }
                        while (ln != null);
                        ViewData["grid"] = fileContent;
                    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            

            return View();
        }

        public static bool IsNumeric(string NumericText)
        {
            bool isnumber = true;
            foreach (char c in NumericText)
            {
                isnumber = char.IsNumber(c);
                if (!isnumber)
                {
                    return false;
                }
            }
            return isnumber;
        }

_{Monday, July 30, 2012 1:58 PM}

throw new Exception("value not number");

throw new Exception("value not number at line "+ ln );

_{Monday, July 30, 2012 2:46 PM}

Add a new variable called lineNumber and increment it inside the loop. That way you can do:

throw new Exception(String.Format("value not number at line {0}", lineNumber));

Also, csv files can be more complicated than a simple split statement can handle. If you have a qouted value for instance it will try to split commas inside the value instead of taking all qouted text like it should. For Example:

"Ken, Parker", 56, Employee

would be broken up as 

"Ken

Parker"

56

Employee

However, qouted strings are suppose to be taken as a single value and not split. Of course this is more important if you do not have control of the file format.

_{Monday, July 30, 2012 8:17 PM}

Use LinqToCSV and it has all the features you need. like validating the content of the file and many more feature.

Look for this article. http://www.codeproject.com/Articles/25133/LINQ-to-CSV-library

_{Tuesday, July 31, 2012 6:13 AM}

Yes, i have tried out an example in LinqToCSV library and it worked out well in display a grid. but the issue i am facing there is out to catch the WrongDataFormat Exception.

If someone can put me through with the code to catch the WrongDataFormat Exception in LinqToCSV i will really appreciate it.

_{Tuesday, July 31, 2012 6:45 AM}

Look at the bottom of the article. It has that.

WrongDataFormatException

Additional Properties - This exception exposes the same properties as Exception, plus these additional properties:

Thrown when a field has the wrong format. For example, a numeric field with the value "abc".

_{Tuesday, July 31, 2012 7:11 AM}

I understand you, but the proble i face is how to contruct the code to catch the particular field and line that has an error.

Please try and contruct me a code to follow. my code's below

[GridAction]
        [HandleError(View = "Error", ExceptionType = typeof(ArgumentException))]
        public ActionResult Grid(string FileName)
        {
            int LineNbr = 0;
            string File = TempData["file"] as string;
           
                try
                {
                    CsvContext cc = new CsvContext();
                    List<ClsFileContent> nation = cc.Read<ClsFileContent>(File).ToList();
                    ViewData["nat"] = nation;
                }                
                catch(WrongDataFormatException wdf)
                {
                    string fieldName = Convert.ToString(wdf.Data["FieldName"]);
                    int LineNbr = Convert.ToInt16(wdf.Data["LineNbr"]);
                    
                    throw new ArgumentException(string.Format("error on column-{1} at line-{2}", wdf.Data["FieldName"], wdf.Data["LineNbr"]));
                }

            
            return View("Index", ViewData["nat"]);
        }

_{Tuesday, July 31, 2012 7:14 AM}

I added the variable but it's only generating line number at random. my code's below

 [HttpPost]
        public ActionResult Index(HttpPostedFileBase FileUpload)
        {
            ViewData["showGrid"] = true;
            int LineNbr = 0;

            try
            {
                    HttpPostedFileBase fb = Request.Files[0];
                    List<ClsFileContent> fileContent = new List<Models.ClsFileContent>();
                    StreamReader sd = new StreamReader(fb.InputStream);
                    
                        string ln = string.Empty;
                        string[] ary = null;
                        ln = sd.ReadLine();
                        do
                        {
                            ln = sd.ReadLine();
                            LineNbr = ln.Count();
                            //LineNbr++;
                            if (ln != null)
                            {
                                ary = ln.Split(',');
                                fileContent.Add(new ClsFileContent
                                {
                                    FirstName = ary[0],
                                    MiddleName = ary[1],
                                    LastName = Convert.ToInt16(ary[2]),
                                });
                                if(!IsNumeric(ary[2]))
                                {
                                    //ViewData["error"] = "value not number at line " + ln + " in " + FileUpload.FileName;
                                    //throw new Exception("value not number at line " + ln +" in "+ FileUpload.FileName);
                                    throw new Exception(String.Format("value not number at line {0}", LineNbr));
                                }
                            }
                        }
                        while (ln != null);
                        ViewData["grid"] = fileContent;
                    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            

            return View();
        }

for example error is on line 2 and it can say the error is on line 21 or any line.

_{Tuesday, July 31, 2012 10:57 AM}

Even when i declare the variable and increment in the loop, it still doesn't recognise the lines in the csv file.

_{Tuesday, July 31, 2012 1:40 PM}

it's still selecting number at random, just specifying any number.

like: Value not number on line 17. instead of line 3 that the value is not an integer.

_{Tuesday, July 31, 2012 6:33 PM}

It looks like your indexes are off. You are using ary[2] for lastname and also checking if it is numeric. Are you sure that you did not intend on ary[3]? Also I would suggest doing Int.TryParse instead of checking each character. See the link below for how to use TryParse.

http://msdn.microsoft.com/en-us/library/f02979c7

_{Wednesday, August 1, 2012 5:01 AM}

The datatype of the lastName field is int, that's why i am trying to check maybe the value in LastName field of the csv file is int before showing the grid.

And ary[2] is for LastName, no mistake in that.

_{Wednesday, August 1, 2012 10:28 AM}

yes i am able to get the lineof the error but what if i want to catch multiple lines. That is:

if there is a similar mistake on lines 1, 7, 10, 20 or any other line, how will i contstruct the code to catch it and display the error to list the line numbers with error. presently the exception message is

value not integer on line 2 in names.csv

 my code is:

 [HttpPost]
        public ActionResult Index(HttpPostedFileBase FileUpload)
        {
            ViewData["showGrid"] = true;
            var LineNbr = 0;

            try
            {
                    HttpPostedFileBase fb = Request.Files[0];
                    List<ClsFileContent> fileContent = new List<Models.ClsFileContent>();
                    StreamReader sd = new StreamReader(fb.InputStream);
                    
                        string ln = string.Empty;
                        string[] ary = null;
                        ln = sd.ReadLine();
                        while (ln != null)
                       // do
                        {                            
                            ln = sd.ReadLine();
                            //int line = LineNbr++;
                            
                            if (ln != null)
                            {
                                LineNbr++;
                                ary = ln.Split(',');
                                fileContent.Add(new ClsFileContent
                                {
                                    FirstName = ary[0],
                                    MiddleName = ary[1],
                                    LastName = Convert.ToInt16(ary[2]),
                                });                                
                            }
                        }
                       // while (ln != null);
                        ViewData["grid"] = fileContent;
                    
                }
                catch 
                {
                    throw new Exception("value not integer on line " + LineNbr + " in " + FileUpload.FileName);
                }
            

            return View();
        }

**

**

_{Wednesday, August 1, 2012 2:12 PM}

Make a List of Exceptions and instead of throwing the exception keep it in the list. Then latter you can show all the exceptions at once.

List<Exception> errors = new List<Exception>();

while(...)
{
  try
  {
  }
  catch(Exception ex)
  {
     errors.Add(ex);
  } 
}

Also, it is best practice to catch only exceptions that you are expecting and not to catch general exceptions. Find out what exceptions can be thrown and only catch those exceptions. This is because some system exceptions are expected to cause the program to close and should not allow the program to continue if they happen. A stack overflow is an example of an exception that should result in the program crashing.

_{Wednesday, August 1, 2012 7:51 PM}

How can i check for a field with the wrong data item. Like i want to say Column LastName on Line 12 has an invalid input.

Just to catch the error of the columns as i have done with the rows.

_{Friday, August 3, 2012 6:29 AM}

I am able to catch the row of the error now,  How can I catch the exact column of the error.

That to say that:  LastName column of Line 15 has an invalid value.

_{Friday, August 3, 2012 12:48 PM}

You can't do that using split. I am not sure reg expressions will do it either. I will post a function later because I am doing the same thing right now.

_{Monday, August 6, 2012 5:57 AM}

Thanks man,

I will be expecting the function as soon as you're through.