Import multiple excel files in one go

Question

Import multiple excel files in one go

Talvin Singh

Ten Centuries

Points: 1288
More actions
October 18, 2017 at 2:07 pm

#324209

Hi all,
i have around 50+ excel spreadsheets with 1 sheet in each, the same headings but varying amounts of data from 1000 rows to 5000 plus.
is there a way to reference a file location and import all the data in that one tab for each spreadsheet into one table?
file names are different as well as the sheet name, eg
Filename: Wed 18-10-2017.xlsx
Sheet: 18-10 data
i currently have to individually import 1 excel sheet at a time. the first import created the table. my second import i appended the data onto the table. i dont want to do this 50+ times.
Many thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

For something like that, I'd typically use SSIS, with a Foreach Loop Container, such as described here:
http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

I'm not quite sure about dealing with the different sheet names. Is there some correlation between the file name and sheet name? MAybe setup a variable with an expression such as:
SUBSTRING(@[User::FileName], 5, 5) + " data"

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

no correlation between filename and sheet name.
is there a way to do it without SSIS method?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

How did you import that single spreadsheet, so far?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

William Rayer SSC Eights! Points: 865 More actions · Answer 4

If you wanted a pure TSQL solution, this should be possible using Openrowset to load in each spreadsheet. Create a table storing the filenames, another table to store the loaded spreadsheets, then loop through the filename table calling Openrowset to load each spreadsheet into the spreadsheet table. Unfortunately you would need to use dynamic SQL as Openrowset only works with fixed strings and does not allow the filename as a parameter. NOTE: this is not an ideal solution as it's using TSQL like a programming language instead of a data manipulation language. But it should get the job done.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

Maybe a solution in two steps.The Excel files are placed in an inbox. A VBA macro will open each file and save the sheet as a TAB sep text file. The macro to do that is easy to fix and is not depending on filenames or sheet names. If this is fine for you I can fix the macro. The text files are imported by TSQL an bulk insert.
Thank you

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

There's actually an easy way to read the tab names from a given spreadsheet file but I'd like an answer to my original question below so that I can make an informed recommendation. In other words, what tools and methods are you currently using to import that one spreadsheet?

Jeff Moden - Sunday, November 5, 2017 3:21 PM
How did you import that single spreadsheet, so far?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

currently i import within SQL server management studio.
right click the database name > task > Import
this is done on an individual file basis.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Talvin Singh - Tuesday, November 7, 2017 2:53 AM
currently i import within SQL server management studio.
right click the database name > task > Import
this is done on an individual file basis.

I have some references on how to do this without SSIS, VBA, PowerShell, etc, etc, etc. You'll also need to install what are affectionately known as the "ACE Drivers". Don't try to install those on your own. There's a trick I need to show you for how to install them on 64 bit machines that also contain 32 bit applications... and I haven't found a box yet that is completely free of 32 bit applications.

I have a really full dance card over the next couple of days even in the evenings. It'll take me a couple of days to get to this but, just to give you a heads up, this is all possible and can be done without sucking the nail off your thumb but it does take some initial setup. Once setup, it becomes fairly easy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Really appreciated.
Last week I imported 30 files one by one, and each of those had a few tabs. Very time consuming and risky if I miss a tab or file.
Enjoy and look forward to hearing from you.