Where to keep data from excel file before saving it in database?

Question

I've got an issue with my app. Using a form, user can choose an excel file to populate some table in database. I validate data before saving it and show the table with some validation messages. I also presents whole data from excel. Here's my issue:

1. Where to keep data before saving it to db? Let's assume that user doesn't see validation messages. He clicks SaveData button. We go to some action but we don't have data from our file in it. We can't pull data from db because it's not there either. We had access to data in the first action, but now we're in the second one.
2. With many validation messages I'd like to use paging. Here's the same problem - user clicks NextPage button and we go to an action. We don't have any data from excel in it.
3. Should I keep data in ViewBag? Can ajax help me here? I forgot one thing - I can have many rows in an excel file (1000+). I use gridmvc to present data.

Thank you for your help.

---

Update: If validation failed user can't use any data from excel file to populate database. He has to correct data from excel file manually and then try to use it again.

Answer 1

If i where you - i would upload excel to server, validate it and

1. if validation failed - return validation log to user as text file.

2. if validation success - upload file to db without additional confirmations.

In any cases user cannot fix errors in your grid, so save button will not becom accessible before valid excel will be loaded.

In some circumstances you can return to user his excel with validation column added to it. But i'm not sure that this will be more useful to user.

Answer 2

I would suggest you, Load the file to the server. Read the rows and columns of the excel file and load it in dataset. Add a new column to the dataset as Errors, now validate each row from the dataset if you find any error for the row then push it in that error column and set the flag as true.

Once all the rows are traversed, and if the flag is true then return the dataset on the user interface or back as excel.

If there are no errors then you can save the excel to db.

Hope this helps.