January 4, 2019 at 1:57 am
Happy New Year all 🙂
I want to import data from an old excel file i have that has headers and data on the first sheet and only data on sheets 2, 3 and so on. I have over 100 sheets and the same columns, full of data.
I tried openrowset but it doesnt work as i have office 32bit and 64bit sql server, and i dont want to install either or to get to same versions.
Is there another way to import all sheets in one go, regardless of the excel version (xls, xlsx etc) or bit version (32 vs 64), into a 64bit sql server?
Many thanks in advanced.
January 4, 2019 at 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 4, 2019 at 8:05 am
sgmunson - Friday, January 4, 2019 7:33 AMI 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?
January 5, 2019 at 3:48 pm
sgmunson - Friday, January 4, 2019 7:33 AMI 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?
January 5, 2019 at 6:39 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply