SQL Import from Excel using non-contiguous range?

I have some Excel spreadsheets that I cannot change as they are used by anoth开发者_运维知识库er department and they will not change them in future. They are .xlsm with over 500 columns (A:TH). I'm trying to import them into SQL server 2008 on a 64bit machine but I'm having huge problems. All forms of Excel import appear to truncate the columns I select to the first 255.

Ultimately there will 5 separate tables to store this data with 1 common key. I could write a short VBA script to sort the data in Excel into arranged columns of tables at source but I wanted to ask if the following was possible first...

This works fine and selects the columns A:IV

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                         'Excel 12.0;Database=C:\NEW.xlsm',
                         'SELECT * FROM [Details Sheet$A:IV]')

Is there a clever way to do something similar with a non-contiguous range such as

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                         'Excel 12.0;Database=C:\NEW.xlsm',
                         'SELECT * FROM [Details Sheet$C:C,IW:LZ]')

ie. pick up the key in column C and the additional columns IW:LZ? The problem for me is that using the full range C:LZ and SELECT [ID],[THIS],[THAT] FROM etc won't work for fields beyond 255 columns in the range, very annoying!

Have you tried using SSIS to import the Excel files? It can be very picky about data types, but I've never run into a limitation that I couldn't work around with a bit of a Script Component.

It's designed to be a high-performance ETL tool for jobs like what you're trying to accomplish. If you're new to it, check out this article on importing the entirety of Wikipedia as XML into multiple tables.

A quick note is that you may need to install additional Office drivers to read Excel 2007 format, especially on 64-bit machine.