Say the source data comes in excel format, below is how I import the data.

1. Converting to csv format via MS Excel
2. Roughly find bad rows/columns by inspecting
3. backup the table that needs to be updated in SQL Query Analyzer
4. truncate the table (may need to drop foreign key constraint as well)
5. import data from the revised csv file in SQL Server Enterprise Manager
6. If there's an error like duplicate columns, I need to check the original csv and remove them

I was wondering how to make this procedure more effecient in every step? I have some idea but not complete. For step 2&6, using scripts that can check automatically and print out all error row/column data. So it's easier to remove all errors once. For step 3&5, is there any way to automatically update the table without manually go through the importing steps?

Could the community advise, please? Thanks.