Click here to Skip to main content
15,881,831 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
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

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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900