J Livingston SQL (7/20/2014)

---

as an example of what may work for you...........

I have a spreadsheet called JLS.xlsx that is always stored in C:\xlimport (C:\xlimport\jls.xlsx)

it has a worksheet named "data" that I want to extract periodically into a SQL table always called [newtable] but the column names change on each import.

the first row of the worksheet represents the column header names to use SQL [newtable]

jls.xlsx example

29/07/201422/07/201415/07/2014

123456789987654321654789321

987654321654789321123456789

SELECT * into newtable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\xlimport\jls.xlsx; Extended properties=Excel 12.0', [data$])

SELECT * FROM newtable

users update the spreadsheet with new information and rename the column headings

so drop new table and rerun code and you will pick up the new column headings

DROP TABLE newtable

SELECT * into newtable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\xlimport\jls.xlsx; Extended properties=Excel 12.0', [data$])

SELECT * FROM newtable

if needed as part of a larger SSIS solution put the code in an Execute TSQL Task.

Alternatively set up a linked server and use that

EXEC master.dbo.sp_addlinkedserver

@server = N'jlsxlimport',

@srvproduct=N'Excel 12.0',

@provider=N'Microsoft.ACE.OLEDB.12.0',

@datasrc=N'C:\xlimport\jls.xlsx',

@provstr=N'Excel 12.0'

select * into newtable from jlsxlimport...data$

some very good advice on getting data into SQL from Excel can be found here

http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm