What method should I use in validating imported excel data on the database

[jbutardo]

Hi,
My title may be wrong but I would like to explain this process that I need here,

Here is my Process,
I already have imported excel file and inserted it in the datatable, and then I also validated it's format before inserting it in the database.
Now Here is a process that i want to know,
In importing excel file, there could have been more than 1 error in a certain row or column, and I need to validate it, which I have already made, but I want that if there's an error in a single row, it will stop inserting the datas, even if there is a row that has no error, and then create a log report on all the rows which have errors.

What methodology should I use in that situation.

I have tried to loop the datatable of the imported excel, then validate it, but in it's validation method, if there is a correct row, it will automatically inserts it in the database, which must not happen unless all rows are really correct,

I really need help for this,
thanks

[adam_k]

depending on your database you could use a transaction and upon error rollback. If no error was found then you commit the transaction and all changes (inserts in your case) will be "finalized"/made permanent. (In a multiuser environment it can be a pain)

If this isn't possible on the db you are using then you can use a temporary table (it can be tricky, but can be done almost in all dbs) to hold your inserts and upon completion transfer the records to their final destination.

Finally you can make it as simple as adding a status field and use that to delete any records inserted until you came across the error.

[ChrisPadgham]

The most robust way is to import the data into a staging table, each column in the staging table is text 255.

Do your validation there before inserting the data into the product table.

Alternatively you could open the excel spreadsheet as an embedded object and work through it cell by cell validating as you go.

[jbutardo]

depending on your database you could use a transaction and upon error rollback. If no error was found then you commit the transaction and all changes (inserts in your case) will be "finalized"/made permanent. (In a multiuser environment it can be a pain)

If this isn't possible on the db you are using then you can use a temporary table (it can be tricky, but can be done almost in all dbs) to hold your inserts and upon completion transfer the records to their final destination.

Finally you can make it as simple as adding a status field and use that to delete any records inserted until you came across the error.

I get the point of the rollback procedure..
But this is my question on this procedure that I want,
If there is a correct row on the excel file that I imported, it will be inserted right away, in which, the thing is that the program should check all rows first, then if there is wrong in it, the program will stop the whole procedure so that the user, will try to fix the whole excel file before importing it again to the database..

[adam_k]

In a process like the one you mention all sort of checks and all sort of errors are possible. You could check that x field contains numeric values and that y field is z chars long before you go to the db, but you might get an error due to foreign key or duplicate entry or ...

You can evaluate each field in your program before going to the db, but I suggest you still use a transaction. With a transaction the correct rows seem to be inserted right away, but that is the case only after you've commited the transaction. The records appear to the session with the transaction and everybody else is excluded from the table or the records in the tran.
If you rollback the transaction everything is cancelled, including the inserted records so far.