Excel is still a great application with extensive features and a robust programming language.

Just this weekend I had to import a large spreadsheet of data into SQL Server, but all the data were in pivot table format, and each sampling location was on its own separate tab. 15-20 tabs, each tab with several dozen rows and columns: in all, around 30,000 data points needing transformation into 5 columns.

The kicker: each tab had extraneous data such as header, notes, etc. and blank rows/columns, all of which needed to be skipped based on programmed criteria. Though I've used SSIS to import a single-tab, well-formatted, predictably layed-out spreadsheet, I haven't tried SSIS to deal with multi-tab, irregularly formatted spreadsheets. My guess is stepping through them and selecting only those meeting my criteria would not be fun.

An hour and half with VBA in Excel and I was done, and the code runs in well under a second. From there it was a trivial OPENROWSET to grab the correctly formatted data into SQL.

The right tool for the job!

Rich