Import data in table

[SantoR]

user imports data from Excel to a table but i am not sure that the user will import right data into the table.

so in case a error comes due to a record, i want to cancel all the changes/updates done because of that excel file.

i.e if there are 10 records to be imported and error comes while uploading 5th record, then all the 4 records updated earlier should be recalled / reversed / cancelled

 

[MarkK]

1) Save the import filename with the record. If a record import fails from that file, delete all records with that filename.

2) Do the import inside a transaction. If there is an error during processing, rollback the transaction.

3) Put a boolean flag in the import table. Flag the records as false during the import. If the import fails, delete all the records that still have a false flag. If the import succeeds mark all the flags as true.

To name a few . . .

 

[vbaInet]

I like all of MarkK's suggestions and a transaction would be my preferred option, but I'll add one more similar to his first suggestion:

1) Save the import filename with the record. If a record import fails from that file, delete all records with that filename.

4) Save the import into a staging table, if it fails, delete all the records from that staging table.

Think of a staging table as a lone table where you import the Excel data into, transform the data in the staging table so it's fit for the master table, then update the master table with records from the staging table and finally purge the staging table.

With any of the methods you choose, it's best to use a staging table as the intermediary import process.

 

[MarkK]

vbaInet, I like the staging table suggestion. It provides a superior ability to isolate and identify crappy data and reduces the chance or harming existing good data.
Thanks,

 

[SantoR]

sorry i am not familiar with the terms,
staging table means--> replica of master table ?
will staging table satisfy all the relationships of master table ?

VbaInet : can you please explain option 4 with through a code ...

thanks

 

[vbaInet]

A staging table is a replica (in structure) of your master table. It will hold no data. You simply import your data into it, perform any enhancements before you push it to your master table. Like MarkK mentioned, "it reduces the chance of harming existing good data".

 

[gemma-the-husky]

in general it's very difficult with excel files - because there is no data typing of column contents.

I often follow the above idea - import to a staging table, and then run a series of validation tests on the data. If it fails any, they have to sort out the problem, and then try the import again.

 

[SantoR]

thanks every one for sharing your knowledge...

 

[vbaInet]

Let us know how you get on and feel free to ask any further questions.