Datatable column/row field validation in C#

Question

1.00/5 (1 vote)

See more:

Hi I want to validate datable and if there is an any error in row of data table then i want add Error Msg in Error Column "Excel D: column contain Invalid data". in that row which consist error in row. and if any error occured i want to download excel with error in row.

Following is the code

fileLocation = ConfigurationManager.AppSettings["excelFilePath"].ToString() + fileName;

DataSet ExcelDataSet = null;
                       try
                       {
                           ExcelDataSet = objUl.GetDataTableFromExcel(fileLocation);
                       }
                       catch (Exception ex)
                       {

                           return RedirectToRoute(new { Controller = "Error", Action = "NotFound" });
                       }

public DataSet GetDataTableFromExcel(string path, bool hasHeader = true)
        {
            DataSet dataSet = null;
            string ErrorMsg = string.Empty;
            try
            {
                using (var pck = new OfficeOpenXml.ExcelPackage())
                {
                    using (var stream = System.IO.File.OpenRead(path))
                    {
                        pck.Load(stream);
<pre>var ws = pck.Workbook.Worksheets.FirstOrDefault();
                    DataTable tbl = new DataTable();
                    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                    {
                        tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                    }

                    DataColumn ErrorCol = new DataColumn("Error", typeof(string));
                    tbl.Columns.Add(ErrorCol);

                    var startRow = hasHeader ? 2 : 1;
                    for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                    {
                        bool RowIsEmpty = true;
                        var wsRow = ws.Cells[rowNum, 1, rowNum, 10];
                        DataRow row = tbl.Rows.Add();
                        for (int colNum = 1; colNum <= tbl.Columns.Count - 1; colNum++)
 object temp = ws.Cells[rowNum, colNum].Value;
                            //if (temp != null)
                            //{
                            //if (temp.ToString().Length <= 5)
                            //{
                            if (temp != null && !string.IsNullOrEmpty(temp.ToString()))
                            {
                                ErrorMsg = validateLengthColumn(temp.ToString().Trim(), colNum);
                                if (ErrorMsg != "")
                                {
                                    row[11 - 1] = ErrorMsg.ToString();
                                }

                                if (colNum == 3 || colNum == 4)
                                {
                                    try
                                    {
                                        //ExceptionLog.LogError(temp.ToString(), "date", "date");

                                        if (temp.ToString().IndexOf("AM") > 0 || temp.ToString().IndexOf("PM") > 0)
                                        {
                                            row[colNum - 1] = (temp.ToString().Trim());
                                        }
                                        else
                                        {

                                            row[colNum - 1] = temp.ToString().Trim();
                                            //double d = double.Parse(temp.ToString());
                                            ////DateTime d = DateTime.ParseExact(temp.ToString(), "MM/dd/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);
                                            ////string dt = temp.ToString();

                                            ////string dt = temp.ToString();
                                            //DateTime conv = DateTime.FromOADate(d);
                                            ////DateTime conv = DateTime.Parse(dt.ToString());
                                            ////DateTime conv = Convert.ToDateTime(temp.ToString());
                                            ////string convT = conv.ToString("dd/MM/yyyy");
                                            //string convT = d.ToString("dd/MM/yyyy");
                                            //convT = convT.Replace("-", "/");
                                            //row[colNum - 1] = convT;
                                            //row[colNum - 1] = temp.ToString();
                                        }
                                        //ExceptionLog.LogError(row[colNum - 1].ToString(), "date", "date");
                                    }
                                    catch (Exception ex)
                                    {
                                        row[colNum - 1] = "";
                                    }

                                }

                                else
                                {
                                    row[colNum - 1] = temp.ToString().Trim();
                                }


                                RowIsEmpty = false;
                            }
                            else
                            {
                                if (colNum == 1 || colNum == 2)
                                {
                                    RowIsEmpty = true;
                                    //break;
                                }
                            }
                            //}
                            //else
                            //{
                            //    ErrorMsg = "column 1 Has invalid Length";
                            //    row[11 - 1] = ErrorMsg.ToString();
                            //}

                            //}
                        }

                        if (RowIsEmpty == true)
                        {
                            break;
                        }
                    }
                    if (tbl.Rows.Count > 0)
                    {
                        dataSet = new DataSet();
                        dataSet.Tables.Add(tbl);
                    }
                }
            }

                        {

public string validateLengthColumn(string Value, int col)
        {
            string Msg = string.Empty;
            int outvalue;
            if (Value.Length > 100 && col == 1)
            {
                Msg = col + " Contain Invalid Length"; 
            }
            if (Value.Length > 100 && col == 2)
            {
                Msg = "Column " + col + " Contain Invalid Length OR Format";
            }
            if (Value.Length > 50 && col == 3)
            {
                Msg = "Column " + col + " Contain Invalid Length OR Format";
            }
            if (Value.Length > 50 && col == 4)
            {
                Msg = "Column " + col + " Contain Invalid Length OR Format";
            }
            if (Value.Length > 30 && col == 5)
            {
                Msg = "Column " + col + " Contain Invalid Length OR Format";
            }
            if (Value.Length > 100 && col == 6)
            {
                Msg = "Column " + col + " Contain Invalid Length OR Format";
            }
            if (col == 7)
            {
                if (Value.Length > 30)
                {
                    Msg = "Column " + col + " Contain Invalid Length OR Format";
                }
                if (int.TryParse(Value, out outvalue))
                {

                }
                else
                {
                    Msg = "Column " + col + " Contain Invalid Datatype IT should number";
                }
                
            }
            if (Value.Length > 50 && col == 9)
            {
                Msg = "Column " + col + " Contain Invalid Length OR Format";
            }
            if (Value.Length > 10 && col == 10)
            {
                Msg = col + "  Invalid Length";
            }
            return Msg;
        }

DataRow[] result = dataTable.Select("Error like '%Invalid Length%' ");

                            if (result.Length > 0)
                            {
                                ExportToExcel(dataTable, theFileName);
                            }

Explain you what i am trying to do

I am taking data from Excel and passing data to following function

ExcelDataSet = objUl.GetDataTableFromExcel(fileLocation);

in above function taking data and converting it to datatable , in this function i added extra column if any error occure

DataColumn ErrorCol = new DataColumn("Error", typeof(string));
                   tbl.Columns.Add(ErrorCol);

in same function i validating data using following function

ErrorMsg = validateLengthColumn(temp.ToString().Trim(), colNum);

if any error ooucred it added in Error column

after all done datable fill

last i am validation if datable "Error" column contain any value if "yes" then i pass datatable to export data

DataRow[] result = dataTable.Select("Error like '%Invalid Length%' ");

                           if (result.Length > 0)
                           {
                               ExportToExcel(dataTable, theFileName);
                           }

Problem Is using this code try to procced 40,000 record it's take lot a time

if there is any code that make above process fast

What I have tried:

i try many thing datable.compute() method and many article in google but not get successd

Posted 19-Jan-24 1:31am

Akshay malvankar

Add a Solution

Comments

Akshay malvankar 22-Jan-24 1:56am

Can Anyone help me on this

Akshay malvankar 22-Jan-24 7:26am

any one here to help me on this

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Zain Khan 2024 · Answer 1 · 2024-01-23T20:07:00

It looks like you want to validate data in an Excel file and add an error message to a new "Error" column for rows that contain invalid data. Additionally, you want to download the Excel file with the added error information.

Here's a suggested solution:

1. Add Error Column:
- Create a new DataColumn named "Error" at the end of your DataTable to store error messages for each row.

2. Validation Method:
- Refactor your `validateLengthColumn` method to accept DataRow and DataColumn parameters. This method will then validate the data in the specified column and update the "Error" column if needed.

3. Row-Level Validation:**
- After populating each row, call the validation method to check for errors and update the "Error" column accordingly.

4. **Download Excel with Errors:
- After validation, if there are errors, you can export the DataTable to a new Excel file. Include the "Error" column in the exported file.

Here's a simplified example of the changes:

// Inside your loop for populating rows
DataRow row = tbl.Rows.Add();
for (int colNum = 1; colNum <= tbl.Columns.Count - 1; colNum++)
{
    object temp = ws.Cells[rowNum, colNum].Value;

    // ... (your existing code)

    // Validate and update the "Error" column
    DataColumn errorColumn = tbl.Columns["Error"];
    string errorMsg = validateLengthColumn(row, errorColumn, temp.ToString().Trim(), colNum);
    if (!string.IsNullOrEmpty(errorMsg))
    {
        row[errorColumn] = errorMsg;
    }
}

// Your validation method
public string validateLengthColumn(DataRow row, DataColumn errorColumn, string Value, int col)
{
    // ... (your existing validation code)
}

After populating the DataTable, check if any rows have errors in the "Error" column. If errors are found, export the DataTable to a new Excel file, including the "Error" column.

Keep in mind that this is a simplified suggestion, and you might need to adjust it based on your specific requirements and the structure of your existing code.

Below are the links to the finest learning platforms
1. C# Tutorial (C Sharp)[^]W3 School
2. https://iqratechnology.com/academy/c-sharp-training/Iqratechnology
3. https://www.javatpoint.com/c-sharp-tutorialJavaPoint