Im trying to figure out how to develop a bulk loader for MS Excel WorkBook uploads to SQL Server 2000. The SQL Server tables have the same names at the excel worksheets and each work book represents a grouping of data in the database.
I realize that I can use DTS to import data from the excel spreadsheets, the issues are:
- I need to read the Excel Worksheets who's files names will have dynmanic prefixes and static suffixes, so each of the three bulk loaders can read the file name suffix and determine that it needs to be run using the appropriate DTS package.
- I need the worksheet names to be prepresented as the Tbl names for which the data will be uploaded into, and the column headers as the field names. Should I be breaking the excel workbook up by saving each worksheet as a separate .csv file? How?
- If the sheet is null, then it will be skipped in the import.
The data uploads from Excel will replace ALL of the data in the prior table, so should I DELETE and CREATE a new table, DELETE and APPEND new records, or CREATE a new table and DELETE the old table upon success of the creation and then rename it?