Export multiple excel worksheets to table in sql server

Question

Export multiple excel worksheets to table in sql server

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110651 More actions · Answer 1

I can think of two ways to do this. SSIS might work, if you can set up a For Each Loop Container that could loop through a list of the sheet names, and then operate an insert from each sheet. Pretty sure that means the Excel connection manager gets a package variable for its sheet name, if that's possible. If it's not, then I'd probably build a VBA macro to connect to SQL Server and manually loop through each sheet. Very slow, no doubt, compared to SSIS.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Talvin Singh Ten Centuries Points: 1288 More actions · Answer 2

sgmunson - Friday, January 4, 2019 7:33 AM
I can think of two ways to do this. SSIS might work, if you can set up a For Each Loop Container that could loop through a list of the sheet names, and then operate an insert from each sheet. Pretty sure that means the Excel connection manager gets a package variable for its sheet name, if that's possible. If it's not, then I'd probably build a VBA macro to connect to SQL Server and manually loop through each sheet. Very slow, no doubt, compared to SSIS.

I have been looking into SSIS and it looks like a good fix, but my first sheet contains headers, and every other sheet doesnt not.
Examples i have found must contain headers on each sheet, is there a function or step to capture headers only in sheet 1?

Talvin Singh Ten Centuries Points: 1288 More actions · Answer 3

sgmunson - Friday, January 4, 2019 7:33 AM
I can think of two ways to do this. SSIS might work, if you can set up a For Each Loop Container that could loop through a list of the sheet names, and then operate an insert from each sheet. Pretty sure that means the Excel connection manager gets a package variable for its sheet name, if that's possible. If it's not, then I'd probably build a VBA macro to connect to SQL Server and manually loop through each sheet. Very slow, no doubt, compared to SSIS.

I have managed to get this working, and it works when there are column headers on row A for each sheet.
what if only the first sheet has column headers?
i get an error and it only loads the first sheets date, but is there a way to continue loading remaining data?

pietlinden SSC Guru Points: 63621 More actions · Answer 4

What if you do something like copy the column names from the first sheet, then loop through the rest of the sheets, and insert a new row above the existing stuff and paste that in? Should be something like

dim i as integer
For i = 1 to ActiveWorkbook.Worksheets.Count - 1
' insert the column header row
next i

then save the file. Then you can just go ahead and import, I think.