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 (11 comments total)

First, Excel has a row limit of 65,536. If the combined amount of data exceeds this amount, you will not be able to store it in one Excel file.

Second, CSV and XLS are fundamentally different. CSV is a text-only file that you can open with any text-editor. XLS files are binary representations of all kinds of data, text being only a small part, that are generally only supported by Excel and other spreadsheet applications.

Text files can easily be combined with eachother, while binary files cannot.

If your data is only columns of text and numbers, without graphs or fonts, then this is the easiest process:

1. Export each of your files to CSV by selecting it from the Format menu under the filename in the Save As area of Excel.
2. Use the solution you linked to, or a similar solution, to combine each of these resulting text files with eachother into one file.
3. Re-open the file in Excel and Save As XLS.
posted by odinsdream at 12:05 PM on November 6, 2008

Note the row limit is by sheet, not by workbook, and apparently has been increased in Excel 2007 though I can't verify that, and of course you'd be unable to send the data to older Excel users.
posted by odinsdream at 12:07 PM on November 6, 2008

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

Okay I lied, the easiest way is with a macro. Here it is:

Private Sub ConcatenateAll()

Workbooks.Add
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\ConcatResults.xls"

CopyTargetBookmark = 1

For Each Workbook In Application.Workbooks
If Workbook.Name <> "ConcatResults.xls" And Workbook.Name <> "PERSONAL.XLS" Then
Workbook.Activate
Workbook.Worksheets(1).UsedRange.Copy
Workbooks("ConcatResults.xls").Activate
Range("A" & CopyTargetBookmark).Select
ActiveSheet.Paste
CopyTargetBookmark = CopyTargetBookmark + Workbook.Worksheets(1).UsedRange.Rows.Count
End If
Next Workbook

End Sub


Open all of your workbooks at once, then run the macro. It will copy all of the information from each workbook's first sheet, then paste it into a workbook named "ConcatResults.xls" stored in C:\

Each new paste will arrive right below the previous one, until it runs out of open workbooks to paste from.
posted by odinsdream at 12:27 PM on November 6, 2008 [2 favorites]

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

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]

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