SQL Server 2000, how to automate import data from excel

In summary, to import a dirty excel file, I would write a script to clean it up if possible (it almost always is depending on how it is dirty) and then use DTS and that should be the entire process. No additional steps should normally be needed to ge the data into SQL.

Here is my comments on each step:

1. I do not see why you need to do this. You can probably inspect as easily if not more easily in excel, and excel can be imported directly into SQL in a number of ways. Since you are using 2000, you will not have SSIS, but you should DTS which works quite nicely. You could also consider using opendatasource from inside of SQL or else look at some of the third party programs that do specifically this.

2. This depends on how you are defining bad. If it is something you can specify in enough detail to turn it into code...then you can turn it into code. Personally I would use Python to do this which has robust libraries to work with excel. You could use just about any major language though, including the VBA capabilities built into excel itself.

3. While I hate to ever suggest not backing up a database, it is probably not necessary to make a special backup before importing data. This is especially true if you are in FULL recovery mode so you could backup the log after the fact and do a point in time restore.

4. I do not understand why you are doing this, but then I do not know your work flow or what you do with it after the import. But truncating is extremely fast at least.

5. As mentioned, you could use DTS and do this without ever converting to CSV.

6. If you fully automate the data cleansing step, then this should not be a concern.

[Edited to address the comments]

Regarding Python libraries that work with excel, I would start with http://www.python-excel.org/. It has the downloads for xlrd and xlwt which together let you handle excel from within Python quite nicely. pyExcelerator is another option I have used in the past, though I think that xlrd and xlwt are superior for most use cases.

As to the date format, that should be relatively easy to handle though precisely though how you do it depends on the exact situations. If they are stored as dates in excel (or you can easily convert them to that) then you should have no problem importing them into SQL as dates. If they are stored as text that is in one of the standard date formats, then SQL is pretty good (but not perfect so do some testing!) about doing implicit conversions with very little user work. And if they are truly messed up then the Python datetime library is very good at parsing any date format that is remotely close to any of the standard formats so you can use that as one of many options to do that cleanup in an automated fashion.