Combining a ton of Excel files into one Excel file

November 6, 2008 11:41 AM

How do I combine a lot of excel files into one file?

I have hundreds of excel files that I want to combine into one excel file. I want the data from each file to get piled up into one big file, in one workbook. Just imagine if you had individual sports player stats, and you wanted to just see a big long list of player 1 separately, then player 2 separately, then player 3, on down.

This website suggests a command line solution, but I don't know if that works, plus I'm using .xls files though I know the csv files referenced at that solution are similar.

How do you suggest I do this?

posted by cashman to Computers & Internet (8 answers total)

Response by poster: I'm fairly sure I won't hit the limit. Thanks for your reply. Can I batch convert to csv? Because if not, that means I have to go into each file. If I go into each file I might as well just copy/paste.
posted by cashman at 12:21 PM on November 6, 2008

Just to clarify, do you want each file to end up in its own worksheet, or for all of the files to get piled into the same worksheet?
posted by qvtqht at 12:27 PM on November 6, 2008

It would be way better to upload the information into a database, even Access, in order to combine that many records.

Excel is going to drive you insane trying to combine that many files into one workbook. Aside from the row limit per sheet, there's a certain load point at which Excel chokes and starts taking down other Office apps as it flails while opening/accessing/saving very large files.
posted by batmonkey at 12:28 PM on November 6, 2008

And, on non-preview, nevermind - if you're not going to hit the row limit with hundreds of workbooks, my input is meaningless :D
posted by batmonkey at 12:30 PM on November 6, 2008

If you can program a little you can write a VBA macro to do this for you. Would need to do something like:
- Get list of files
- For each file use Import Data onto Existing Sheet
(You could also read file directly and write to activesheet.)
You should use Record Macro and record the action of importing one file. It's pretty easy and would be a lot easier than doing it manually. I'm too snowed at work to write it I'm afraid. Maybe google-fu can find you an example.

Here's a function you can use to get a list of files from a directory to start you off if you choose this route.

Function fcnGetFileList(ByVal strPATH As String, Optional strFilter As String) As Variant
' Returns a one dimensional array with filenames
' Otherwise returns False

Dim F As String
Dim i As Integer
Dim FileList() As String

If strFilter = "" Then strFilter = "*.*"

Select Case Right$(strPATH, 1)
Case "\", "/"
strPATH = Left$(strPATH, Len(strPATH) - 1)
End Select

ReDim Preserve FileList(0)

F = Dir$(strPATH & "\" & strFilter)
Do While Len(F) > 0
ReDim Preserve FileList(i) As String
FileList(i) = F
i = i + 1
F = Dir$()
Loop

If FileList(0) <> Empty Then
fcnGetFileList = FileList
Else
fcnGetFileList = False
End If
End Function

posted by NailsTheCat at 12:31 PM on November 6, 2008 [1 favorite]

Umm, I love Excel more than most, but it sounds like what you need is a database. Most databases can import the info you have in xls or csv format easily enough.

But, to use Excel. Yes, I can confirm that the 2007 Excel row limit is over 1 million rows now (as opposed to the older 65k)

It would be useful to know how you have the data arranged in your varying spreadsheets but the xls data can be converted to CSV easily enough. Open the file(s) in Excel, choose SAVE AS and change the drop down to CSV. This would allow you to use the application you gave the link for as well as other possibilities.

The disadvantage to converting to CSV is that you lose any formatting and (again, without knowing what kind of data you are working with) this could be a dealbreaker.

How many different files/workbooks/worksheets are we talking about here?

There are many freelance type sites like oDesk, eLance, and many others... that you can pay a relatively small wage for this type of data entry/clerical work. I had a similarly mind-numbing issue recently involving OCR Scanning and found a smart, pleasant gentleman who performed the work at a reasonable price and finished in half the time I expected him to. (Yes I paid him the originally budgeted hourly amount as a bonus.) One of the best $40 bucks I ever spent.

Can you link up some example spreadsheets?
posted by emjay at 12:36 PM on November 6, 2008 [1 favorite]

Response by poster: Thanks for the macro. I have to get together a few more .xls files, but I'll run it and hopefully it works. Much, much appreciated.
posted by cashman at 12:44 PM on November 6, 2008

As usual, I'm late to the party, but I use an Excel add-in for this exact purpose. You can find it here.

I use it daily to combine a couple dozen .xls files without pain or dangerous side-effects.

As mentioned previously, make sure the resulting big-ass file (BAF, if you will) isn't going to exceed Excel's ridiculous row limits (yes, it has been increased in 2007, but only to 104,000-odd rows). If it will exceed those limits, just do half or maybe a quarter of your files at a time, then pull them into Access to create the BAF.

Cheers!
posted by Salient at 7:07 AM on November 9, 2008 [1 favorite]