One of those simple but indispensable tricks

Back to a “real” post now after all the book stuff, but it’s going to be a short one while I get back on my feet.

Let’s say you have multiple worksheets (or workbooks) that all contain the same sort of data:

image  image  image

Multiple Worksheets (or Workbooks), All Contain The Same Type of Data

You Want to Combine ALL of Them Into a Single PowerPivot Table

These worksheets all come to you separately, but really you just want them as one big table.

Naturally, if it’s a small number of sheets, and each sheet isn’t massive, you can just copy paste them all into one table in Excel, then copy/paste into PowerPivot, or link the table into PowerPivot, or export as CSV so you can import it.

And you could also use Paste Append to directly paste into PowerPivot.

But if the combined data set exceeds 1 million rows, you won’t be able to combine the sheets into one – you will exceed the worksheet row limit.  And a data set of that size is not something you can paste into PowerPivot directly with Paste Append – pasting large data sets into PowerPivot takes forever, if it completes at all.

Here’s what I do when I find myself in this position:

1) Remove the Header Row


Delete the Header Row – We Don’t Want The Header
Duplicated Multiple Times in the Combined Data Set

My goal is to have the data start in cell A1:


Important that the Data Starts in A1

2) Save each sheet as CSV.

When you save as CSV, it saves only the active sheet, so you have to repeat this for each sheet (or each workbook).

This yields, for instance:


Now you Have a Bunch of Individual CSV Files

3) Open a Command Prompt


Start –> All Programs –> Accessories



Yep, That’s a Cmd Prompt

4) Change Directory to the Folder Containing Your CSV Files


This Isn’t a Cmd Prompt Tutorial, but the CD Command Changes Folder

5) Copy Them All Into One File


Copy *.csv combined.csv

This part is really the only “trick” in the whole process.

This command merges all of your CSV files into a single CSV file.  If I look back in Windows Explorer now:



6) Now Import that CSV into PowerPivot


Last Step

Bonus:  Macro to Save All Workbooks in a Folder as CSV

If you have a bunch of workbooks in a folder and you need to save them all as CSV, here’s a macro that will do it all for you:

Sub ProcessWorkbooksInFolder()

    Dim sPath As String
    Dim sFile As String
    Dim sDir As String
    Dim oWB As Workbook
    ‘***Set this to your folder of workbooks***
    sPath = “C:\Users\rob\desktop\csvfiles”
    If Right$(sPath, 1) <> “\” Then sPath = sPath & “\”
    sDir = Dir$(sPath & “*.xlsx”, vbNormal)
    Do Until LenB(sDir) = 0
        Set oWB = Workbooks.Open(sPath & sDir)

        oWB.Close False
        sDir = Dir$

End Sub

Sub SaveAsCSV()
    Dim sNewPath As String
    sNewPath = GetFileName(ActiveWorkbook.FullName)
    sNewPath = sNewPath & “.csv”
    ActiveWorkbook.SaveAs Filename:=sNewPath, FileFormat:=xlCSV, CreateBackup:=False

End Sub

Function GetFileName(sFullPath As String) As String
    GetFileName = Replace(sFullPath, “.xlsx”, “”, 1, -1, vbTextCompare)
    GetFileName = Replace(sFullPath, “.xlsb”, “”, 1, -1, vbTextCompare)
    GetFileName = Replace(sFullPath, “.xlsm”, “”, 1, -1, vbTextCompare)
    GetFileName = Replace(sFullPath, “.xls”, “”, 1, -1, vbTextCompare)
End Function