Ahh, the classic story. Boy gets job. Boy becomes the department data collector. Boy meets 25 Admins who send him a spreadsheet every week. Boy copies and pastes into one workbook. Boy lights himself on fire.
Combining multiple Excel worksheets into one data table is such a common task, I would bet a pound of bacon that most of us have been through that ordeal once or twice.
The good news is that you can just scour the internet, and you'll probably find dozens of techniques for combining multiple Excel files together.
Here's one simple way you can leverage Access to combine multiple Excel files (without VBA).
Step 1: Create links to your Excel workbooks in Access.
You'll have to repeat this process for each excel workbook you until you have them all linked in your database.
As you can see, the Excel linked tables are easy to spot.
Step 2: Start a new query in SQL View
Step 3: In SQL View, start typing Select statements for all the linked Excel tables, using the Union operator between each select statement.
Step 4: Save and Run the query.
The result will be a single dataset containing the combined data from all the linked files.
As long as the linked Excel files are in the same location, Access will automatically refresh the links for data changes each time you open the database. This means you can run through this setup once, then simply run the union query each time you need to re-combine the your Excel files.
Next week, I'll cover another method of using Access to combine Excel workbooks.
Update:
Rob correctly points out that I negelected to mention a key rule about the UNION operator.
The UNION operater will, by default, remove any duplicate rows you may have in your data. If you know that you will have duplicate rows in your data and you want to keep them, you'll need to use UNION ALL. UNION ALL will allow the duplicate rows to come through.
That's a really good technique. Shows that we shouldn't forget about Access in our enthusiasm for Excel (?).
But why would you want a solution that doesn't involve VBA coding? (shakes head in disbelief and returns to Recordset code……)
it's worth pointing out that 'UNION' will stack the datasets and delete the duplicates. If this is not what you want, or if its not possible, then 'UNION ALL' doesn't do the duplication step. The added benefit is that it's much quicker too.
Rob
Well, this is timely.
I backed myself into a corner by using Excel for a sales manager's little pet project that suddenly grew into a dealership initiative (result: one spreadsheet per salesman at five locations). I was starting to quiver trying to figure out the best route to get all this data together into one report. I was going to do some sort of VBA thing, but didn't know how to deal with all the issues regarding updated and new records.
These steps give me a simple method to work from. I can't wait to see next week's method!
Roy: Of course, you are correct. But don't worry, VBA will be part of Methods 3 and 4.
Rob: Great points Rob! I'll update the post.
Great post! keep'em coming!
Or what about using Ron's great add-in to do the trick for you. Used this the other day and it took only seconds to merge 34 files….
http://www.rondebruin.nl/merge.htm
Orange
Great idea. Just one additional note. If the different excel files don't already have a column to indicate their source, you can add one in the SQL:
Select 1 as Region, * From North
UNION
Select 2, * From South
UNION
Select 3, * From West
Nice post
Another way is to use this add-in if you not like VBA code :
http://www.rondebruin.nl/merge.htm
There is also code on the pages below
http://www.rondebruin.nl/copy3.htm
Or
http://www.rondebruin.nl/fso.htm
Or if there are to many data rows add it to a txt file
http://www.rondebruin.nl/mergetotxt.htm
"Little" projects usually grow into "big" ones, and it gets messy, and that is when the forgotten"Access"-ory becomes the best fast option!
Thanks!
…and the comments added also all came in handy!
Ron: Sorry your comments did not come through straight away. I reset the configurations to allow for links.
Anyone not familiar with Ron's work should definately visit his site. Tons of extremely useful tools such as his Merge utility.
I tried using the imported spreadsheet like a db table. Reading the data onto a front end is no problem but I tried updating and it did not work. Unless of course it's my code, which it most likely is.
Will the imported spreadsheet work just like a db table?
The best information i have found exactly here. Keep going Thank you
The best information i have found exactly here. Keep going Thank you
You can union 50 files or 256 columns which ever is less
If you have a file with say 80 Coulmns then you can union a max of 3 files(3 x 80 = 240<255)
Is there any reason you couldn't do this from Excel itself using the Data/From Other Sources/From Microsoft Query? I see there's an 'Excel Files' option in the 'Choose Data Source' dialog box.
Also, would be good if you had comment notification on this blog, Mike. Is this a possibility?
Cheers
Forgot to mention that above comment relates to Excel 2007. Not sure what steps you need to follow in previous versions to bring up the 'Choose Data Source' box, but they'll have it tucked away somewhere I'm sure.
Jeffrey: I'm still trying to figure out wordpress and this template I've chosen. I've added a Recent Comments list, while I research how (and if) I can get the email notification option working.
Hi,
Good technique t oconsolidate data.
Is there a technique to accomplish the revere i.e. split data from one worksheet into many sheets by using MS Access. Say i have an Excel range where Departments are in one column.
I would like to create a seperate sheet for every department. I can do this through functions and formulas but was wondering of there is a way to do this easily via Access.
Ashish: There are a few techniques. I'll go over them in the next few weeks here.
Hi,
After importing all the data in to Excel as per the method described above, assume that i want to change a paticular heading in all the child sheets (which are being consolidated). Suppose i rename the heading "Article Number" as "Article #" in all the sheets. After doing so, when i right click and refresh the consolidated sheet, i get the following message
[Microsoft][ODBC Microsoft Access driver] Too few parameters. Expected 1.
How can one overcome this problem.
I've used this technique to combine files that had time sequenced data where the time intervals were on different periods. I started with creating a master time file (every second in my case) and imported the time file with the other files. The union query then pulled all the data together. I then pushed the combined data result back to Excel to create a graph with time on the X axis and the different data on the Y axis. The Excel chart had > 3000 data points and that's why this technique is so much better than manually copying/pasting the data to combine it in Excel. I used this technique in Access 2003 and 2007 and don't see why it wouldn't work with earlier versions.
My technique for this is to use CSV files. Save
from Excel as *.csv and email it to me. You
get a smaller size email. Once you get all
the CSV files, use the COPY command in
DOS (CMD window) as
COPY 1.csv+2.csv+3.csv… all.csv
Then open all.csv with Excel. Done.
Tim: Nifty trick!
Excellent technique that I intend to share with some of my customers who have a very limited number of people that feel confortable with Access but must people keep a lot of valueable information in Excel. Their main problem is having to retype all of the information of their Excel files into a useable Access database.
Thanks Sam, I didn’t know about those limitations (256 columns makes sense, but I wasn’t aware of the 50 file limit).
I didn’t see one of my favorite resources listed here, so I’ll put it out there right now:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
Also, for splitting files, this is the best tool I’ve seen:
http://www.datapigtechnologies.com/AccessMain.htm
Go to the bottom of the page and get the file named ‘DataPig Access Explosion’.
Regards,
Ryan—
The only issue I can see (and have experienced WAY too many times) is that your data providers need to NOT ALTER the Excel file template. I have people changing freaking headers every month or so and they can't understand why I have an issue with it!!
"It's just 'Middle Names' instead of 'Middle Name' – what's the big deal?" Um, it's DIFFERENT, that's the big deal!
Ok, I'll go back to whimpering in the corner.
Thanks for this. I put it to use this day, not with separate workbooks but with separate sheets in the same workbook. It worked, but I ran into the "query is too complex" error when I tried to export a really long union query.
I had to use two separate union queries, then export to Excel and copy/paste the results together. I'm not sure what the upper limit is. I had 54 sheets worth of data (about 1900 rows total, after combining them).
Hi,
Your kind support is required for the following issue.
How we can Import Excel (Multipul Tabs) in MS Access using Macro?
Regards,
Umer Saeed
If you are not into macro programming or add-ins, you can use my program bulk file merger, which will merge hundreds in a snap. Bulk File Merger will merge multiple csv, xls, doc, pdf, or txt files into one master file. All with one button.
My site: http://essexredevelopment.com
This is very nifty. There is one problem. I am merging five different excel sheets. All of them have serial numbers entries. When they merge in access database the serial numbers repeat itself. is there a way that access merges and adjust serial numbers itself ?
Hi,
((. SO … THERE IS A HUMONGOUS # OF WORKSHEETS TO TAKE CARE OF. How can I have an index of all the work sheets on a top worksheet: so that I am able to go to a particular work sheet at a click of a button. PLEASE HELP!
the resolution on this page is gr8. But I am facing a slightly different dilemma. I have recently joined a new organization, here the work I have inherited is in a big mess. these guys have been using excel to generate Proforma invoices for their buyers…fine till this point, the trouble is that (1) there are approx 50 buyers. (2) every time a buyer orders thru mail, a new performa is created. e.g. Buyer name ABC Inc. if they have ordered 6 times then there are 6 invoices in their name. (3) the worst part: all these multiple proformas are work sheets in the same work book!!!
Thanx & Regards
Sudhanshu Sinha.