You must Sign In to post a response.
  • Category: ASP.NET

    Forloop not helping for Validating 10000 rows from excel

    Im doing some validation for excel rows... Incase of 10000 rows in excel, this validation inside a for loop or even foreach loop giving out timeout error. ( Code in C#)

    Any other best method to come across this timeout issue?
  • #765609
    Hi

    Check your database Transaction Time out part.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #765615
    loop on each row in excel file will leads to timeout exception as there are lot of rows in a excel sheet
    what you can do is read excel file using OLEDB and then loop on each row using datareader, instead of for. it will help you more
    Here are some good links
    https://social.msdn.microsoft.com/Forums/vstudio/en-US/09654227-99a8-47d0-9d6c-c2ab0d293aba/validate-excel-sheet-data-before-importing-to-database-in-c-windows-application?forum=csharpgeneral
    http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765621
    Hi lilly,

    As suggested by prasad, Get your data into dataset and loop the records using dataset, this will give the best result.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #765622
    HI Prasad, Thanks for the reply

    I came across one good code like this which will set all 10000 value as expected in 1 sec..

    Table.AsEnumerable().ToList().ForEach(p => p.SetField<string>("PERSON"," "));

    But I need to include a small if condition since I dont want all row value to change.. only certain..

    Expecting like Table.AsEnumerable().ToList().ForEach(p => if(p.PERSON ==null or 0) p.SetField<string>("PERSON"," "));

    Any idea??

  • #765623
    Hi

    Without see your code we canot give proper solution . Can you share your code with excel sheet.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.


Sign In to post your comments