As the character Steve McCroskey said in Airplane II: The Sequel (1982), “Jacobs, I want to know absolutely everything that's happened up till now. ”Well, first the spreadsheets zipped, then came the unzip and then the XML but it got too bloated, so it got parsed into tables. Then came the EAV and the dynamic queries. Then anyone started to be able to do anything with the data. I couldn't believe it.

Trying to efficiently import Excel spreadsheet data into an RDBMS can be, at best of times, challenging! Issues such as drivers, versions, OS architecture, file formats and other spanners are often tumbling in the mix. Even if one has fully working and compatible drivers installed, the effort of importing a spreadsheet is in the minutes at the best. I’ve concluded that reading and parsing the spreadsheet files directly in the SQL Server is one of the most, if not the most flexible solution there is. Using this solution, one can simply drop any number of files in a directory, run a procedure and puff, just like a rabbit out of the magician´s hat, all the content of the spreadsheets is accessible in the SQL Server database of your choice.

This solution has relatively low technical prerequisites, it only requires an unzipping functionality being accessible to the SQL Server Services. The solution works directly on the structure of the spreadsheet files.Through the years I’ve regularly searched for articles on this subject, constantly looking for a better way of importing spreadsheets into SQL Server. Unfortunately, with very few exceptions, the articles that I’ve found are screenshot exercises of the Import / Export Wizard, Linked Server Setups and of course, a lot of ACE driver problems. Having solved the SQL Server spreadsheet import problem to my satisfaction and seeing how simple the solution is, I would like to see a native implementation based on a similar approach. I still haven’t suggested this solution to Microsoft Connect yet, as I would like to introduce this to the SQL Server Community before moving forward.

The video is not available to view online.